SQL Server如何固定執行計劃

原文: SQL Server如何固定執行計劃

   SQL Server 其實從SQL Server 2005開始，也提供了類似ORACLE中固定執行計劃的功能，隻是好像很少人使用這個功能。當然在SQL Server中不叫"固定執行計劃"這個概念，而是叫"執行計劃指南"（Plan Guide 很多翻譯是計劃指南，個人覺得執行計劃指南稍好一些）。當然兩者雖然概念與命名不同，實質上它們所說的是相同的事情，當然商業包裝是很常見的事情。個人還是覺得“固定執行計劃”這個概念叫起來順口,通俗易懂，執行計劃指南（Plan Guide）叫起來老感覺非常拗口，不知所雲（後面會在這兩個概念切換，你知道我所說的是一件事情就好）。其實我以前也很少使用這些功能，直到最近在SQL Server 2014資料庫中使用固定執行計劃解決了幾個SQL的性能問題，是以覺得還是有必要總結、歸納一下。

為什麼要固定執行計劃？

為什麼要使用固定執行計劃（Plan Guid）呢？ 個人簡單的從下面幾個方面介紹一下，如有不足，敬請指正。個人也是在探索當中。

由于一些特殊原因（例如Parameter Sniffing、統計資訊的變化或采樣比例低造成的統計資訊出現偏差、或其他像SQL Server 2014新的基數評估（Cardinality Estimator）特性引起優化器選擇不合适的JOIN操作等等），導緻某個SQL的執行計劃出現很大偏差，當資料庫優化器為SQL選擇了一個糟糕的執行計劃時，就可能出現嚴重性能問題，我就碰到過這樣一個例子，在SQL Server 2014中，有一個SQL的執行頻率較頻繁，有時候優化器突然選擇了一個較差的執行計劃時，這時就會出現嚴重的性能問題。是以，這個時候，我們就必須使用Plan Guide固定這個執行計劃，進而讓優化器使用正确的執行計劃，進而解決這樣的性能問題。

另外一方面，因為優化器生成執行計劃本身是很複雜的過程，我們所能幹涉的不多，最多使用HINT提示來改變執行計劃。而且優化器基于一些算法和開銷考慮，也有可能生成的執行計劃不是最優執行計劃，而Plan Guid是DBA管理資料庫的一件利器，如果你發現了一個比目前更好的執行計劃，也能使用執行計劃指南固定這個SQL的執行計劃。當然這種情況非常、非常少，至少我在生産環境使用得不多。

有時候,某個系統是購買供應商的，你發現資料庫裡面有大量幾乎相同的SQL解析，然後緩存了，其實你發現這些SQL完全可以隻解析一次，完全可以參數化，沒有必要大量解析。但是現在供應商沒有提供技術支援了，不可能去優化代碼裡面的SQL語句，那麼你也可以使用執行計劃指南來幫你解決這個問題。

還有就是使用Plan Guide來調優，對比不同的執行計劃的優劣。當然應該還有一些其它應用場景，隻是我沒有碰到過而已。

如何固定執行計劃？

Plan Guide主要用到下面幾個存儲，關于這些系統存儲過程的使用方法、功能介紹，官方文檔有詳細的介紹。在此就不畫蛇添足了。

sys.sp_create_plan_guide,

sys.sp_create_plan_guide_from_handle,

sys.sp_control_plan_guide

下面我們還是看看一些應用場景案例吧！構造一個合适、貼切的例子實在是太花精力和時間，生産環境案例又不能搬出來，我們先來看看官方文檔提供的例子吧，如下SQL所示，在測試資料庫AdventureWorks2014,該SQL使用Nested Loop關聯兩個表

SELECT COUNT(*) AS c      

FROM Sales.SalesOrderHeader AS h      

INNER JOIN Sales.SalesOrderDetail AS d      

  ON h.SalesOrderID = d.SalesOrderID      

WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';      

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112207359-1394191895.png

假如（注意這裡是假設）發現如果這個SQL中，兩個表使用MERGE JOIN的方式，效率更高，那麼我們可以使用sp_create_plan_guide來建立執行計劃指南（固定執行計劃），如下所示

EXEC sp_create_plan_guide       

    @name = N'my_table_jon_guid',      

    @stmt = N'SELECT COUNT(*) AS c      

FROM Sales.SalesOrderHeader AS h      

INNER JOIN Sales.SalesOrderDetail AS d      

  ON h.SalesOrderID = d.SalesOrderID      

WHERE h.OrderDate >= ''20000101'' AND h.OrderDate <=''20050101'';',      

    @type = N'SQL',      

    @module_or_batch = NULL,      

    @params = NULL,      

    @hints = N'OPTION (MERGE JOIN)';      

