如何track存儲過程的編譯次數

<a href="http://blogs.msdn.com/b/apgcdsd/archive/2012/04/16/track.aspx">轉載自此處</a>

有個script我們很熟悉，是用來去查找目前SQL

Server中哪些存儲過程變重編譯的次數最多的：

--Gives you the top 25 stored procedures that have been recompiled.

select top 25 sql_text.text, sql_handle, plan_generation_num,  execution_count,

    dbid,  objectid

into DMV_Top25_Recompile_Commands

from sys.dm_exec_query_stats a

    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where plan_generation_num &gt;1

order by plan_generation_num desc

go

那麼，這個腳本究竟是記錄什麼情況下的存儲過程recomile呢？

我們在SQL Server上建立一個這樣的store

procedure：

create proc aaa

as

select plan_generation_num,* FROM DMV_Top25_Recompile_Commands where plan_generation_num  &gt; 2

然後準備好用這個腳本來傳回plan_generation_num的值

where  sql_text.text like '%aaa%'

Exec aaa之後的腳本傳回結果：

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/3750.image001.png"></a>

這裡的第六行結果集就是我們的存儲過程aaa。這時的plan_generation_num值顯示為1.

接下來我們mark recompile：

sp_recompile aaa

然後再次執行 exec aaa

使用腳本查詢：

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/0211.image002.png"></a>

這裡看到存儲過程重編譯以後，plan_generation_num的值并沒有增加。

那為什麼我們還會使用這樣的腳本來傳回重編譯次數很多的存儲過程呢？

接下來我們再次将存儲過程mark recompile，然後直接使用腳本查詢：

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/5187.image003.png"></a>

這時，我們發現該存儲過程的plan 和text已經從DMV中移除了。看起來sp_recompile會直接将cache中緩存的執行計劃和語句直接辨別成不可用。是以DMV中就沒有相關的記錄了。

這就是說，存儲過程辨別重編譯這種模式導緻的重編譯，從DMV裡面是沒有辦法跟蹤的。

那麼從性能螢幕的計數器 “sp

recompilation/sec”裡面能不能跟蹤到呢？

我們反複執行：

exec aaa

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/2867.image004.png"></a>

性能螢幕中一直顯示為0

那麼plan_generation_num的值究竟是什麼含義呢？BOL中的解釋很簡單：

A sequence number that can be used to distinguish between instances of plans after a recompile.

中文版的含義為：可用于在重新編譯後區分不同計劃執行個體的序列号。

這裡并沒有說明如何去計算的序列号。我們從另一篇英文的blog中找到了更加詳細的說明：

There are a lot of interesting columns in P and S, especially in S, and here I will only discuss what I have learned about plan_generation_num in S. SQL Server 2005 treats the compiled plan for a stored

procedure as an array of subplans, one for each query statement. If an individual subplan needs recompilation, it does so without causing the whole plan to recompile. In doing so, SQL Server increments the plan_generation_num on the subplan record to be 1

+ MAX(plan_generation_num for all subplans). The general distribution of plan_generation_num among all subplans for a given plan is such that it has multiple of 1's and distinct numbers &gt; 1. That is because all subplans start with 1 as their plan_generation_num.

Appendix A is the query for learning plan_generation_num.

<a href="http://lfsean.blogspot.com/2008/02/understanding-sql-plangenerationnum.html">http://lfsean.blogspot.com/2008/02/understanding-sql-plangenerationnum.html</a>

這部分說明簡單的來說，就是隻要存儲過程中有一條語句發生重編譯，這個plan_generation_num值就會+1.這裡并沒有說是整個存儲過程重編譯的時候，這個值會+1.

接下來我們修改測試存儲過程aaa：

Alter TABLE aaa_table(

[text] [nvarchar](max) NULL,

[sql_handle] [varbinary](64) NOT NULL,

[plan_generation_num] [bigint] NOT NULL,

[execution_count] [bigint] NOT NULL,

[dbid] [smallint] NULL,

[objectid] [int] NULL

) ON [PRIMARY]

insert into aaa_table select * from DMV_Top25_Recompile_Commands where 1=2

然後我們執行存儲過程，收集profiler trace，同時繼續監控性能螢幕

開始重新執行存儲過程aaa

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/0702.image005.jpg"></a>

這裡我們可以看到sp recompilation/sec立刻變成了7。

Profiler trace中可以看到每條insert語句上都觸發了一個sp:recompile

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/2476.image006.png"></a>

腳本的查詢結果：

<a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-42-89/8750.image007.png"></a>

可以看到plan_generation_num的值增加到6了。

aaa這個存儲過程符合這個條件：

The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.

是以我們的結論是，使用這個腳本去查詢重編譯次數高的存儲過程是沒有錯的，但是這個腳本并不包含由于sp_recompile已經定義存儲過程時使用了with

recompile的選項而導緻的存儲過程重編譯的情況。