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

轉載自此處

有個 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.

?

http://lfsean.blogspot.com/2008/02/understanding-sql-plangenerationnum.html

?

這部分說明簡單的來說，就是只要存儲過程中有一條語句發生重編譯，這個 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

insert into aaa_table select * from DMV_Top25_Recompile_Commands where 1=2

insert into aaa_table select * from DMV_Top25_Recompile_Commands where 1=2

insert into aaa_table select * from DMV_Top25_Recompile_Commands where 1=2

insert into aaa_table select * from DMV_Top25_Recompile_Commands where 1=2

?

?

然后我們執行存儲過程，收集 profiler trace ，同時繼續監控性能監視器

開始重新執行存儲過程 aaa

?

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

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

?

?

腳本的查詢結果：

?

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

?

為什么這樣寫存儲過程會導致重編譯？ http://support.microsoft.com/kb/243586 ? 這篇文章中列舉了多種會導致存儲過程重編譯的情況：

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

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

?

因此我們的結論是，使用這個腳本去查詢重編譯次數高的存儲過程是沒有錯的，但是這個腳本并不包含由于 sp_recompile 已經定義存儲過程時使用了 with recompile 的選項而導致的存儲過程重編譯的情況。

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