info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Using Profiler to view SQL:StmtRecompiles and the EventSubClass to see the reason for a recompile (w/ Stored Procedure using Dynamic SQL)
by BF (Principal Consultant; Architecture; Engineering)
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: