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:
|
|
|
|
|
|