SQL Server 執行計畫緩衝

標籤：

標籤：SQL SERVER/MSSQL SERVER/資料庫/DBA/記憶體池/緩衝區

概述  

瞭解執行計畫對資料庫效能分析很重要，其中涉及到了語句效能分析與儲存，這也是寫這篇文章的目的，在瞭解執行計畫之前先要瞭解一些基礎知識，所以文章前面會講一些概念，學起來會比較枯燥，但是這些基礎知識非常重要。

 

目錄

•  概述
•  基礎概念
  •  怎樣緩衝執行計畫
  •  SQL Server自動刪除執行計畫
  •  重新編譯執行計畫
•  測試
  •  執行計畫相關係統視圖
  •  手動清空緩衝執行計畫
  •  測試索引更改對執行計畫的影響
  •  測試增加欄位對執行計畫的影響
•  總結

基礎概念

SQL Server 有一個用於儲存執行計畫和資料緩衝區的記憶體池。池內分配給執行計畫或資料緩衝區的百分比隨系統狀態動態波動。記憶體池中用於儲存執行計畫的部分稱為過程緩衝。

SQL Server 執行計畫包含下列主要組件：

• 查詢計劃

執行計畫的主體是一個重入的唯讀資料結構，可由任意數量的使用者使用。這稱為查詢計劃。查詢計劃中不儲存使用者上下文。記憶體中查詢計劃副本永遠不超過兩個：一個副本用於所有的串列執行，另一個用於所有的並存執行。並行副本覆蓋所有的並存執行，與並存執行的並行度無關。

• 執行內容

每個正在執行查詢的使用者都有一個包含其執行專用資料（如參數值）的資料結構。此資料結構稱為執行內容。執行內容資料結構可以重新使用。如果使用者執行查詢而其中的一個結構未使用，將會用新使用者的上下文重新初始化該結構。

怎樣緩衝執行計畫

SQL Server 有一個高效的演算法，可尋找用於任何特定 SQL 陳述式的現有執行計畫。在 SQL Server 中執行任何 SQL 陳述式時，關聯式引擎將首先查看過程緩衝中是否有用於同一 SQL 陳述式的現有執行計畫。SQL Server 將重新使用找到的任何現有計劃，從而節省重新編譯 SQL 陳述式的開銷。如果沒有現有執行計畫，SQL Server 將為查詢產生新的執行計畫。

SQL Server自動刪除執行計畫

什麼情況下會刪除執行計畫

在沒有人工手動清除緩衝的情況下，如果出現記憶體不足的情況下SQL Server會自動清除一部分沒被利用到的緩衝計劃。

所有緩衝的最大大小取決於max server memory的大小。

怎樣判斷需要刪除的執行計畫

果存在記憶體不足的情況，資料庫引擎將使用基於開銷的方法來確定從過程緩衝中刪除哪些執行計畫。怎樣確定一個執行計畫的開銷呢，對於一個第一次執行的執行計畫SQL Server將它的開銷值設為0，被多次執行過的執行計畫SQL Server將它的開銷值設定為原始編譯開銷，所以資料庫引擎會重複檢查每個執行計畫的狀態並將刪除當前開銷為零的執行計畫。如果存在記憶體不足的情況，當前開銷為零的執行計畫不會自動被刪除，而只有在資料庫引擎檢查該執行計畫並發現其當前開銷為零時，才會刪除該計劃。當檢查執行計畫時，如果當前沒有查詢使用該計劃，則資料庫引擎將降低當前開銷以將其推向零。

資料庫引擎會重複檢查執行計畫，直至刪除了足夠多的執行計畫，以滿足記憶體需求為止。如果存在記憶體不足的情況，執行計畫可多次對其開銷進行增加或降低。如果記憶體不足的情況已經消失，資料庫引擎將不再降低未使用執行計畫的當前開銷，並且所有執行計畫都將保留在過程緩衝中，即使其開銷為零也是如此。

重新編譯執行計畫

根據資料庫新狀態的不同，資料庫中的某些更改可能導致執行計畫效率降低或無效。SQL Server 將檢測到使執行計畫無效的更改，並將計劃標記為無效。此後，必須為執行查詢的下一個串連重新編譯新的計劃。導致計劃無效的情況包括：

• 對查詢所引用的表或視圖變更（ALTER TABLE 和 ALTER VIEW）。
• 對執行計畫所使用的任何索引變更。
• 對執行計畫所使用的統計資訊進行更新，這些更新可能是從語句（如 UPDATE STATISTICS）中顯式產生，也可能是自動產生的。
• 刪除執行計畫所使用的索引。
• 顯式調用 sp_recompile。
• 對鍵的大量更改（其他使用者對由查詢引用的表使用 INSERT 或 DELETE 語句所產生的修改）。
• 對於帶觸發器的表，插入的或刪除的表內的行數顯著增長。
• 使用 WITH RECOMPILE 選項執行預存程序。

