Monday, October 8, 2007

How many times stored procedures have recompiled?

I have recently visited our client and we found out that He has a very high number of stored procedures that have recompiled. Certainly, we have observed some perfomance decreasing. Fortunately , the client has alredy upgraded to SQL Server 2005(SP2) and apart from running SQL Server Profile to indentify those stored procedures we came out with the following query.

It gives you the top 10 stored procedures that have been recompiled.

select *
from sys.dm_exec_query_optimizer_info

select top 10
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
plan_generation_num >1
order by plan_generation_num desc

Note that the plan_generation_num indicates the number of times the stored procedure has recompiled. Very cool feature.

No comments: