SQLServer中的執行計畫緩衝由於長時間緩衝對效能造成的幹擾

標籤：image   文法   根據   目的   bst   文字編輯器   條件   計劃   相同   

 

本文出處：http://www.cnblogs.com/wy123/p/7190785.html 

（保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了連結到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他）

 

先拋出一個效能問題，前幾天遇到一個生產環境效能極其低下的預存程序，開發人員根據具體的商務邏輯和返回的資料量，猜測到這個預存程序的執行應該不會有這麼慢。
當時意識到可能是執行計畫緩衝的問題，因為當前這個預存程序的寫法還是比較遵守參數化SQL的規範的（如果是動態即席查詢SQL就不會有問題了）
有意思的是，開發人員提供的相關參數，跟編譯時間候的參數竟然是一模一樣的，這也是本文重點要表達的重點。
於是去查詢當前伺服器上緩衝的執行計畫的到底是怎麼樣的，緩衝這個執行計畫的時間，以及當前緩衝的這個執行計畫編譯時間候的參數。
在查詢到對應預存程序緩衝的執行計畫之後，發現其執行計畫，確實跟當前直接帶入參數執行SQL的執行計畫有很大的差異。
當然該問題不完全與Parameter Sniffing完全一致，因此暫不討論Parameter Sniffing問題。
執行計畫由於已經被緩衝了起來，當前查詢也命中了先前執行計畫的緩衝，但緩衝的這個執行計畫對目前的查詢來說並可能不是最（相對）最佳化的，
畢竟該執行計畫已經緩衝了超過1天了。
那麼，緩衝的執行計畫到底適不適合當前語句的執行？如果不適合於當前語句的執行，又該怎麼處理，類似問題從長遠看，該怎麼避免？

 

特定語句的執行計畫緩衝資訊

參考如下sql，查詢出來某些指定語句的執行計畫緩衝資訊。

  SELECT  st.Text,        SUBSTRING(            st.Text, (qs.statement_start_offset/2)+1,            ((CASE qs.statement_end_offset            WHEN -1 THEN DATALENGTH(st.Text)            ELSE qs.statement_end_offset            END - qs.statement_start_offset)/2)+1            ),        qp.query_plan,        qs.plan_handle,        qs.sql_handle,        DB_NAME(st.dbid) as dbname,        qs.creation_time,        qs.last_execution_time,        getdate() as currenttime,        qs.execution_count,        qs.last_worker_time,        qs.last_physical_reads,        qs.last_logical_reads,        qs.last_elapsed_timeFROM sys.dm_exec_query_stats qs     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st     OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp WHERE 1=1    and st.text like ‘%模糊比對預存程序中具體標記性的sql文本%‘order by creation_time desc 

對於類似如下的查詢結果，從查詢到的結果中可以看出，直接開啟query_plan的xml，可以看到這個預存程序中的語句的執行計畫情況，當前我這裡僅僅是一個樣本

 

 對於當前這個緩衝的執行計畫的編譯參數，可以將query_plan的xml資訊複製出來，用notepad++之類的文字編輯器格式化之後顯示，查詢器編譯時間候的參數，
參考，在xml資訊的最後面一部分，或者可以直接按照關鍵字搜尋ParameterList，就可以找到編譯執行計畫時候的具體的參數值了。
在ParameterList的子節點中column就是參數，ParameterCompiledValue的值就是編譯執行計畫的值。
這樣一來，就可以確定，緩衝的執行計畫在編譯的時候的參數與當前啟動並執行參數是否存在較大的差異，以及緩衝的執行計畫是在什麼產生的，緩衝了多久。

 

對於一開始提到的問題，結果就是當前執行的預存程序中的語句，其執行計畫已經被緩衝超過了一天，因為尚未帶到觸發“重編譯”的條件，該緩衝繼續保留在記憶體中。
鑒於資料庫中的資料是不斷變化的，當時編譯的執行計畫，在目前時間來看，即便是參數完全一致，也不一定適用於當前的查詢，效能問題也因此產生。
為什麼會編譯出來一個與當前完全不一致的執行計畫，並且緩衝到目前為止還沒有被清理？

