mssql2005优化暗示

来自《Microsoft.Press.Inside.Microsoft.SQL.Server.2005.Query.Tuning.and.Optimization.Sep.2007》
Chapter 4. Troubleshooting Query Performance—Query Improvements
前几天看完了执行计划那部分后，大体浏览了一下优化暗示的使用，为了预防今后忘记，抓紧时间记下来，写一下大体的叙述吧，具体情况的话，今后在认真研究：
一、查询暗示：
1、FAST N Hint：是一个面向目标的暗示，告诉优化器，选择一个能尽快产生头N条记录的执行计划，例如：
SELECT [OrderId], [CustomerId], [OrderDate]
FROM [Orders]
ORDER BY [OrderDate]
OPTION (FAST 1)
2、OPTIMIZE FOR Hint：为另一个面向目标的暗示，是MSSQL2005里新增加的，它要求查询优化器基于你确定的参数产生一个计划，例如：
DECLARE @ShipCode nvarchar(20)SET @ShipCode = N'05022'SELECT [OrderId], [OrderDate]FROM [Orders]WHERE [ShipPostalCode] = @ShipCodeOPTION (OPTIMIZE FOR (@ShipCode = N'05022'))
        
3、Query-Level Join Hints：这些暗示强制优化器使用一个确定的连接方式，例如：neted loop join,merge join,hash join，如下：
SELECT O.[OrderId]FROM [Customers] C JOIN [Orders] O ON C.[CustomerId] = O.[CustomerId]WHERE C.[City] = N'London'OPTION (MERGE JOIN)
4、GROUP Hints：用来强制使用汇总方式，例如：order group,hash group,如下：SELECT [CustomerId], MAX([OrderDate])FROM [Orders]GROUP BY [CustomerId]OPTION (HASH GROUP)
5、UNION Hints：强制使用连接操作，例如： CONCAT UNION, MERGE UNION, and HASH UNION，如下：
SELECT [CustomerId]FROM [Orders]WHERE [ShipCity] = N'London'UNIONSELECT [CustomerId]FROM [Customers]WHERE [City] = N'London'OPTION (MERGE UNION)
二、强制顺序（Force Order）：
1、Forcing Join Order：FORCE ORDER 强制优化器产生的计划按照FROM后面表的顺序进行表间的连接，例如：
SELECT O.[OrderId]FROM [Customers] C JOIN [Orders] O JOIN [Employees] E   ON O.[EmployeeId] = E.[EmployeeId] –首先连接，形成左树连接   ON C.[CustomerId] = O.[CustomerId]WHERE C.[City] = N'London' AND E.[City] = N'London'OPTION (FORCE ORDER, HASH JOIN)SELECT O.[OrderId]FROM [Customers] C JOIN [Orders] O   ON C.[CustomerId] = O.[CustomerId] –先连接，形成右树连接   JOIN [Employees] E ON O.[EmployeeId] = E.[EmployeeId]WHERE C.[City] = N'London' AND E.[City] = N'London'OPTION (FORCE ORDER, HASH JOIN)
此外，还可以使用FORCE ORDER 来改变Aggregation计划中的位置，如果使用FORCE ORDER ，并且查询带有一个GROUP BY子句，那么Aggregation会放在子查询连接后及其他和子查询无关的连接的前面中间，例如：
SELECT O.[CustomerId], COUNT(*)FROM [Customers] C JOIN [Orders] O   ON C.[CustomerId] = O.[CustomerId]WHERE C.[Country] = N'USA'GROUP BY O.[CustomerId]OPTION (FORCE ORDER)
三、其他暗示：
1、MAXDOP N Hint：该暗示可以改变查询执行的并行度。
2、EXPAND VIEWS Hint：仅仅用于企业版，该暗示阻止优化器匹配索引视图；
四、表暗示：INDEX, NOEXPAND, and FASTFIRSTROW
1、INDEX hint:可以强制优化器使用一个特定的索引（或堆）来进行扫描或搜索；
（1）Eliminating a Bookmark Lookup，例如：
SELECT [OrderId], [CustomerId]FROM [Orders] WITH (INDEX(1))  –使用索引号为1的索引，索引号可以查询系统
WHERE [ShipPostalCode] = N'99362' –视图来获取
（2）Forcing a Bookmark Lookup,例如：
DECLARE @ShipCode nvarchar(20)SET @ShipCode = N'99362'SELECT [OrderId], [CustomerId]FROM [Orders] WITH (INDEX([ShipPostalCode]))WHERE [ShipPostalCode] = @ShipCode
（3）Multiple Nonclustered Indexes：在多个非簇索引里选用某个索引，例如：
SELECT [OrderId], [CustomerId]FROM [Orders] WITH (INDEX([OrderDate], [ShipPostalCode]))WHERE [OrderDate] = '1998-02-26' AND [ShipPostalCode] = N'99362'
（4）Index Union：如果表上有唯一性索引，可以强制产生一个模拟索引连接（INDEX UNION）的计划,例如：
SELECT [OrderId], [CustomerId]FROM [Orders]WHERE [OrderDate] = '1998-02-26' OR [ShipPostalCode] = N'83720'

SELECT [OrderId], [CustomerId]FROM [Orders] WITH (INDEX([OrderDate]))WHERE [OrderDate] = '1998-02-26'UNIONSELECT [OrderId], [CustomerId]FROM [Orders] WITH (INDEX([ShipPostalCode]))WHERE [ShipPostalCode] = N'83720'
（5）Detecting INDEX Hints in a Query Plan：通过属性窗口和XML计划，我们可以查看索引暗示：
  
2、NOEXPAND Hint：阻止MSSQL显式的扩展一个索引视图的定义。
3、FASTFIRSTROW Hint：用于查询中的任何一个表上，而FIRST N则用于整个查询。
4、ANSI-Style. Join Hints：允许更好的控制连接顺序和连接类型，例如：
SELECT O.[OrderId]FROM [Employees] E INNER MERGE JOIN   (       [Customers] C INNER LOOP JOIN       [Orders] O       ON O.[CustomerId] = C.[CustomerId]   )   ON O.[EmployeeId] = E.[EmployeeId]WHERE   C.[City] = N'London' AND   E.[LastName] = N'Peacock'
（5）USE PLAN Hint：是MSSQL2005里的新特点，它可以通过告诉优化器你希望产生的XML计划来强制一个计划。例如：
SET SHOWPLAN_XML ONGOSELECT O.[OrderId]
FROM [Employees] E INNER MERGE JOIN   (       [Customers] C INNER LOOP JOIN       [Orders] O       ON O.[CustomerId] = C.[CustomerId]   )   ON O.[EmployeeId] = E.[EmployeeId]WHERE   C.[City] = N'London' AND   E.[LastName] = N'Peacock'GOSET SHOWPLAN_XML OFFGO
SELECT O.[OrderId]FROM [Employees] E JOIN   (       [Customers] C JOIN       [Orders] O       ON O.[CustomerId] = C.[CustomerId]   )   ON O.[EmployeeId] = E.[EmployeeId]WHERE   C.[City] = N'London' AND   E.[LastName] = N'Peacock'OPTION (USE PLAN N' … ')