那麼此時再執行這個SQL時，你就會發現執行計劃就會變成Merge Join方式了。 這樣好過在SQL Server中使用HINT，為什麼呢？ 有可能這個SQL是寫死在應用程式裡面，如果以後這個執行計劃變成了一個糟糕的執行計劃，維護的成本非常高（一方面如果沒有記錄，需要耗費精力去定位、查找這段SQL，另外一方面，DBA是沒有權限接觸這些應用程式代碼的，可能需要你溝通、協調開發人員、運維人員。耗費無數的時間、精力.....,還有可能其他接手維護的人不了解情況等等），而使用執行計劃指南，那麼你查找、禁用、删除這個執行計劃指南即可。非常友善、高效，也許你一分鐘就能搞定，如果是Hint，說不定處理完，需要幾天，想必這樣的耗費精力溝通、協調的事情很多人都遇到過。

SELECT COUNT(*) AS c      

FROM Sales.SalesOrderHeader AS h      

INNER MERGE JOIN Sales.SalesOrderDetail AS d      

  ON h.SalesOrderID = d.SalesOrderID      

WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';      

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112209234-270711924.png

另外，我們再來構造一個例子，模拟系統裡面出現大量解析的SQL語句的案例，如下所示

USE AdventureWorks2014;      

GO      

SET NOCOUNT ON;      

GO      

DROP TABLE TEST      

GO      

CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(8));      

GO      

CREATE INDEX PK_TEST ON TEST(OBJECT_ID);      

GO      

DECLARE @Index INT =1;      

WHILE @Index <= 10000      

BEGIN      

    INSERT INTO TEST      

    SELECT @Index, 'kerry';      

    SET @Index = @Index +1;      

END      

GO      

UPDATE STATISTICS  TEST WITH FULLSCAN;      

GO      

構造了上面案例後，我們清空該資料庫所有緩存的執行計劃（僅僅是為了幹淨的測試環境，避免以前緩存的執行計劃影響實驗結果）,生産環境你不能使用DBCC FREEPROCCACHE清空所有緩存的執行計劃，但是可以用DBCC FREEPROCCACHE删除特定的執行計劃。

DBCC FREEPROCCACHE;

GO

然後我們開始測試我們的例子，假設系統裡面有大量類似的SQL語句，數量驚人（我們僅僅測試四個）。如果這個系統是從供應商那裡購買的，現在又沒有技術支援和Support的人（或者及時有人Support，但是不嚴重影響使用的情況，人家不想花費精力去優化），沒有人協助你優化這些SQL，你又不能将資料庫參數“參數化”從簡單設定為強制（因為影響太大，而且沒有測試，不确定是否帶來潛在的性能問題）.....

SELECT * FROM TEST WHERE OBJECT_ID=1;      

GO      

SELECT * FROM TEST WHERE OBJECT_ID=2;      

GO      

SELECT * FROM TEST WHERE OBJECT_ID=3;      

GO      

SELECT * FROM TEST WHERE OBJECT_ID=4;      

GO      

....................................................................      

此時檢視執行計劃,發現緩存了4個執行計劃

SELECT qs.sql_handle,      

       qs.statement_start_offset,      

       qs.statement_end_offset,      

       qs.plan_handle,      

       qs.creation_time,      

       qs.execution_count,      

       qs.query_hash,      

       qs.query_plan_hash,      

       st.text,      

       qp.query_plan      

FROM sys.dm_exec_query_stats AS qs      

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st      

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp      

WHERE text LIKE N'%SELECT * FROM TEST WHERE OBJECT_ID%' AND text NOT LIKE 'SELECT qs.sql_handle%';      

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112210937-591761618.png

那麼此時，執行計劃指南就能發揮其作用了,使用sp_create_plan_guide建立執行計劃指南，強制SELECT * FROM TEST WHERE OBJECT_ID=xxx這樣的SQL參數化

DECLARE @stmt nvarchar(max);      

DECLARE @params nvarchar(max);      

EXEC sp_get_query_template N'SELECT * FROM TEST WHERE OBJECT_ID=1',      

@stmt OUTPUT,       

@params OUTPUT;      

EXEC sp_create_plan_guide N'my_sql_parameter_test',       

    @stmt,       

N'TEMPLATE',       

NULL,       

@params,       

N'OPTION(PARAMETERIZATION FORCED)';      

然後我們執行下面指令，清空該資料庫所有緩存的執行計劃，然後執行上面四個SQL語句

DBCC FREEPROCCACHE;      

GO      

SELECT * FROM TEST WHERE OBJECT_ID=1;      

