Tuesday, 19 February 2013

Find the reason for recompilation using SQL Server Profiler

Often as a DBA you encounter performance issues on SQL due to frequent recompilation of the SQL Statement or a Store procedure, We can detect frequent SQL Recompilation by,

1.       SQL Profiler SP:Recompile and SQL:StmtRecomple Event classes
2.       Perfmon SQL Counter SQL:Statistics(Re-Compilations/sec)
3.       DMV sys.dm_exec_query_stats; plan_generation_num Column.

As the scope of our article is not detecting SQL Recompilation but find the reason for recompilation I am not going to detail on the above more.

The reason for recompile can be detected using SQL Server Profiler in EventSubClass Column corresponding to SP:Recompile and SQL:StmtRecomple Event classes. Before We go into an example
I am listing Type of event subclass. Indicates the reason for recompilation.

1 = Schema Changed
2 = Statistics Changed
3 = Recompile DNR
4 = Set Option Changed
5 = Temp Table Changed
6 = Remote Rowset Changed
7 = For Browse Perms Changed
8 = Query Notification Environment Changed
9 = MPI View Changed
10 = Cursor Options Changed
11 = With Recompile Option

Let’s see an Example, I am using tempdb and a #table for this example, I have Started SQL Server profiler and have added Event Class, TextData, SPID and Event Sub Class as my columns and have added
SP:Recompile, RPC:Completed,SQL:StmtRecompile and SQL:BatchCompled eventsto the trace.

Create Table #Temptbl (col1 int)
INSERT INTO #Temptbl Values(1)
select * from #Temptbl
select * from #Temptbl

Note:- The event subclass shows Deferred compile because the object #Temptbl is not available during compilation.

ADD Rnum int IDENTITY (1,1)
select * from #Temptbl
declare @i int;
set @i = 0;
while (@i < 1000)
insert into #Temptbl values(@i);
set @i = @i + 1;
select * from #Temptbl

select * from #Temptbl

DELETE FROM #Temptbl where Rnum > 100

select * from #Temptbl where Rnum > 25300 and Rnum < 35300 OPTION(RECOMPILE)

But the most common way an SP gets recompiles is for EventSubClass:Statistics Change which I have not dealt in the above example. I will be writing a separate article on Temporary table and permanent table Cardinality and Recompilation where I will explain in detail. This article is intended to show how to find why SP or SQL batch are recompiled.


  1. Good job. thanks for your sharing. hermes birkin bag

  2. One more EventSubClass to be aware of:
    12 - Parameterized Plan Flushed.

    Doesn't show up in the documentation, but just saw it during a trace.