How to Determine if a Stored Procedure is Being Recompiled on Every Execution in SQL Server 2012

Question

What query can I use to determine if a stored procedure is being recompiled every time it is executed in SQL Server 2012? I suspect that some of our stored procedures might be getting recompiled, but I'm not sure how to check. Thanks!

Answer 1

xe

Probably the only reasonable way to do this is with Extended Events. You can use this definition:

CREATE EVENT SESSION 
    [sql_statement_recompile] 
ON 
    SERVER 
ADD 
    EVENT sqlserver.sql_statement_recompile
    (
        WHERE [object_id] = 138 /*Replace with your stored procedure object id*/
        AND   [sqlserver].[database_id] = 999 /*Replace with your database id*/
    )
ADD 
    TARGET package0.event_file
    (
        SET filename = N'sql_statement_recompile'
    );
ALTER EVENT SESSION 
    [sql_statement_recompile]
ON SERVER
   STATE = START;

The database and object id filters are optional, of course, but can help you target things more precisely.

Since you're on SQL Server 2012, you may need to use the GUI in SSMS to search for the sql_statement_recompile event and see what filters and additional output is available that may be of use to you.