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.

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

Tuesday 12 February 2013

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');



2. Use SHOW_STATISTICS consistency checker.

 DBCC SHOW_STATISTICS ('tblname', indexname);