If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP:Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.
SELECT dxmv.name,
dxmv.map_key,
dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = N'statement_recompile_cause'
ORDER BY dxmv.map_key;
When I ran this query, I got following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
map_key map_value 1 Schema changed 2 Statistics changed 3 Deferred compile 4 Set option change 5 Temp table changed 6 Remote rowset changed 7 For browse permissions changed 8 Query notification environment changed 9 PartitionView changed 10 Cursor options changed 11 Option (recompile) requested 12 Parameterized plan flushed 13 Test plan linearization 14 Plan affecting database version changed |