SQL Server 2008中SQL应用系列–目录索引

SQL Server的系统查询过程负责在SELECT查询执行时候产生查询执行计划。SQL Server会“智能”地选择一个高效计划来取代低效的一个。大多数时候，SQL Server会把这份工作干得很棒。但正如有些人所担忧的，SQL Server并不是万能的，有时候，我们通过查询执行计划、表统计信息、支撑的索引及其他因素，研究性能之后，发现查询优化器选择的执行计划没有达到预期的效果，或者说，查询优化器做出了错误的选择。此时我们可能需要使用Hints（提示）来覆盖SQL Server查询优化器决定的过程。

看MSDN（http://msdn.microsoft.com/en-us/library/ms187713%28v=sql.105%29.aspx），Hints(提示)是指定的强制选项或策略，由 SQL Server 查询处理器针对 SELECT、INSERT、UPDATE 或 DELETE 语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。

使用Hints之前，我有义务提醒一下各位读者：

SQL Server绝大多数情况下会做出正确的选择，即便使用的Hints短期内有效，但随着数据库内容的更改，使用的查询计划反而可能更高效，但此时因为Hints更霸道，SQL Server并不会“自作主张”地使用优化器。SQL Server发布补丁后，有效的Hints也可能会改变。

本文将要的介绍的内容包括：SQL Server三种不同类型的Hints：联接提示（Join Hints）、查询提示（Query Hints）、表提示（Table Hints），包括SQL Server2008中引入的提示ForceSeek，可以用它将索引查找来替换索引扫描。

1、使用联接提示（Join Hints）

官方解释：联接提示用于指定查询优化器在两个表之间强制执行联接策略。（http://msdn.microsoft.com/zh-cn/library/ms173815%28v=sql.105%29.aspx）

用法：<join_hint> ::= { LOOP | HASH | MERGE | REMOTE }

联结提示会强制查询优化器来使用你命令的方式联结表，通过内部JOIN操作来实现。可用的联结提示：

代码演示：

    /*********使用Hints联结***************/ /*********[email protected]邀月************/ USETestDb2 GO IFNOTOBJECT_ID('HintsDemo','U')ISNULLDROPTABLEHintsDemo GO IFNOTOBJECT_ID('HintsDemo2','U')ISNULLDROPTABLEHintsDemo2 GO ----创建测试数据表 CREATETABLEdbo.HintsDemo(HIDint,HTitleNvarchar(50)) GO CREATETABLEdbo.HintsDemo2(HID2int,HIDint) GO ----插入20条数据 INSERTHintsDemo(HID,HTitle) VALUES(cast(rand()*10ASINT),replicate('X',cast(rand()*25ASINT))) GO20--重复该语句20次,邀月注 INSERTHintsDemo2 SELECTTOP10cast(rand()*10ASINT),HIDfromHintsDemo GO2--重复该语句2次，邀月注 --此时两个表各有20条记录 SELECT*FROMHintsDemo SELECT*FROMHintsDemo2 SETSHOWPLAN_XMLONGO SELECTh.HID,h.HTitle,d.HID2 FROMHintsDemoh INNERJOINHintsDemo2dONh.HID=d.HID GO SETSHOWPLAN_XMLOFFGO

下面使用Hash Join

    SETSHOWPLAN_XMLONGO SELECTh.HID,h.HTitle,d.HID2 FROMHintsDemoh InnerHASHJOINHintsDemo2dONh.HID=d.HID GO SETSHOWPLAN_XMLOFFGO

2、使用查询提示（Query Hints）

官方解释：在查询语句的持续时间内，查询提示优先于查询优化器的默认行为。您可以使用查询提示指定受影响的表的锁定方法、一个或多个索引、一个查询处理操作（如表扫描或索引查找）或其他选项。查询提示应用于整个查询。（http://msdn.microsoft.com/zh-cn/library/ms181714%28v=sql.105%29.aspx）

