>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
    CREATE TABLE #t ( a INT )
    SELECT  *
    FROM    #t
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.

Posted on December 29, 2010, in Performance Tuning, SQL Profiler. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: