有個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, o
有個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 >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? > 2
然后準備好用這個腳本來返回plan_generation_num的值
select top 25 sql_text.text, sql_handle, plan_generation_num,? execution_count,
??? dbid,? objectid
from sys.dm_exec_query_stats a
??? cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where? sql_text.text like ‘%aaa%’
order by plan_generation_num desc
Exec aaa之后的腳本返回結果：
這里的第六行結果集就是我們的存儲過程aaa。這時的plan_generation_num值顯示為1.
接下來我們mark recompile：
sp_recompile aaa
然后再次執行 exec aaa
使用腳本查詢：
這里看到存儲過程重編譯以后，plan_generation_num的值并沒有增加。
那為什么我們還會使用這樣的腳本來返回重編譯次數很多的存儲過程呢？
接下來我們再次將存儲過程mark recompile，然后直接使用腳本查詢：
這時，我們發現該存儲過程的plan 和text已經從DMV中移除了。看起來sp_recompile會直接將cache中緩存的執行計劃和語句直接標識成不可用。因此DMV中就沒有相關的記錄了。
這就是說，存儲過程標識重編譯這種模式導致的重編譯，從DMV里面是沒有辦法跟蹤的。
那么從性能監視器的計數器 “sp recompilation/sec”里面能不能跟蹤到呢？
我們反復執行：
sp_recompile aaa
exec aaa
性能監視器中一直顯示為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 > 1. That is because all subplans start with 1 as their plan_generation_num. Appendix A is the query for learning plan_generation_num.
這部分說明簡單的來說，就是只要存儲過程中有一條語句發生重編譯，這個plan_generation_num值就會+1.這里并沒有說是整個存儲過程重編譯的時候，這個值會+1.
