SQL Server 執行計劃快取-知識星球

概述

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

1. 概述
2. 基礎概念
2.1 怎樣快取執行計劃
2.2 SQL Server自動刪除執行計劃
2.3 重新編譯執行計劃
3. 測試
3.1 執行計劃相關係統檢視
3.2 手動清空快取執行計劃
3.3 測試索引更改對執行計劃的影響
3.4 測試增加欄位對執行計劃的影響
4. 總結

基礎概念

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_stats

sys.dm_exec_requests

sys.dm_exec_cursors

sys.dm_exec_xml_handles

sys.dm_exec_query_memory_grants

sys.dm_exec_connections

plan_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();

GO

---清空快取中的資料

DBCC DROPCLEANBUFFERS;

---清空特定快取儲存區中的執行計劃

DBCC FREESYSTEMCACHE() -- 'ALL', pool_name, 'Object Plans', 'SQL Plans', 'Bound Trees'

GO

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

---清空制定資料庫執行計劃

DECLARE @DBID INT

SET @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

)

GO

INSERT INTO TPlan(Name,Istate)

VALUES('1',1),('2',2),('3',3)

GO

SELECT NAME FROM TPlan

GO

SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects

WHERE DBID=DB_ID()

使用Profiler監控

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

修改索引

在索引中新增欄位

DROP INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] WITH ( ONLINE = OFF )

GO

USE [Study]

GO

CREATE 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.syscacheobjects

WHERE DBID=DB_ID()

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

猜測：SQL Server在架構更改的時候透過檢測執行計劃已經對原先的執行計劃進行了編譯，所以在新的查詢中還是使用了第一次查詢的執行計劃。如果有誰知道結果麻煩告知。

作者：pursuer.chen

連結：http://blog.jobbole.com/86275/

SQL Server 執行計劃快取

概述

目錄