Identifying Recompile Causes

Strictly speaking, a recompile isn’t really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here’s one way to get it done.

Extended Events for Recompile Causes

You knew I was going there.

Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). So, capturing the recompiles and getting grotty on the details means capturing statements. Understand that this can be expensive, especially in terms of storage. So, if you use the script I’m going to show, please, put some good filtering in place.

    ADD EVENT sqlserver.rpc_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.rpc_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sp_statement_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sp_statement_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_batch_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_batch_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_statement_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_statement_recompile
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_statement_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks'))
    ADD TARGET package0.event_file
    (SET FILENAME = N'QueryAndRecompile')

What I’m doing here is blanketing things. I have the start and stop of both RPC and Batch. Then, I have the start and stop of SP & SQL statements. Finally, I’ve added sql_statement_recompile. Now, that’s a key point. Even though you can see sp_statement_starting or sql_statement_starting and you think, ah, I need both sp_statement_recompile and sql_statement_recompile, in fact, the one covers both batch statements and prepared statements. There isn’t a sp_statement_recompile event.

I also have causality tracking enabled so that I can easily group a set of events on a busy system.

Also, I’m only filtering for a given database. It’s probably a VERY good idea to add some more filtering, as I said earlier.

This method could be overkill. You could easily just capture sql_statement_recompile and then add an Action to it to get the statement being recompiled. However, then, correlating it to a particular procedure or batch becomes a pain. Further, correlating it to an individual call to a given procedure or batch is an even bigger pain. It’s down to what you need.

One other note. You can get a complete list of all possible recompile causes by looking into the Extended Events system views:

SELECT dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE = 'statement_recompile_cause';

It’s worth looking at this list. It’s grown and expanded over the years. It looks VERY different now than it did for 2012. I suspect it’s going to be even more different when 2022 gets released.


No real reason to post this information other than I’m going through the recompile chapter in the book. I took a look at the blog and realized I hadn’t shared a lot of information on tracking recompiles. For many of us, we’ll never need this. For some of us, having the ability to track down specific causes for a given recompile could be a life-saver. I hope this helps.

4 thoughts on “Identifying Recompile Causes

  • Shawn E Hamzee

    Thanks Grant, really informative. what if you are not sure to recompile but know that the performance of the proc has degraded and so you’d want to know if it’s a good option to recompile it?

    • The only way to really answer that question is with the Mk I Eyeball. Look at the values & row estimates that the optimizer used in the existing plan. Compare that to the existing data & statistics. That’s your best bet. No real automated way to answer that particular question.

  • Mark D Powell

    My question is, how do you look at the captured results? Wading through the output via the Event Viewer does not seem practical on a busy system so do you have an XML type query that you use?

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.