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.

CREATE EVENT SESSION [QueryAndRecompile]
ON SERVER
    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')
WITH
(
    TRACK_CAUSALITY = ON
);
GO

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 dxmv.name = '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.

Conclusion

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.