SELECT * FROM TEST WHERE OBJECT_ID=2;      

SELECT * FROM TEST WHERE OBJECT_ID=3;      

SELECT * FROM TEST WHERE OBJECT_ID=4;      

你會發現他們全部使用執行計劃指南裡面的執行計劃了。不用多次解析了。

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112212843-1043996059.png

還是使用上面的例子，我們來解決一個Parameter Sniffing（參數嗅探）的問題，在實驗前，我們先删除前面建立的Plan Guide，以免這個影響測試結果，

EXEC sp_control_plan_guide @operation=N'DROP', @name=N'my_sql_parameter_test';      

我們構造一個資料傾斜的案例，這樣友善我們示範

UPDATE dbo.TEST SET OBJECT_ID =1 WHERE OBJECT_ID <=2000;      

UPDATE STATISTICS dbo.TEST WITH FULLSCAN;      

然後我們建立一個簡單的存儲過程Proc_Parameter_Sniffing

CREATE PROCEDURE Proc_Parameter_Sniffing      

( @Object_ID  INT)      

AS       

BEGIN      

    SELECT * FROM TEST WHERE OBJECT_ID=@Object_ID;      

END      

GO      

接下來，我們清空緩存的執行計劃，然後執行存儲過程，參數為1

DBCC FREEPROCCACHE;      

GO      

EXEC Proc_Parameter_Sniffer 1;      

然後我們檢視這個存儲過程的實際執行計劃，如下所示，将Query_Plan這些XML拷貝出來并格式化

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112214484-575130696.png

1 <Batch>

2 <Statements>

3 <StmtSimple StatementText="SELECT * FROM TEST WHERE OBJECT_ID=@Object_ID" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0350227" StatementEstRows="2000" StatementOptmLevel="FULL" QueryHash="0xA99C3EB3A64627F3" QueryPlanHash="0x50042F73B31C8535" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120">

4 <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/>

5 <QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="152">

6 <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/>

7 <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="26214" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="3112816"/>

8 <RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="2000" EstimateIO="0.0238657" EstimateCPU="0.011157" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0350227" TableCardinality="10000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

9 <OutputList>

10 <ColumnReference Database="[AdventureWorks2014]" Schema="[dbo]" Table="[TEST]" Column="OBJECT_ID"/>

11 <ColumnReference Database="[AdventureWorks2014]" Schema="[dbo]" Table="[TEST]" Column="NAME"/>

12 </OutputList>

13 <TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

14 <DefinedValues>

15 <DefinedValue>

16 <ColumnReference Database="[AdventureWorks2014]" Schema="[dbo]" Table="[TEST]" Column="OBJECT_ID"/>

17 </DefinedValue>

18 <DefinedValue>

19 <ColumnReference Database="[AdventureWorks2014]" Schema="[dbo]" Table="[TEST]" Column="NAME"/>

20 </DefinedValue>

21 </DefinedValues>

22 <Object Database="[AdventureWorks2014]" Schema="[dbo]" Table="[TEST]" IndexKind="Heap" Storage="RowStore"/>

23 <Predicate>

24 <ScalarOperator ScalarString="[AdventureWorks2014].[dbo].[TEST].[OBJECT_ID]=[@Object_ID]">

25 <Compare CompareOp="EQ">

26 <ScalarOperator>

27 <Identifier>

28 <ColumnReference Database="[AdventureWorks2014]" Schema="[dbo]" Table="[TEST]" Column="OBJECT_ID"/>

29 </Identifier>

30 </ScalarOperator>

31 <ScalarOperator>

32 <Identifier>

33 <ColumnReference Column="@Object_ID"/>

34 </Identifier>

35 </ScalarOperator>

36 </Compare>

37 </ScalarOperator>

38 </Predicate>

39 </TableScan>

40 </RelOp>

41 <ParameterList>

42 <ColumnReference Column="@Object_ID" ParameterCompiledValue="(1)"/>

43 </ParameterList>

44 </QueryPlan>

45 </StmtSimple>

46 </Statements>

47 </Batch>

48 </BatchSequence>

49 </ShowPlanXML>

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112215562-566432583.png

