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.



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.

2 comments:

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

    ReplyDelete
  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.

    ReplyDelete