Sometime I feel I know a lot about SQL Server and very next moment, I realize that honestly I do not know much about this product. Earlier today, I had similar moments. I was playing with few DMVs and suddenly I ended up on the DMVÂ sys.dm_xe_map_values. There are only four columns and one of the columns is a GUID.
The reason I ended up on this DMV was because I was asked a question what are the different reasons any statement can be recompiled. I knew few of the reasons why would any statement recompile but I was not aware of all the reasons for any statement recompilation. After doing some search on the internet and my older archived I figured out the reasons for Statement Recompilation using sails. dm_xe_map_values.
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.
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
It will be interesting to build a test case for each of the compilation reason, as it will give a great learning experience.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Awesome, I haven’t had much time to play sith the DMVs yet and this very topic came up a few days ago. Thanks Pinal
Pinal
I assume this is written on SQL 2012 because I get 16 reasons on SQL 2014. QDS plan forcing policy changed & QDS plan forcing failed are the two new ones.
Hi Pinal,
Thanks for sharing this information, kindly confirm have you build a test case for each of the compilation reason. I would like to know further details on this, please share the same. Limited information is available on this. Thanks in advance!
No. I didn’t find time for it. If you have, please share.