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.

No comments:

Post a Comment