個人猜測有兩個原因，不過也不完全確定，
一是基於當時的資料分布情況（統計資訊）得到的一個執行計畫，可能當時本身的統計資訊就不是準確的，但是有沒有外界因素促使執行計畫重編譯
二是當時編譯的執行計畫本身就是不合理的，執行計畫的編譯與多種因素相關，甚至是記憶體壓力也會導致無法編譯出來一個相對較優的執行計畫，
SQL Server執行計畫的產生，並不一定總是“最高效”的，只是相對高效的，在記憶體壓力小的時候編譯出來的執行計畫，可能與記憶體壓力大的時候編譯出來的執行計畫存在差異
其實第一條猜測的原因存在一定的自相矛盾，
後者的可能性更大，因為如果導致重編譯的因素沒有發生變化，同樣的參數，當前執行也會跟緩衝的執行計畫一樣，如果存在導致重編譯的因素，那麼緩衝的執行計畫本身也要被清理掉。

更何況如果緩衝了某一個較少機率出現的參數，或者類似於產生parameter sniff的參數問題（當然parameter sniff問題就另當別論），影響甚至就更大。

 

如下是某生產環境伺服器上的，可以看到，在業務預存程序或者SQL語句的執行計畫緩衝，多的可以緩衝了超過20天，另外還有緩衝的超過了3天，4天的。

 

 　　據觀察，SQL Server在對待執行計畫緩衝的記憶體佔用，是輕易不會去清理的，可以認為這部分記憶體（相對於資料緩衝）的要求優先順序是比較高的，
　　也就是說在data cache面臨較大壓力的時候（PLE可以低到一分鐘之內），部分執行計畫依然被緩衝的好好的。
　　很可惜，在某些情況下，緩衝的執行計畫，非但沒有提供更好的效能，反倒是因為不適用於當前的查詢，拖慢了效能。

 

執行計畫緩衝的基礎知識

1，什麼是執行計畫緩衝

　　SQL Server查詢引擎接收到sql語句之後，首先會對其進行文法，語義的解析，然後進行編譯，之後產生執行計畫，對於可滿足緩衝的要求的sql語句，SQLServer會對其進行緩衝。

2，執行計畫緩衝的作用

　　減少SQL的編譯，SQLServer接收到存在執行計畫緩衝的SQL語句的時候，可以直接使用緩衝的執行計畫進行執行，避免重新編譯造成的資源上和時間上的消耗。

3，當前執行計畫緩衝佔用的空間

　　參考如下SQL，可以使用sys.dm_os_memory_clerks或者sys.dm_exec_cached_plans  來查詢執行計畫緩衝已經佔用的空間。

--CACHESTORE_SQLCP,動態sql，先行編譯sql執行計畫select * from sys.dm_os_memory_clerkswhere type = ‘CACHESTORE_SQLCP‘--proc：預存程序--prepared：預定義語句--Adhoc：動態查詢select sum(size_in_kb)/1024.0/1024.0 as size_in_GB FROM(    SELECT  objtype ,              size_in_bytes/1024.0 AS  size_in_kb    FROM    sys.dm_exec_cached_plans      WHERE objtype IN ( ‘Proc‘,‘Prepared‘,‘Adhoc‘))t

如下是某伺服器上的執行計畫緩衝資訊，兩者查詢出來的結果基本上一致。

　　4，執行計畫緩衝可最大佔用的空間

　　　　執行計畫佔用的記憶體空間是一種稱之為Stolen Memory的記憶體類型，Stolen Memory包括執行計畫緩衝，以及Session即時計算所需要的記憶體，比如排序，彙總計算，hash join等等。
　　　　SQL Server中Stolen Memory的分配有一個公式，在SQL Server 2005 SP1之後，大概如下
　　　　如果Target Mermory（也即SQL Server可用的最大記憶體空間）

　　　　1）不超過8GB的情況下：Stolen Memory = Target Mermory * 75%  
　　　　2）如果Target Mermory在8GB~64GB之間：Stolen Memory = 8 * 75%  + （Target Mermory - 8）*50% 
　　　　3）如果Target Mermory大於64GB時：Stolen Memory = 8 * 75%  + （64 - 8）*50%  + （Target Mermory - 64）*25%

　　　　可見，隨著最大可用記憶體的增加，Stolen Memory並不是線性遞增的，而是增加程度趨於減少（增加程度的導數是減少的）
　　　　另外在SQL Server之後的版本中也在控制Stolen Memory的最大可用內容量，具體參考《SQL Server 2012 實施與管理實戰指南》+ 這裡。

　　　　需要注意的是，執行計畫緩衝可最大佔用的空間僅僅是Stolen Memory的一部分，並不是Stolen Memory的全部。

 　　　   那麼問題就來了，這裡可以認為，執行計畫緩衝可最大佔用的空間是無法直接控制的，
　　　　如果SQL Server的Target Mermory越大，Stolen Memory也就越大，Stolen Memory越大，執行計畫緩衝可用的記憶體空間就越大，
　　　　雖然執行計畫有老化清理機制，單個人感覺還不夠靈活，或者說可控不強（現在才明白，其他關係型資料庫，開放出來很多可配置的參數的作用了，當然不是說可以隨意配置，想改就改）
　　　　執行計畫緩衝佔用的記憶體空間越大，真的就越好嗎？
　　　　肯定不是，一開始提到的問題就歸因於此，正是因為Stolen Memory尚未得到記憶體壓力，執行計畫緩衝就一直儲存在記憶體中，但是儲存的相對較久的執行計畫，並非適合於當前的查詢。

　　5，關於即席查詢（ad hoc）SQL的配置

　　　　這一點根本文關係不大，簡單提一下，對於ad hoc，也即動態sql，因為其參數很有可能每次都發生變化，類似SQL就沒有必要緩衝了，
　　　　第一次執行的時候僅儲存一個執行計畫的“存根”而不是儲存整個執行計畫，只有相同參數的SQL執行超過一次只有才緩衝下來整個完整的執行計畫。

--開啟 optimize for ad hoc workloadssp_CONFIGURE ‘show advanced options‘,1reconfiguregosp_CONFIGURE ‘optimize for ad hoc workloads‘,1reconfigurego

這一點配置的意義何在？記憶體對資料庫來說是很重要的資源，如果Stolen Memory各項記憶體使用量都可以配置，在儘可能滿足Stolen Memory的基礎上，更多更久地快取資料才是王道。

 

如果解決潛在可能到期的執行計畫

　　　　上述分析說明，在Stolen Memory尚未感受到記憶體壓力，或者是執行計畫清理機制尚未清理老化的執行計畫緩衝的時候，執行計畫緩衝是依舊儲存在記憶體中的。
　　　　但是這種緩衝的執行計畫，因為是語句編譯的時候的資料進行產生的計劃，是不一定適合於當前查詢的，或者是對於當前查詢不是最佳化的。
　　　　這就需要，在某些情況下，需要人為地清理執行計畫緩衝。
　　　　SQL Server提供了一個系統功能DBCC FREEPROCCACHE去清理執行計畫緩衝，參數是上文提到的具體的執行計畫控制代碼（plan_handle），不加參數就全清了，注意一下即可。

-- Remove the specific plan from the cache.清楚特定的執行計畫DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);GO

　　　　對於DBCC FREEPROCCACHE去清理執行特定計畫快取，此種方式僅可以用來應急使用。
　　　　如果經常發生類似問題，於情於理，我個人覺得是說不過去的，絕對是DBA該負得起的責任。

