1. 首页
  2. IT资讯

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-605581/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/184792.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code