[SQL SERVER]善用計畫指南
透過計畫指南可以將特定提示或執行計畫附加至現有查詢。
當你準備調校第三方軟體且無法改寫查詢效能不佳的TSQL(有些軟體可能會把相關資料庫語法編譯成一顆DLL)，
這時你會感謝SQL Server團隊設想週到，在這樣狀況下透過計畫指南來改善效能可能是唯一又最快的方法。
SQL Server計畫指南有object、sql、template三種類型，
可依照自己需求自行選用，下面我會示範透過透過 sp_create_plan_guide來建立常見的object和sql計畫指南，
以及使用sp_control_plan_guide移除、停用或啟用計畫指南。
1.附加recompile 提示
exec sp_executesql
@stmt = N'select * from Sales.SalesOrderDetail where ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 712
go
exec sp_executesql
@stmt = N'select * from Sales.SalesOrderDetail where ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 942
go
查詢1資料共3382筆，查詢2資料共5筆，但兩者執行計畫卻相同(相當不合理)，
下面我們透過plan guide並加上recompile強制每一次查詢都需重新編譯優化。
exec sp_create_plan_guide
@name = N'UseRecompileFix',
@stmt = N'select * from Sales.SalesOrderDetail where ProductID = @ProductID',
@type = N'SQL',--使用 sql 類型
@module_or_batch = NULL,
@params = N'@ProductID int',
@hints = N'OPTION (RECOMPILE)'
go
在一次執行相同查詢可以看到執行計畫有所改變
這次查詢1執行計畫顯然合理多了。
透過SSMS並展開可程式節點，即可看到該計畫指南
2.使用Loop避開高記憶體用量的hash或merge
create proc usp_mytest1
as
set nocount on
select t1.name,t1.CatalogDescription,t2.name as [ProductName]
from Production.ProductModel t1
left join Production.Product t2
on t1.ProductModelID=t2.ProductModelID
order by t2.name
查詢最佳化程式選用merge join運算子(記憶體授權3632)，
但如果在不同系統環境有記憶體壓力時，我們可以透過計畫指南來避開該運算子 。
exec sp_create_plan_guide
@name = N'UseLoopJoinFix',
@stmt = N'select t1.name,t1.CatalogDescription,t2.name as [ProductName]
from Production.ProductModel t1
left join Production.Product t2
on t1.ProductModelID=t2.ProductModelID
order by t2.name ',
@type = N'OBJECT',--使用 object 類型
@module_or_batch = 'usp_mytest1',
@params = null,
@hints = N'OPTION (Loop join)'
go
記憶體授權降低為3008。
如有使用計畫指南則會在屬性看到該計畫指南名稱。
停用、啟用和刪除計畫指南語法如下
--停用
exec sp_control_plan_guide N'disable', N'UseLoopJoinFix';
--啟用
exec sp_control_plan_guide N'enable', N'UseLoopJoinFix';
--刪除
exec sp_control_plan_guide N'drop', N'UseLoopJoinFix';