它的参数比较复杂：

    <query_hint>::= {{HASH|ORDER}GROUP|{CONCAT|HASH|MERGE}UNION|{LOOP|MERGE|HASH}JOIN|EXPANDVIEWS|FASTnumber_rows|FORCEORDER|IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX|KEEPPLAN|KEEPFIXEDPLAN|MAXDOPnumber_of_processors|MAXRECURSIONnumber |OPTIMIZEFOR(@variable_name{UNKNOWN|=literal_constant}[,...n]) |OPTIMIZEFORUNKNOWN |PARAMETERIZATION{SIMPLE|FORCED} |RECOMPILE|ROBUSTPLAN|USEPLANN'xml_plan'|TABLEHINT(exposed_object_name[,<table_hint>[[,]...n]]) } <table_hint>::= [NOEXPAND]{INDEX(index_value[,...n])|INDEX=(index_value)|FORCESEEK[(index_value(index_column_name[,...]))]|FORCESCAN|HOLDLOCK|NOLOCK |NOWAIT|PAGLOCK|READCOMMITTED|READCOMMITTEDLOCK|READPAST|READUNCOMMITTED|REPEATABLEREAD|ROWLOCK|SERIALIZABLE |SPATIAL_WINDOW_MAX_CELLS=integer|TABLOCK|TABLOCKX|UPDLOCK|XLOCK }

一般情况下， 我们可以在存储过程中使用ReCompile查询提示，这样可以实现SQL语句级的重编译，而不是整个存储过程（批处理语句）的重编译。我们通过一个示例来说明。

    /*********使用查询提示***************/ /*********[email protected]邀月************/ DECLARE@HintsTitleDemonvarchar(50)='0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'SELECTHID,HTitle FROMHintsDemo WHEREHTitle=@HintsTitleDemo ORDERBYHID /* HIDHTitle 60E2FAB59-9A22-4E14-B7BE-33AB500E3B9E */ --使用DMV查询查看内存中的统计计划是否可以重用 SELECTcacheobjtype,objtype,usecounts FROMsys.dm_exec_cached_plans CROSSAPPLYsys.dm_exec_sql_text(plan_handle) WHEREtextLIKE'DECLARE@HintsTitleDemo%'

    --先清除过程缓存,！！！请不要在生产环境中使用下句 DBCCFREEPROCCACHE /*DBCCexecutioncompleted.IfDBCCprintederrormessages,contactyoursystemadministrator.*/ DECLARE@HintsTitleDemonvarchar(50)='0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'SELECTHID,HTitle FROMHintsDemo WHEREHTitle=@HintsTitleDemo ORDERBYHID OPTION(RECOMPILE)--强制重新编译

再次友情提醒，绝大多数情况下，SQL Server 会做出较优的选择，极端情况下，我们才需要干预它的查询计划，以覆盖SQL Server的选择。

3、表提示（Table Hints）

官方解释：通过指定锁定方法、一个或多个索引、查询处理操作（如表扫描或索引查找）或其他选项，表提示可在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。表提示在 DML 语句的 FROM 子句中指定，仅影响在该子句中引用的表或视图。（http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx）

表提示与查询提示类似，用于覆盖Select、INSERT、UPDATE和DELETE的默认行为。可以为一个表设置多个表提示，并使用逗号分开，只要它们不改属于同个分组即可。

    WITH(<table_hint>[[,]...n]) <table_hint>::=[NOEXPAND]{INDEX(index_value[,...n])|INDEX=(index_value)|FORCESEEK[(index_value(index_column_name[,...]))]|FORCESCAN|FORCESEEK|HOLDLOCK|NOLOCK |NOWAIT|PAGLOCK|READCOMMITTED|READCOMMITTEDLOCK|READPAST|READUNCOMMITTED|REPEATABLEREAD|ROWLOCK|SERIALIZABLE |SPATIAL_WINDOW_MAX_CELLS=integer|TABLOCK|TABLOCKX|UPDLOCK|XLOCK } <table_hint_limited>::= {KEEPIDENTITY|KEEPDEFAULTS|HOLDLOCK|IGNORE_CONSTRAINTS|IGNORE_TRIGGERS|NOLOCK|NOWAIT|PAGLOCK|READCOMMITTED|READCOMMITTEDLOCK|READPAST|REPEATABLEREAD|ROWLOCK|SERIALIZABLE|TABLOCK|TABLOCKX|UPDLOCK|XLOCK }

