SQL Server 2005 provides two important dynamic views sys.dm_exec_cached_plans (P) and sys.dm_exec_query_stats (S) to help understand cached plans and their statistics. Joining on [plan_handle], P and S have a 1-to-0 relationship. P has a record for every compiled plan in the plan cache, and S can have a record for every query statement in the corresponding cached plan. S can be empty for a cached plan of cache object types "Parse Tree" or "CLR Compiled Proc" or for objects such as system stored procedures, ad hoc queries, prepared statements, etc. Since I am mainly interested in user stored procedures and their compilation/recompilation frequency, for our purposes, P and S have a 1-to-many relationship - a cached plan for a stored procedure in P has one or more corresponding recoreds in S, one for each query statement in the stored procedure.
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.
Based on the above discussion, the higher the maximum(plan_generation_num) for a cached plan, the more frequent the plan has been recompiled. Be aware that it is not the recompilation of the whole plan but rather of the individual subplans that is building up maximum(plan_generation_num).
Appendix A.
Following is a query to list all plan_generation_num for a stored procedure
---- Analze recompilation via.plan_generation_num
DECLARE @vcName VARCHAR(50);
SELECT @vcName = 'qp_Temp';
SELECT
P.plan_handle,
S.statement_start_offset, S.statement_end_offset,
S.plan_generation_num,
S.execution_count,
P.usecounts,
S.creation_time,
S.last_execution_time,
sqlSmt = SUBSTRING(T2.text, (S.statement_start_offset/2) + 1, ((CASE WHEN S.statement_end_offset > 0 THEN S.statement_end_offset ELSE DATALENGTH(T2.text) END - S.statement_start_offset)/2) + 1),
L.query_plan,
TL.query_plan,
obj = OBJECT_NAME(T.objectid),
T.objectid,
P.cacheobjtype,
P.objtype,
T.text
FROM sys.dm_exec_cached_plans P
INNER JOIN sys.dm_exec_query_stats S
ON P.plan_handle = S.plan_handle
OUTER APPLY sys.dm_exec_sql_text(P.plan_handle) T
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) L
OUTER APPLY sys.dm_exec_sql_text(S.sql_handle) T2
CROSS APPLY sys.dm_exec_text_query_plan (P.plan_handle, DEFAULT, DEFAULT) TL
WHERE P.cacheobjtype = 'Compiled Plan' AND
T.text NOT LIKE '%dm_exec_cached_plans%' AND
P.objtype = 'Proc' AND T.dbid = DB_ID()
AND OBJECT_NAME(T.objectid) = 'qp_Temp'
ORDER BY P.plan_handle, S.statement_start_offset, S.statement_end_offset
