How to track the number of times the stored procedure is compiled in SQLServer

There is a script that we are very familiar with. It is used to find which stored procedures in SQL Server have the most recompilation times: -- Example. selecttop25sql_text.text, SQL _handle, plan_generation_num, execution_count, dbid, o

There is a script that we are very familiar with. It is used to find which stored procedures are recompiled the most frequently: -- 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

There is a script that we are very familiar with. It is used to find which stored procedures are the most frequently recompiled in 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

Cross apply sys. dm_exec_ SQL _text (SQL _handle) as SQL _text

Where plan_generation_num> 1

Order by plan_generation_num desc

Go

So, under what circumstances is the Stored Procedure recomile recorded by this script?

Create a store procedure on SQL Server:

Create proc aaa

As

Select plan_generation_num, * FROM DMV_Top25_Recompile_Commands where plan_generation_num> 2

Then, use this script to return the plan_generation_num value.

Select top 25 SQL _text.text, SQL _handle, plan_generation_num, execution_count,

Dbid, objectid

From sys. dm_exec_query_stats

Cross apply sys. dm_exec_ SQL _text (SQL _handle) as SQL _text

Where SQL _text.text like '% aaa %'

Order by plan_generation_num desc

Result returned by the script after Exec aaa:

The sixth row of result set is our stored procedure aaa. The plan_generation_num value is 1.

Next we will mark recompile:

Sp_recompile aaa

Execute exec aaa again

Use script query:

Here we can see that the plan_generation_num value does not increase after the stored procedure is re-compiled.

So why do we still use this script to return a stored procedure with a large number of recompilation times?

Next we will re-store the Stored Procedure mark recompile, and then directly use the script to query:

At this time, we found that the plan and text of the stored procedure have been removed from DMV. It seems that sp_recompile will directly mark the cached execution plan and statements in the cache as unavailable. Therefore, no related records exist in DMV.

This means that there is no way to track the re-compilation caused by the re-compilation mode of the stored procedure identity from DMV.

So can we track the performance monitor counter "sp recompilation/sec?

We repeatedly execute:

Sp_recompile aaa

Exec aaa

Performance Monitor always displays 0

So what is the meaning of the plan_generation_num value? The description in the "BOL" is simple:

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

The Chinese version indicates the serial number that can be used to differentiate instances of different plans after re-compilation.

The serial number to be calculated is not described here. We found a more detailed description from another English 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 inpidual 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.

This part explains simply that as long as a statement in the stored procedure is recompiled, The plan_generation_num value will be + 1. this does not mean that this value will be + 1 during the re-Compilation of the entire stored procedure.