Sql Server Performance: Which Statement Triggered the Recompile Event

In last post “Performance Counter to Count Stored Procedure Re-compilations” we have discussed about a useful counter of system performance counters i.e. SQL Re-Compilations/sec. In this post, we have discussed that if nonzero values are consistently occurring for this counter, we should seriously search for the culprit stored procedures and then statement with in that stored procedure.

Let me explain, how Sql Profiler can help us to find statement in a stored procedure which actually triggered the recompile event.

Open Sql Server Profiler and start a new trace but with following events

·         SP:Starting

·         SP:StmtStarting

·         SP:Recompile

·         SP:Completed

You can select these events by selecting check box “Show all events” (as mentioned in screen shot. Click “Run” button to start trace.

Meanwhile execute following query as an example

CREATE PROCEDURE proc_testRecompilation

AS

    CREATE TABLE #t ( a INT )

    SELECT  *

    FROM    #t

GO

EXEC proc_testRecompilation

Now shift your focus to Sql Profiler to examine trace, which must resembling following screen shot.

Watch closely the statement which appeared before and after the SP:Recompile. This is the statement in stored procedure which actually triggered recompilation. Once we have detected the culprit statement, we must find out the reason behind this happening. In our example statement triggered recompilation because of "Recompilations Due to Interleaving Data Definition Language (DDL) and Data Manipulation Language (DML) Operations". What are other reasons behind recompilation occurring read previous post.

Note: In our example you will only see the recompile event on the first execution of the procedure, or if you drop and re-create the procedure each time you execute the script.