来自《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' |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-605581/,如需转载,请注明出处,否则将追究法律责任。
主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/184792.html

