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.

Check if a Temporary table exists

To check if a temporary table exists,The usual method of querying sys.objects table on tempdb will not store #tables in the same naming convention as we create, So one of the best way to check if a #table exists is as below,

IF object_id('tempdb..#MyTempTable') IS NOT NULL

BEGIN

<TSQL Batch>

END

Find last statistics update date

Find below 2 ways to find the last statistics update on a SQL table,

1. Using STATS_DATE Function,

SELECT name AS index_name,

STATS_DATE(object_id, index_id) AS statistics_update_date

FROM sys.indexes

WHERE object_id = OBJECT_ID('tblname');

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

2. Use SHOW_STATISTICS consistency checker.

 DBCC SHOW_STATISTICS ('tblname', indexname);

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