SQL Server 利用Profiler觀察執行計劃是否重用時SP:Cachemiss，SP:CacheInsert以及SP:CacheHit的含義

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

執行計劃的緩存與重用

在通過SQL Profile觀察一個SQL語句或者存儲過程是否有可用的緩存執行計劃的時候，
通過SP:CacheMiss和SP:CacheHit事件可以說明是否發生了編譯/重編譯和是否重用了緩存的執行計劃，
但是對於SP:CacheMiss這一細節，還是存在不少理解錯誤的情況的，本文通過一個簡單的例子來解釋說明SP:CacheMiss所表達的真實含義。

簡單建個測試表，來測試使用

CREATE TABLE Test
(
    Id INT,
    NAME VARCHAR(100)
)
GO

DECLARE @i INT = 0
WHILE @i<100000
BEGIN
    INSERT INTO Test VALUES(@i,NEWID())
    SET @i = @i+1
END
GO

CREATE INDEX IX_Id ON Test(Id)
GO

如何利用Profile觀察SQL語句的執行計劃的編譯/重編譯和重用？

　　眾所周知的，參數化SQL的執行計劃可以緩存並重用，那麼就以如下一個簡單的參數化SQL為例，觀察在傳入不同參數時的執行計劃重用現象
　　下麵是兩個參數化的SQL語句，語句的主體都是一樣的，只是帶入的參數不同，正常情況下，第二句代碼是可以重用第一句代碼執行之後緩存的執行計劃的。

　　至於怎麼開Profile就不說了，基礎問題。

SP:CacheInsert和SP:CacheHit

　　執行上述代碼，如下

DBCC FREEPROCCACHE
GO

EXEC SP_EXECUTESQL N'SELECT * FROM [dbo].[Test] WHERE Id = @id', N'@id INT',@id =9999
GO

EXEC SP_EXECUTESQL N'SELECT * FROM [dbo].[Test] WHERE Id = @id', N'@id BIGINT',@id =201
GO

　　得到的Profile中跟蹤的信息如下

　　簡單說明一下，執行第一句代碼之前，是清空了執行計劃緩存的，所以第一次執行的sql語句是沒有執行計劃緩存可用的。

　　參考截圖。

　　首先看第一次執行的情況：
　　　　上面說了，執行第一句代碼之前，是清空了執行計劃緩存的，所以第一次執行的sql語句是沒有執行計劃緩存可用的。
　　　　因此第一句SQL代碼是需要編譯的（不是重編譯），
　　　　參數化SQL編譯之後要緩存起來（不同參數可以重用執行計劃），表現就是有一個SP:CacheInsert。
　　　　SP:CacheInsert的意思是當前SQL編譯，生成了一個執行計劃，並且向緩存中插入一個執行計劃緩存。
　　然後看第二次執行的情況：
　　　　在第二句SQL執行的時候，因為第一句SQL已經編譯並且緩存了一個執行計劃，
　　　　因此當前SQL是不需要編譯，並且可以重用已有的執行計劃的。
　　　　這個就表現在有一個SP：CacheHit，
　　　　SP:CacheHit的意思就是，當前SQL的執行計劃在緩存的執行計劃中命中。

為什麼第一次和第二次都會出現一個“SP:CacheMiss”事件

　　為什麼第一次和第二次都會出現一個“SP:CacheMiss”事件？
　　上面不是說第二個SQL的執行已經重用了第一次的執行計划了嗎，為什麼還會出現“SP:CacheMiss”事件？
　　“SP:CacheMiss”事件到底代表什麼含義？
　　原因就是：
　　SQL Server在緩存執行計劃的時候，只緩存SQL語句本身，而不緩存執行的語句。
　　聽起來這麼彆扭，還是以實例來說明吧，參考下圖，緩存就是換成的語句本身，而不是整個執行的字元串信息
　　因此“SP:CacheMiss”代表的是：“EXEC SP_EXECUTESQL N'SELECT * FROM [dbo].[Test] WHERE Id = @id', N'@id INT',@id =8888”這個字元串。

 　 關於上述結論再舉一例：

　  如果上述實例不足以說明問題的話，再舉一個例子，這裡創建一個沒有任何參數和動態SQL的存儲過程（不會導致每次執行都重新編譯，執行計劃會重用），
　  正常情況下，該存儲過程第一次執行之後，執行計劃會被緩存起來，第二次執行的時候就可以重用第一次的執行計劃。

　  這裡情況緩存的執行計劃，連續執行兩次EXEC TestPlanCache，觀察Profile中的現象，是不是還是每次執行仍有有一個SP:CacheMiss事件？
　  為什麼，難道說還是因為沒有緩存可用嗎？
　  肯定不是，第一次執行仍舊是有一個SP:CacheInsert，第二次仍舊是SP:CacheHit，跟上述發生執行計劃重用的現象一致。
　  結論仍舊一樣，與上面所述類似，SQL Server不會緩存EXEC TestPlanCache這個文本本身，緩存的執行計劃是內部的SQL語句的執行計劃，
　  SP:CacheMiss是針對EXEC TestPlanCache這個命令來說的，SQL Server是不緩存調用存儲過程或者參數化SQL的命令本身的。

總結：

有人在觀察執行計劃緩存與重用的時候，尤其是存儲過程或者sp_executesql執行的參數化SQL語句，
會發現不停地出現SP:CacheMiss事件，就武斷地斷定為發生了編譯/重編譯，
編譯與重編譯是針對SQL語句或者存儲過程中的SQL來說的，而不是針對調用存儲過程或者參數化SQL的命令本身來說的。
SQL Server自身不會緩存調用存儲過程或者參數化SQL的命令本身，因此會經常發現SP:CacheMiss事件。
對於SQL語句的計劃緩存，如果是第一次編譯，會緩存起來，緩存的時候就會出現SP:CacheInsert，第二次或者以後重用這個計劃，就是出現SP:CacheHit事件。