測試執行計畫相關係統視圖

--1.緩衝的每一個對象返回一行，包括緩衝計劃的類型、緩衝引用的對象、緩衝計劃佔用的空間、被使用次數、以及建立時間等SELECT * FROM sys.syscacheobjects;--2.緩衝的每個查詢計劃返回一行，包括執行計畫被使用的次數、執行計畫的大小、記憶體位址、執行計畫的類型、語句等SELECT * FROM sys.dm_exec_cached_plans;GO---3.返回由指定的 sql_handle 標識的 SQL 批處理的文本/*其中sql_handle來自:sys.dm_exec_query_statssys.dm_exec_requestssys.dm_exec_cursorssys.dm_exec_xml_handlessys.dm_exec_query_memory_grantssys.dm_exec_connectionsplan_handle來自:sys.dm_exec_cached_plans*/ SELECT * FROM sys.dm_exec_sql_text(sql_handle | plan_handle);GO--4.以 XML 格式返回計劃控制代碼指定的批查詢的執行程序表，主要接受來自sys.dm_exec_cached_plans的plan_handle控制代碼SELECT * FROM sys.dm_exec_query_plan(plan_handle);GO--5.每個計劃屬性返回一行，主要接受來自sys.dm_exec_cached_plans的plan_handle控制代碼SELECT * FROM sys.dm_exec_plan_attributes(plan_handle);GO--6.針對每個 Transact-SQL 執行計畫、通用語言執行平台 (CLR) 執行計畫和與計劃關聯的遊標返回一行,，主要接受來自sys.dm_exec_cached_plans的plan_handle控制代碼SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(plan_handle);--7.返回緩衝查詢計劃的彙總效能統計資訊。緩衝計劃中的每個查詢語句在該視圖中對應一行，並且行的生存期與計劃本身相關聯。在從緩衝刪除計劃時，也將從該視圖中刪除對應行。*/--該系統檢視表針對每一個緩衝中的執行計畫統計其執行時間、物理、邏輯操作等資訊SELECT * FROM sys.dm_exec_query_stats 

手動清空緩衝執行計畫

--清空緩衝中的執行計畫DBCC FREEPROCCACHE; -- ( plan_handle | sql_handle | pool_name )GO-- 清空制定資料庫的執行計畫DBCC FLUSHPROCINDB(<dbid>);GO---清空緩衝中的資料DBCC DROPCLEANBUFFERS;---清空特定緩衝儲存區中的執行計畫DBCC FREESYSTEMCACHE(<cachestore>) -- ‘ALL‘, pool_name, ‘Object Plans‘, ‘SQL Plans‘, ‘Bound Trees‘GO

測試索引更改對執行計畫的影響

---清空制定資料庫執行計畫DECLARE @DBID INTSET @DBID=DB_ID()DBCC FLUSHPROCINDB(@DBID);GO---建立測試資料庫CREATE TABLE TPlan(ID INT PRIMARY KEY IDENTITY(1,1),Name NVARCHAR(20) NOT NULL,Istate INT NOT NULL,Idate DATETIME DEFAULT(GETDATE()))GO---建立索引CREATE INDEX IX_TPlan_NAME ON TPlan(Name)GOINSERT INTO TPlan(Name,Istate)VALUES(‘1‘,1),(‘2‘,2),(‘3‘,3)GOSELECT NAME FROM TPlanGOSELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjectsWHERE DBID=DB_ID()

 

 

 使用Profiler監控

使用SQL:StmtRecompile監控，如果是監控儲存區過程則使用：SP:Recompile

 修改索引

在索引中添加欄位

DROP INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] WITH ( ONLINE = OFF )GOUSE [Study]GOCREATE NONCLUSTERED INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] (    [Name] ASC)INCLUDE ( [Istate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]GO

再執行查詢

SELECT NAME FROM TPlan

 

測試增加欄位對執行計畫的影響

增加查詢非相關欄位

ALTER TABLE [dbo].[TPlan] ADD Number INT

 刪除查詢有關的索引也同樣會導致執行計畫重編譯，這裡就不貼出來了。

 查看執行計畫

SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjectsWHERE DBID=DB_ID()

執行計畫中顯示了該執行計畫被調用了兩次，在隨機叢書中寫的是會重新編譯新的執行計畫，如果是這樣的話那這裡的值應該是1才對。

猜測：SQL Server在架構更改的時候通過檢測執行計畫已經對原先的執行計畫進行了編譯，所以在新的查詢中還是使用了第一次查詢的執行計畫。

如果有誰知道結果麻煩告知。

 

總結

 

 

 

 如果文章對大家有協助，希望大家能給個推薦，謝謝！！！

 

備忘：     pursuer.chen     部落格：http://www.cnblogs.com/chenmh 本網站所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。 《歡迎交流討論》

 

SQL Server 執行計畫緩衝