为了提高性能，可以使用提示（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.Suffix
option (order group)
强制优化器使用Sort操作符来替换Hash Match。开销反而增加，只是演示。
2.MERGE/HASH/CONCATE UNION
当代吗中出现UNION操作符，就可能出现这些操作符。可以使用提示来引导优化器
select pm1.Name,pm1.ModifiedDate from Production.ProductModel pm1
union
select pm2.Name,pm2.ModifiedDate from Production.ProductModel pm2
Concatenation(串联)操作符这个操作符开销为0，但是Sort操作符就比较昂贵了。可以加上MERGE UNION提示
select pm1.Name,pm1.ModifiedDate from Production.ProductModel pm1
union
select pm2.Name,pm2.ModifiedDate from Production.ProductModel pm2
option (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 p
on pm.ProductModelID=p.ProductModelID
left join Production.ProductModelIllustration as pmi
on p.ProductModelID=pmi.ProductModelID
left join Production.Illustration as i
on pmi.IllustrationID=i.IllustrationID
where 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 p
on pm.ProductModelID=p.ProductModelID
left join Production.ProductModelIllustration as pmi
on p.ProductModelID=pmi.ProductModelID
left join Production.Illustration as i
on pmi.IllustrationID=i.IllustrationID
where pm.Name like '%Mountain%'
order by pm.Name
option (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 p
on pm.ProductModelID=p.ProductModelID
left join Production.ProductModelIllustration as pmi
on p.ProductModelID=pmi.ProductModelID
left join Production.Illustration as i
on pmi.IllustrationID=i.IllustrationID
where pm.Name like '%Mountain%'
order by pm.Name
option (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.Comments
FROM 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的值，优化器决定引入并行操作。
可以再语句级别就行控制
GO
sp_configure 'show advanced options',1
go
RECONFIGURE 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) MaxScrappedQty
FROM Production.WorkOrder wo
GROUP BY wo.DueDate
ORDER 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=@city
set @city='London'
select * from Person.Address where city=@city
为什么使用聚集索引，因为优化器不知道你将要传入什么值，所以只能取统计信息中的平均值，平均值也较大，所以选择了扫描操作。
让优化器在第二个查询中使用Mentor值产生的执行计划作为该查询的执行计划。
declare @city nvarchar(30)
set @city='Mentor'
select * from Person.Address where City=@city
set @city='London'
select * from Person.Address where city=@city
option(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.Diagram
FROM 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.IllustrationID
WHERE 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.ProductModelID
left join Production.ProductModelIllustration pmi on pm.ProductModelID=pmi.ProductModelID
left join Production.Illustration i on pmi.IllustrationID=i.IllustrationID
where pm.Name like '%Mountain%'
order by pm.Name
改写后的查询效率更低。
Merger
把Loop提示改成Merger
三：表提示
表提示在很多地方都会用到，比如控制锁的行为。在生成执行计划的过程中，这种提示会控制优化器按照你指定的方式来使用特定的表。
对于表提示，建议用于测试和问题侦测，不建议常规化使用。
INDEX（）
这个提示用于指定优化器使用特定索引来访问表，可以使用索引编号和索引名称，建议使用所以你名称
select de.Name,e.JobTitle,p.LastName+', '+p.FirstName from HumanResources.Department de
join HumanResources.EmployeeDepartmentHistory edh on de.DepartmentID=edh.DepartmentID
join HumanResources.Employee e on edh.BusinessEntityID=e.BusinessEntityID
join Person.Person p on e.BusinessEntityID=p.BusinessEntityID
where 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.BusinessEntityID
join Person.Person p on e.BusinessEntityID=p.BusinessEntityID
where de.Name like 'P%'
扩展信息：
1.阅读庞大的执行计划
IF OBJECT_ID('Sales.uspGetDiscountRates', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetDiscountRates;
go
CREATE 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