如下所示，目前它确實是使用準确的執行計劃，進行全表掃描（TableScan)，如果此時使用其它參數（例如下面SQL），就會出現Parameter Sniffer(參數嗅探）問題，這個是因為SQL Server在處理存儲過程的時候，是一次編譯，多次重用，執行計劃重用。是以當參數為2500的時候，執行計劃依然是進行全表掃描（TableScan），這個時候，全表掃描顯然是一個糟糕的執行計劃。

EXEC Proc_Parameter_Sniffer 2001;

而且，大部分資料應該做Index Seek是一個較優的執行計劃，隻有Object_ID=1這樣的特殊資料，全部掃描才是一個較優的執行計劃，假如實際使用環境中，也很少用到Object_ID=1這樣的查詢，那麼我們可以固定執行計劃，讓其使用參數2001的執行計劃

EXEC sp_create_plan_guide       

    @name = N'parameter_sniffing_guid',      

    @stmt = N'SELECT * FROM TEST WHERE OBJECT_ID=@Object_ID',      

    @type = N'OBJECT',      

    @module_or_batch =N'Proc_Parameter_Sniffing',      

    @params = NULL,      

    @hints = N'OPTION(optimize for(@Object_ID=2001))';      

然後我們再次調用EXEC Proc_Parameter_Sniffer 1;時，你會發現該SQL的執行計劃變更為索引查找了。

http://images2015.cnblogs.com/blog/73542/201701/73542-20170118112217328-276485100.png

當然實際生産環境中，情況往往比較複雜，絕不可能有這麼簡單、理想的環境出現，往往還需要根據實際情況、權衡利弊，多方考慮才能指定一個折中的方案。具體問題具體分析、不能依葫蘆畫瓢。理論要結合實際情況。

檢視執行計劃指南

檢視執行計劃指南非常資訊非常簡單，你隻需要查詢sys.plan_guides即可。

SELECT * FROM sys.plan_guides;

另外，啟用、禁用、删除執行計劃指南都是通過一個系統存儲過程sys.sp_control_plan_guide來實作的，使用非常簡單。下面僅僅簡單舉幾個例子。sys.sp_control_plan_guide的存儲過程如下,實際上它都是封裝調用了sys.sp_control_plan_guide_int的功能

SET QUOTED_IDENTIFIER ON      

SET ANSI_NULLS ON      

GO      

create procedure sys.sp_control_plan_guide      

    @operation nvarchar(60),      

    @name sysname = NULL      

as      

BEGIN TRANSACTION      

declare @return_code int      

if( lower(@operation) = 'drop' OR lower(@operation) = 'enable' OR lower(@operation) = 'disable')      

    exec @return_code =  @operation, @name      

else      

    exec @return_code = sys.sp_control_plan_guide_int @operation      

if( @return_code = 0 )      

begin      

    if( lower(@operation) = 'drop' OR lower(@operation) = 'drop all')      

    begin      

    EXEC %%System().FireTrigger(ID = 238, ID = 27, ID = 0, ID = 0, Value = @name,      

            ID = -1, ID = 0, ID = 0, Value = NULL, ID = 2,      

            Value = @operation, Value = @name, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)      

    end      

    else      

    begin      

    EXEC %%System().FireTrigger(ID = 216, ID = 27, ID = 0, ID = 0, Value = @name,      

            ID = -1, ID = 0, ID = 0, Value = NULL, ID = 2,      

            Value = @operation, Value = @name, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)      

    end      

end      

COMMIT TRANSACTION      

GO      

禁用執行計劃指南

1：禁用名字為my_sql_plan_test的執行計劃指南

USE AdventureWorks2014;      

GO      

EXEC sp_control_plan_guide @operation=N'DISABLE', @name=N'my_sql_plan_test'      

2：禁用所有的執行計劃指南

USE AdventureWorks2014;      

GO      

EXEC sys.sp_control_plan_guide @operation = N'DISABLE ALL';      

确切的說，應該是禁用資料庫AdventureWorks2014下所有的執行計劃指南。

啟用執行計劃指南

1：啟用名字為my_sql_plan_test的執行計劃指南

USE AdventureWorks2014;      

GO      

EXEC sp_control_plan_guide @operation=N'ENABLE', @name=N'my_sql_plan_test';      

2：啟用所有的執行計劃指南

USE AdventureWorks2014;      

GO      

EXEC sys.sp_control_plan_guide @operation = N'ENABLE ALL';      

确切的說，應該是啟用資料庫AdventureWorks2014下所有被禁用的執行計劃指南。

删除執行計劃指南

删除執行計劃指南非常簡單，如下所示

我們首先檢視有執行計劃指南，找到想要删除的Plan Guide，例如，我們想删除命名為my_sql_plan_test的執行計劃指南。

EXEC sp_control_plan_guide @operation=N'DROP', @name=N'my_sql_plan_test';      

參考資料：

https://technet.microsoft.com/zh-cn/library/ms188255(v=sql.105).aspx https://technet.microsoft.com/zh-cn/library/bb964726(v=sql.105).aspx https://msdn.microsoft.com/zh-cn/library/ms179880.aspx