Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Using Profiler to view SQL:StmtRecompiles and the EventSubClass to see the reason for a recompile (w/ Stored Procedure using Dynamic SQL)
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-01-23









Query which can identify various possible reasons, stored in profiler related catalog views, for a recompile.


SELECT sv.subclass_name, sv.subclass_value
FROM
sys.trace_events AS e
inner join sys.trace_subclass_values AS sv on e.trace_event_id = sv.trace_event_id
WhERE
e.name = 'SP:Recompile'
AND
sv.subclass_value < 1000
ORDER BY
sv.subclass_value;






SQL Profiler: