SQL Server控制执行计划

SQL Server控制执行计划

为了提高性能，可以使用提示（hints）特性，包含以下三类：

查询提示：（query hints）告知优化器在整个查询过程中都应用某个提示

关联提示：（join hints）告知优化器在查询的特定部分使用指定的关联算法

表提示：（table hints）告知优化器使用表扫描还是表上特定的索引

这是非常规操作，除非你无计可施，或者用于研究。

一：查询提示

查询提示的基本语法是加入OPTION子句，如下：

select * from TableName OPTION 参数

这种提示不能运用在单独的INSET语句中，除非里面包含了SELECT操作，也不能用在子查询中

1.HASH/ORDER GROUP

两类提示----HASH GROUP和ORDER GROUP，用于GROUP BY聚合操作（通常就是DISTINCT或者聚合操作）。他们能强制优化器使用hashing或者grouping操作来实现聚合。

select p.Suffix,COUNT(p.Suffix)as SuffixUsageCount from Person.Person as p group by p.Suffix

优化器选择了Hash Match来实现聚合，这个操作在TempDB中创建了一个哈希表，然后从聚集索引扫描得到的结果里选出了不重复的数据插进这个表，接着返回结果集。

由于Hash Match操作是非排序的，因此这个开销大。如果把数据预先排序，就可避免在TempDB中额外排序。

select p.Suffix,COUNT(p.Suffix)as SuffixUsageCount from Person.Person as p group by p.Suffixoption (order group)

强制优化器使用Sort操作符来替换Hash Match。开销反而增加，只是演示。

2.MERGE/HASH/CONCATE UNION

当代吗中出现UNION操作符，就可能出现这些操作符。可以使用提示来引导优化器

select pm1.Name,pm1.ModifiedDate from Production.ProductModel pm1union select pm2.Name,pm2.ModifiedDate from Production.ProductModel pm2

Concatenation(串联)操作符这个操作符开销为0，但是Sort操作符就比较昂贵了。可以加上MERGE UNION提示

select pm1.Name,pm1.ModifiedDate from Production.ProductModel pm1union select pm2.Name,pm2.ModifiedDate from Production.ProductModel pm2option (merge UNION);

这里通过使用Merge Join来替代Concatenation操作从而实现UNION操作。由于合并连接要求数据是有序的，所以又引入了两个额外的Sort操作，反而增加了开销。

换成hash UNION。

select pm1.Name,pm1.ModifiedDate from Production.ProductModel pm1 union select pm2.Name,pm2.ModifiedDate from Production.ProductModel pm2 option (hash UNION);

3.LOOP/MERGE/HASH JOIN

如果使用了某种JOIN提示，那么整个查询的所有表都会使用这种关联算法，所以这个查询提示比较危险。

select pm.Name,pm.CatalogDescription,p.Name as productName,i.Diagram from Production.ProductModel as pm left join Production.Product as pon pm.ProductModelID=p.ProductModelID left join Production.ProductModelIllustration as pmion p.ProductModelID=pmi.ProductModelID left join Production.Illustration as ion pmi.IllustrationID=i.IllustrationIDwhere pm.Name like '%Mountain%'order by pm.Name

使用Hash Join提示：

select pm.Name,pm.CatalogDescription,p.Name as productName,i.Diagram from Production.ProductModel as pm left join Production.Product as pon pm.ProductModelID=p.ProductModelID left join Production.ProductModelIllustration as pmion p.ProductModelID=pmi.ProductModelID left join Production.Illustration as ion pmi.IllustrationID=i.IllustrationIDwhere pm.Name like '%Mountain%'order by pm.Nameoption (hash join)

全部关联使用了Hash Join

使用MERGE JOIN的执行计划

select pm.Name,pm.CatalogDescription,p.Name as productName,i.Diagram from Production.ProductModel as pm left join Production.Product as pon pm.ProductModelID=p.ProductModelID left join Production.ProductModelIllustration as pmion p.ProductModelID=pmi.ProductModelID left join Production.Illustration as ion pmi.IllustrationID=i.IllustrationIDwhere pm.Name like '%Mountain%'order by pm.Nameoption (merge join)

4.FORCE ORDER

如果想强制优化器使用自己的逻辑表关联顺序而不让优化器进行不穷尽的分析

SELECT pc.Name AS ProductCategoryName , ps.Name AS ProductSubCategoryName , p.Name AS ProductName , pdr.Description , pm.Name AS ProductModelName , c.Name AS CultureName , d.FileName , pri.Quantity , pr.Rating , pr.CommentsFROM Production.Product AS p LEFT JOIN Production.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID LEFT JOIN Production.ProductSubcategory AS ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID LEFT JOIN Production.ProductInventory AS pri ON p.ProductID = pri.ProductID LEFT JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID LEFT JOIN Production.ProductDocument AS pd ON p.ProductID = pd.ProductID LEFT JOIN Production.Document AS d ON pd.DocumentNode = d.DocumentNode LEFT JOIN Production.ProductCategory AS pc ON ps.ProductCategoryID = pc.ProductCategoryID LEFT JOIN Production.ProductModelProductDescriptionCulture AS pmpdc ON pm.ProductModelID = pmpdc.ProductModelID LEFT JOIN Production.ProductDescription AS pdr ON pmpdc.ProductDescriptionID = pdr.ProductDescriptionID LEFT JOIN Production.Culture AS c ON c.CultureID = pmpdc.CultureID;

加上option（force order）；

5.MAXDOP

有时候会出现两个执行计划：一个是实际的执行计划，一个是具有并行操作的执行计划，并行执行计划更慢，运行的开销已经超过了一个叫cost threshold for parallelism的值，优化器决定引入并行操作。

可以再语句级别就行控制

GOsp_configure 'show advanced options',1goRECONFIGURE WITH OVERRIDE; GO sp_configure 'cost threshold for parallelism', 1; GO RECONFIGURE WITH OVERRIDE; GO SELECT wo.DueDate , MIN(wo.OrderQty) MinOrderQty , MIN(wo.StockedQty) MinStockedQty , MIN(wo.ScrappedQty) MinScrappedQty , MAX(wo.OrderQty) MaxOrderQty , MAX(wo.StockedQty) MaxStockedQty , MAX(wo.ScrappedQty) MaxScrappedQtyFROM Production.WorkOrder woGROUP BY wo.DueDateORDER BY wo.DueDate; GO sp_configure 'cost threshold for parallelism', 50; GO RECONFIGURE WITH OVERRIDE; GO

option(maxdop 1)

6.OPTIMIZE FOR

参数嗅探发生在存储过程中，参数化查询能够最大化地重用执行计划，优化器通过这些参数来评估索引等的情况。

select * from Person.Address where City='Mentor'

select * from Person.Address where City='London'

在同样的语句下，参数不同，会导致执行计划的不同，执行计划的不同主要是因为City=‘Mentor’具有较高的选择度，优化器可以使用查找操作，London这个参数导致选择度降低，这也致使优化器使用了扫描操作，而弃用了原有的非聚集索引

改写为参数化：

declare @city nvarchar(30)set @city='Mentor'select * from Person.Address where City=@cityset @city='London'select * from Person.Address where city=@city

为什么使用聚集索引，因为优化器不知道你将要传入什么值，所以只能取统计信息中的平均值，平均值也较大，所以选择了扫描操作。

让优化器在第二个查询中使用Mentor值产生的执行计划作为该查询的执行计划。

declare @city nvarchar(30)set @city='Mentor'select * from Person.Address where City=@cityset @city='London'select * from Person.Address where city=@cityoption(optimize for(@city='Mentor'))

在无法预知参数时，这个提示是一个避免参数嗅探的较好的方法。

三：连接提示

物理关联算法只有Nested Loops、Merge Join、Hash Match Join3种

1.Nested Loops联接，从outer table（执行计划中位于上方的表）中读取一行，然后与inner table（执行计划中位于下方的表）中的每一行对比，并返回满足JOIN条件的行其算法的复杂度和两表的行数相乘之积成正比。对于小表来说非常高效

2.Merge联接：两个已经排序好的表（或者结构集），其算法复杂度与所有数据的总行数成正比。当所进行的连接是等于连接(关联中的等于操作)时，对于大数据集非常高效。

3.Hash Match联接，从一个输入表中读取所有的行，然后通过散列法，存入内存中的哈希表，并与另外一个表关联，如果满足JOIN条件，就返回。对于巨大的数据量，特别是数据仓库系统，是最有效的算法。

由于某些原因，改写关联算法可以从中获益。

（1）Nested Loops

SELECT pm.Name , pm.CatalogDescription , p.Name AS ProductName , i.DiagramFROM Production.ProductModel pm LEFT JOIN Production.Product p ON pm.ProductModelID = p.ProductModelID LEFT JOIN Production.ProductModelIllustration pmi ON pm.ProductModelID = pmi.ProductModelID LEFT JOIN Production.Illustration i ON pmi.IllustrationID = i.IllustrationIDWHERE pm.Name LIKE '%Mountain%'ORDER BY pm.Name;

Hash Match占据了最大的开销、由于Where pm.name like '%mountain%'的存在，优化器使用不了列上的索引，所以选择聚集索引扫描，Order By 会导致Sort操作的发生。如果尝试把Hash Match换成Nested Loops，用于检查执行计划是否有性能问题。

