SQL SERVER – sys.dm_xe_map_values – Reasons for Statement Recompilation

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)

Previous Post
SQL SERVER – Weekly Series – Memory Lane – #027
Next Post
SQL SERVER – Azure SQL Databases Backup Made Easy with SQLBackupAndFTP

Related Posts

4 Comments. Leave new

  • Forrest Pugh
    May 5, 2013 9:36 pm

    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

    Reply
  • 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.

    Reply
  • 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!

    Reply

Leave a Reply

Menu