SQL: Checking the Plan Recompile Number
Author: jason
Date: 2014-06-25 11:18:06
Category: Musing

Another thing I'm getting alerts on (got a monitoring tool? they're great by the way) is Plan Recompiles. So what plans are recompiling?

SQL Authority has the answer: http://blog.sqlauthority.com/2010/02/18/sql-server-plan-recompilation-and-reduce-recompilation-performance-tuning/

Code

SELECT TOP 10
qs.plan_generation_num,
qs.execution_count,
DB_NAME(st.dbid) AS DbName,
st.objectid,
st.TEXT
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009