更多的時候，已經緩衝過一段時間的執行計畫，哪些是本應該被清理的，那些是可以繼續緩衝使用的？這一點很難主觀地來界定。
甚至也沒有有效參考資訊，因為你不知道3天之前編譯的那個執行計畫，緩衝了三天之後，對於當前的查詢，是不是依舊是最（較）最佳化的。
這一點可以在實踐中根據具體的情況進行探索，比如可以根據產生實踐，清理超過1天的執行計畫緩衝，或者把實踐這個閾值設定的大一點，但是不建議太長，比如三天五天的
個人認為，既然執行計畫能夠緩衝，那麼被清理之後，依然能夠緩衝，但是清楚之後重編譯的執行計畫，之準確性上會有更大的提高，一次重編譯的代價也是值得的。
理想情況下，一個執行計畫編譯之後，能夠緩衝（重用執行計畫）一天，相對來說也是比較理想的了。

 

當然，這裡是不包括“採用with recompile語句預存程序的重編譯或者option(recompile)基於語句的重編”來避免執行計畫緩衝的。
執行計畫緩衝本是處於節約資源與提高效能方面的考慮，只不過是凡事都具體兩面性，有利必有弊。
正常情況下，也不建議採用with recompile或者option(recompile)來強制編譯預存程序或者SQL語句，大多數時候遵循好規範即可。

 

大概想了一下，使用類似如下SQL，採用動態執行DBCC FREEPROCCACHE的方式，可以達到預期的目的。
當然，執行方式，執行時間，時間閾值可以根據具體情況進行調整。

  if object_id(‘tempdb..#t_remove_expired_plan‘) is not null    drop table #t_remove_expired_planGOcreate table #t_remove_expired_plan(    id int identity(1,1),    plan_handle varbinary(500))GOinsert into #t_remove_expired_plan (plan_handle) select  qs.plan_handlefrom sys.dm_exec_query_stats qs where creation_time< dateadd(hh,-24,getdate())GOdeclare @exists_data bit = 1declare @v_plan_handle varbinary(500)declare @str_sql varchar(1000)while @exists_data = 1begin    select top 1 @v_plan_handle = plan_handle from #t_remove_expired_plan    if(@v_plan_handle is not null)    begin        execute sp_executesql N‘DBCC FREEPROCCACHE(@plan_handle)‘ ,N‘@plan_handle varbinary(500)‘,@plan_handle = @v_plan_handle    end    delete top (1) from #t_remove_expired_plan    if exists(select 1 from #t_remove_expired_plan)    begin        set @exists_data = 1    end    else     begin        set @exists_data = 0    endend

 

 

總結：

　　　執行計畫緩衝原本是為了減少編譯SQL帶來的資源以及時間上的消耗，在可存在可重用的執行計畫緩衝的情況下提高SQL的執行效率
　　　對於老化的緩衝計劃的處理方式，SQL Server有自己的清理機制，但是仍舊缺乏一定的靈活性。
　　　但是在某些特殊情況下，對於效能問題，緩衝的執行計畫可能會干擾到當前SQL的執行效率，
　　　可能會存外部環境沒有問題（伺服器資源，CPU，IO，記憶體，網路等），SQL本身寫法也沒有問題，也不會出現缺少索引等情況，但是執行效率打不到預期的情況，
　　　這時就好考慮是不是緩衝的執行計畫導致當前SQL採用了不合理的執行方式。
　

　　　微軟的平台，一直以來本著簡單易容，快速上手的特點，對使用者做了大量的封裝（屏蔽），在個人化的可配置化上，與其他資料庫尤其是開源產品相比顯得有些不足。
　　　不過可以通過間接的方式，來達到類似於“可配置化”的目的。
 

 

 

 

SQLServer中的執行計畫緩衝由於長時間緩衝對效能造成的幹擾