SQLServer中的執行計劃緩存由於長時間緩存對性能造成的干擾

来源:http://www.cnblogs.com/wy123/archive/2017/07/16/7190785.html
-Advertisement-
Play Games

本文出處：http://www.cnblogs.com/wy123/p/7190785.html （保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了鏈接到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他） 先拋出一個性能問題，前幾天遇到一個生產環境性能極其低下的存儲過程，開發人員 ...


 

本文出處：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_time
FROM 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_clerks
where 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越大，執行計劃緩存可用的記憶體空間就越大，
　　　　雖然執行計劃有老化清理機制，但個人感覺還不夠靈活，或者說可控不強（現在才明白，其他關係型資料庫，開放出來很多可配置的參數的作用了，當然不是說可以隨意配置，想改就改）
　　　　執行計劃緩存占用的記憶體空間越大，真的就越好嗎？或者說，SQL Server伺服器的記憶體供給的越大，性能就會一定越好？
　　　　肯定不是，一開始提到的問題就歸因於此，正是因為Stolen Memory尚未得到記憶體壓力，執行計劃緩存就一直存儲在記憶體中，但是存儲的相對較久的執行計劃，並非適合於當前的查詢。

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

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

--開啟 optimize for ad hoc workloads
sp_CONFIGURE 'show advanced options',1
reconfigure
go
sp_CONFIGURE 'optimize for ad hoc workloads',1
reconfigure
go

這一點配置的意義何在？記憶體對資料庫來說是很重要的資源，如果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的方式，可以達到預期的目的。
當然，執行方式，執行時間，時間閾值可以根據具體情況進行調整。

註意 execute sp_executesql的方式執行sql語句，變數類型不支持max類型的參數，因此定義的參數變數類型是varbinary(500)

  
if object_id('tempdb..#t_remove_expired_plan') is not null
    drop table #t_remove_expired_plan
GO

create table #t_remove_expired_plan
(
    id int identity(1,1),
    plan_handle varbinary(500)
)
GO


insert into #t_remove_expired_plan (plan_handle) 
select  qs.plan_handle
from sys.dm_exec_query_stats qs 
where creation_time< dateadd(hh,-24,getdate())
GO


declare @exists_data bit = 1
declare @v_plan_handle varbinary(500)
declare @str_sql varchar(1000)
while @exists_data = 1
begin
    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
    end
end

 

 

總結：

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

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

 

 

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Android 最新 Support V4 包大拆分有用嗎？
    Google 更新了最新的 Support Library 版本，其中最為顯眼的功能莫過於 support-v4 大拆分，然後這個拆分現在看來並沒有那麼美好。 v4 包從 2011 年開始引入，包含 ViewPager、FragmentActivity 等我們常用的功能，目前已經達到 1.3 M，G ...
  • 即刻開始使用Kotlin開發Android的12個原因（KAD 30）
    原作者：現在是實踐所有已經學習到Kotlin技術，以及充分利用它提供功能的時候。如果你還有任何疑問，在本文就給你一些做出最終決定的理由。 ...
  • 在AndroidManifest（清單文件）中註冊activity（活動）及配置主活動、更改App圖標、App名稱、修改隱藏標題欄
    打開app/src/main/AndroidManifest。 1.註冊當前活動。通過<activity android:name>標簽註冊當前活動，Android studio會自動註冊，eclipse需要手動註冊。.MainActivity其中 . 表示包名，在上面package（包）中已經註冊 ...
  • 從源碼的角度看Service是如何啟動的
    七月中旬了，大家的實習有著落了嗎？秋招又準備的怎麼樣了呢？我依舊在準備著秋招，每當想到自己以應屆生的身份找著工作而工作卻不一定要你的時候，難免也會有點失落。互聯網行業的大佬們求賢若渴但對賢才也十分的苛刻，看到內推正如火如荼的進行著，深怕自己被這場浪潮甩在身後，所以也不得不苦心的準備著。如果你也是20... ...
  • MySQL優化
    1.表結構 2.數據類型 3.索引 4.約束 為欄位設定not null非空約束，因為null不僅占據更多的空間，還使對比與索引變得複雜。 5.SQL語句 6.緩存 現在我們大多數時候都是通過ORM框架訪問數據，這些框架往往提供緩存功能（一級緩存或者二級緩存），開啟緩存可以減少訪問資料庫的次數，不僅 ...
  • Spark機器學習(9)：FPGrowth演算法
    關聯規則挖掘最典型的例子是購物籃分析，通過分析可以知道哪些商品經常被一起購買，從而可以改進商品貨架的佈局。 1. 基本概念 首先，介紹一些基本概念。 (1) 關聯規則：用於表示數據內隱含的關聯性，一般用X表示先決條件，Y表示關聯結果。 (2) 支持度(Support)：所有項集中{X,Y}出現的可能 ...
  • MySQL · 引擎特性 · InnoDB奔潰恢復
    前言 資料庫系統與文件系統最大的區別在於資料庫能保證操作的原子性，一個操作要麼不做要麼都做，即使在資料庫宕機的情況下，也不會出現操作一半的情況，這個就需要資料庫的日誌和一套完善的奔潰恢復機制來保證。本文仔細剖析了InnoDB的奔潰恢復流程，代碼基於5.6分支。 基礎知識 lsn: 可以理解為資料庫從 ...
  • MySQL簡介
    一 資料庫常用操作 mysql -u+username -p+password：登陸資料庫管理系統，如mysql -uroot -p123。 create database dbName：創建資料庫。 drop database dbName：刪除資料庫。 use dbName：使用指定資料庫，因為 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類