这里就不举例了，看MSDN（http://msdn.microsoft.com/zh-cn/library/ms187373%28v=sql.105%29.aspx）即可。

值得注意的是NOlOCK选项，如下语句：

    --不锁定执行查询 SELECTHID,HTitle FROMHintsDemo WITH(NOLOCK) WHEREHID=4 /* HIDHTitle 4E6DA3DB2-3D41-47B4-B4E3-DDA90918434C 41C4C9211-EB1C-42B5-A08A-558DC73462B4 4667C9985-3B0A-4767-AED9-82FEE623433D */

NOLOCK表提示让查询在不在被影响的行或数据是放置共享锁－－允许你在不被阻塞或不阻塞其他查询的情况下读取（但会遇到“脏读”问题）。

最后，我们来介绍一个SQL Server 2008引入的FORCESEEK表提示，它可以用来将索引扫描替换为索引查找。会有一些原因导致SQL Server产生不良的查询计划。例如表数据经常变化并且信息忆不再准确，或带有拙劣where子句的查询没有为查询优化器过程提供有用的或足够的信息。

如果为了单独查找指定数据，而在检索一行之前对整个非常大的表进行了整表扫描，由此带来的I/O开销当然是不能接受的。假定上面的测试表非常大。

    /*********使用ForceSeek强制覆盖SCAN***************/ /*********[email protected]邀月************/ --创建一个测试主键 ALTERtabledbo.HintsDemo addPKIDintPrimarykeyIdentity(101,1) GO --此时,SQLServer为我们自动创建一个聚集索引[PK__HintsDem__5E0282723D61619B] SELECT*FROMdbo.HintsDemo --创建一个非聚集索引 CREATENONClusteredIndexidx_ForceSeekDemo ONTestdb2.dbo.HintsDemo(HTitle) ----select*,Row_Number()over(PartitionBy[HID]Orderby[HID]Asc)AsRowID ----fromHintsDemo; SETSHOWPLAN_XMLONGO SELECTDISTINCTHTitlefromHintsDemo WHEREHIDBETWEEN8AND10andHTitle='141466E4-E8CC-4219-A9AF-7C0D2B86A668'GO SETSHOWPLAN_XMLOFF

注意本例使用With(ForceSeek)未必最优，只是提供了一个修改系统访问数据的方式，邀月注

    SETSHOWPLAN_XMLONGO --注意本例使用With(ForceSeek)未必最优，只是提供了一个修改系统访问数据的方式,邀月注 SELECTDISTINCTHTitlefromHintsDemoWITH(FORCESEEK) WHEREHIDBETWEEN8AND10andHTitle='141466E4-E8CC-4219-A9AF-7C0D2B86A668'GO SETSHOWPLAN_XMLOFF

你也可以更进一步指定使用哪个索引

    SELECTDISTINCTHTitlefromHintsDemoWITH(FORCESEEK,INDEX(idx_ForceSeekDemo)) WHEREHIDBETWEEN8AND10andHTitle='141466E4-E8CC-4219-A9AF-7C0D2B86A668'GO

对于本例，如果你需要得更好的性能，可以考虑使用SQL Server引入的指定行集索引功能，比如你只关注某房价表中均价在5000-6000元的楼盘信息，那么可以专门为这个区间建立索引。这个属于题外话，不在本文讨论之列。

小结：本文简要介绍SQL Server三种不同类型的联接提示（Join Hints）、查询提示（Query Hints）、表提示（Table Hints），包括SQL Server2008中引入的提示ForceSeek，可以用它将索引查找来替换索引扫描。再次提醒一下读者:提示有风险，使用需谨慎！

原文链接：http://www.cnblogs.com/downmoon/archive/2012/04/13/2445694.html

【编辑推荐】

    SQL Server 2008新增的变更数据捕获和更改跟踪SQL Server 2008中增强的汇总技巧从SQL Server删除大数据说开去SQL Server 2012的12个激动人心的功能SQL Server数据库恢复案例分享