select pm.Name,pm.CatalogDescription,i.Diagram from Production.ProductModel pm left loop join Production.Product p on pm.ProductModelID =p.ProductModelIDleft join Production.ProductModelIllustration pmi on pm.ProductModelID=pmi.ProductModelIDleft join Production.Illustration i on pmi.IllustrationID=i.IllustrationIDwhere pm.Name like '%Mountain%'order by pm.Name

改写后的查询效率更低。

Merger

把Loop提示改成Merger

三：表提示

表提示在很多地方都会用到，比如控制锁的行为。在生成执行计划的过程中，这种提示会控制优化器按照你指定的方式来使用特定的表。

对于表提示，建议用于测试和问题侦测，不建议常规化使用。

INDEX（）

这个提示用于指定优化器使用特定索引来访问表，可以使用索引编号和索引名称，建议使用所以你名称

select de.Name,e.JobTitle,p.LastName+', '+p.FirstName from HumanResources.Department dejoin HumanResources.EmployeeDepartmentHistory edh on de.DepartmentID=edh.DepartmentID join HumanResources.Employee e on edh.BusinessEntityID=e.BusinessEntityIDjoin Person.Person p on e.BusinessEntityID=p.BusinessEntityIDwhere de.Name like 'P%'

来指定HumanResources.Department表上的pk_Department_DepartmentID索引

select de.Name,e.JobTitle,p.LastName+', '+p.FirstName from HumanResources.Department de with(index(pk_Department_DepartmentID))join HumanResources.EmployeeDepartmentHistory edh on de.DepartmentID=edh.DepartmentID join HumanResources.Employee e on edh.BusinessEntityID=e.BusinessEntityIDjoin Person.Person p on e.BusinessEntityID=p.BusinessEntityIDwhere de.Name like 'P%'

扩展信息：

1.阅读庞大的执行计划

IF OBJECT_ID('Sales.uspGetDiscountRates', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetDiscountRates; goCREATE PROCEDURE Sales.uspGetDiscountRates ( @BusinessEntityId INT , @SpecialOfferId INT ) as begin TRY IF EXISTS ( SELECT * FROM Person.Person AS p INNER JOIN Sales.Customer AS c ON p.BusinessEntityID = c.PersonID INNER JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Sales.SpecialOffer AS spo ON sod.SpecialOfferID = spo.SpecialOfferID WHERE p.BusinessEntityID = @BusinessEntityId AND spo.[SpecialOfferID] = @SpecialOfferId ) BEGIN SELECT p.LastName + ', ' + p.FirstName , ea.EmailAddress , p.Demographics , spo.Description , spo.DiscountPct , sod.LineTotal , pr.Name , pr.ListPrice , sod.UnitPriceDiscount FROM Person.Person AS p INNER JOIN Person.EmailAddress AS ea ON p.BusinessEntityID = ea.BusinessEntityID INNER JOIN Sales.Customer AS c ON p.BusinessEntityID = c.PersonID INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Sales.SpecialOffer AS spo ON sod.SpecialOfferID = spo.SpecialOfferID INNER JOIN Production.Product pr ON sod.ProductID = pr.ProductID WHERE p.BusinessEntityID = @BusinessEntityId AND sod.[SpecialOfferID] = @SpecialOfferId; END ELSE BEGIN SELECT p.LastName + ', ' + p.FirstName , ea.EmailAddress , p.Demographics , soh.SalesOrderNumber , sod.LineTotal , pr.Name , pr.ListPrice , sod.UnitPrice , st.Name AS StoreName , ec.LastName + ', ' + ec.FirstName AS SalesPersonName FROM Person.Person AS p INNER JOIN Person.EmailAddress AS ea ON p.BusinessEntityID = ea.BusinessEntityID INNER JOIN Sales.Customer AS c ON p.BusinessEntityID = c.PersonID INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Production.Product AS pr ON sod.ProductID = pr.ProductID LEFT JOIN Sales.SalesPerson AS sp ON soh.SalesPersonID = sp.BusinessEntityID LEFT JOIN Sales.Store AS st ON sp.BusinessEntityID = st.SalesPersonID LEFT JOIN HumanResources.Employee AS e ON st.BusinessEntityID = e.BusinessEntityID LEFT JOIN Person.Person AS ec ON e.BusinessEntityID = ec.BusinessEntityID WHERE p.BusinessEntityID = @BusinessEntityId; END IF @SpecialOfferId = 16 BEGIN SELECT p.Name , p.ProductLine FROM Sales.SpecialOfferProduct sop INNER JOIN Production.Product p ON sop.ProductID = p.ProductID WHERE sop.SpecialOfferID = 16; END END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_MESSAGE() AS ErrorMessage; RETURN ERROR_NUMBER(); END CATCH RETURN 0;

调用

EXEC Sales.uspGetDiscountRates@BusinessEntityId=1423,@SpecialOfferId=16

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。