原文: 如何track存儲(chǔ)過(guò)程的編譯次數(shù)

轉(zhuǎn)載自此處

有個(gè) script 我們很熟悉，是用來(lái)去查找當(dāng)前 SQL Server 中哪些存儲(chǔ)過(guò)程變重編譯的次數(shù)最多的：

?

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

?

那么，這個(gè)腳本究竟是記錄什么情況下的存儲(chǔ)過(guò)程 recomile 呢？

?

我們?cè)? SQL Server 上創(chuàng)建一個(gè)這樣的 store procedure ：

?

create proc aaa

as

select plan_generation_num,* FROM DMV_Top25_Recompile_Commands where plan_generation_num? > 2

?

然后準(zhǔn)備好用這個(gè)腳本來(lái)返回 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 之后的腳本返回結(jié)果：

?

這里的第六行結(jié)果集就是我們的存儲(chǔ)過(guò)程 aaa 。這時(shí)的 plan_generation_num 值顯示為 1.

?

接下來(lái)我們 mark recompile ：

sp_recompile aaa

然后再次執(zhí)行 ? exec aaa

?

使用腳本查詢(xún)：

?

?

這里看到存儲(chǔ)過(guò)程重編譯以后， plan_generation_num 的值并沒(méi)有增加。

那為什么我們還會(huì)使用這樣的腳本來(lái)返回重編譯次數(shù)很多的存儲(chǔ)過(guò)程呢？

?

接下來(lái)我們?cè)俅螌⒋鎯?chǔ)過(guò)程 mark? recompile ，然后直接使用腳本查詢(xún)：

這時(shí)，我們發(fā)現(xiàn)該存儲(chǔ)過(guò)程的 plan? 和 text 已經(jīng)從 DMV 中移除了。看起來(lái) sp_recompile 會(huì)直接將 cache 中緩存的執(zhí)行計(jì)劃和語(yǔ)句直接標(biāo)識(shí)成不可用。因此 DMV 中就沒(méi)有相關(guān)的記錄了。

這就是說(shuō)，存儲(chǔ)過(guò)程標(biāo)識(shí)重編譯這種模式導(dǎo)致的重編譯，從 DMV 里面是沒(méi)有辦法跟蹤的。

?

那么從性能監(jiān)視器的計(jì)數(shù)器 ? “ sp recompilation/sec ”里面能不能跟蹤到呢？

我們反復(fù)執(zhí)行：

sp_recompile aaa

exec aaa

?

性能監(jiān)視器中一直顯示為 0

?

那么 plan_generation_num 的值究竟是什么含義呢？ BOL 中的解釋很簡(jiǎn)單：

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

中文版的含義為：可用于在重新編譯后區(qū)分不同計(jì)劃實(shí)例的序列號(hào)。

?

這里并沒(méi)有說(shuō)明如何去計(jì)算的序列號(hào)。我們從另一篇英文的 blog 中找到了更加詳細(xì)的說(shuō)明：

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

?

這部分說(shuō)明簡(jiǎn)單的來(lái)說(shuō)，就是只要存儲(chǔ)過(guò)程中有一條語(yǔ)句發(fā)生重編譯，這個(gè) plan_generation_num 值就會(huì) +1. 這里并沒(méi)有說(shuō)是整個(gè)存儲(chǔ)過(guò)程重編譯的時(shí)候，這個(gè)值會(huì) +1.

?

接下來(lái)我們修改測(cè)試存儲(chǔ)過(guò)程 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

?

?

然后我們執(zhí)行存儲(chǔ)過(guò)程，收集 profiler trace ，同時(shí)繼續(xù)監(jiān)控性能監(jiān)視器

開(kāi)始重新執(zhí)行存儲(chǔ)過(guò)程 aaa

?

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

Profiler trace 中可以看到每條 insert 語(yǔ)句上都觸發(fā)了一個(gè) sp:recompile

?

?

腳本的查詢(xún)結(jié)果：

?

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

?

為什么這樣寫(xiě)存儲(chǔ)過(guò)程會(huì)導(dǎo)致重編譯？ http://support.microsoft.com/kb/243586 ? 這篇文章中列舉了多種會(huì)導(dǎo)致存儲(chǔ)過(guò)程重編譯的情況：

aaa 這個(gè)存儲(chǔ)過(guò)程符合這個(gè)條件：

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

?

因此我們的結(jié)論是，使用這個(gè)腳本去查詢(xún)重編譯次數(shù)高的存儲(chǔ)過(guò)程是沒(méi)有錯(cuò)的，但是這個(gè)腳本并不包含由于 sp_recompile 已經(jīng)定義存儲(chǔ)過(guò)程時(shí)使用了 with recompile 的選項(xiàng)而導(dǎo)致的存儲(chǔ)過(guò)程重編譯的情況。

如何track存儲(chǔ)過(guò)程的編譯次數(shù)