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

Ref:- http://msdn.microsoft.com/en-us/library/ms187105.aspx

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.

DROP TABLE #Temptbl

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.

ALTER TABLE #Temptbl

ADD Rnum int IDENTITY (1,1)

select * from #Temptbl

declare @i int;

set @i = 0;

while (@i < 1000)

begin

insert into #Temptbl values(@i);

set @i = @i + 1;

end

select * from #Temptbl

CREATE CLUSTERED INDEX idx1 ON #Temptbl(Rnum)

ALTER INDEX idx1 ON #Temptbl REBUILD

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.