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' ... ')