ANALYSIS:- In this case when we captured PSSDiag and noticed huge recompiles due to AutoStat updates, here’s a snippet of what we noticed in SQL Server 2005 Vs SQL Server 2008 on the same machine.
SQL 2008 ========= EventID Occurrences Description ----------- ----------- -------------------------------------------------- 58 724 Auto Update Stats 166 508 SQL:StmtRecompile
SQL 2005 ======= EventID Occurrences Description ----------- ----------- --------------------------------------------------58 29 Auto Update Stats
Further to this we enabled Trace Flag 205 which helped us in getting the information about recompiles and reasons for the same.
From Error Log we noticed that most of the recompile gets kicked in for a specific table (Say “MyTable”) 2011-07-13 15:30:36.67 spid74 Data related recompile (new stats available): Tbl Dbid: #, Objid: XXXXXXXXX, Indid: 3, current snapshot: 281200, compile time snapshot: 260400, threshold: 500 (small table) 2011-07-13 15:30:36.67 spid74 Recompile issued : Adhoc|Prepared LineNo:1 StmtNo: 1 Reason: 2 … … <<QUERY>> 2011-07-13 15:30:36.71 spid74 AUTOSTATS: UPDATED Stats: MyTable.._WA_Sys_00000046_1C5BBD31 Dbid = # Indid = 2 Rows: 200 Duration: 0ms … … 2011-07-13 15:30:36.71 spid74 AUTOSTATS: UPDATED Stats: MyTable.._WA_Sys_00000046_1C5BBD31 Dbid = # Indid = 3 Rows: 200 Duration: 0ms 2011-07-13 15:30:36.71 spid74 AUTOSTATS: Tbl: MyTable Objid:XXXXXXXXXX Rows: 200.000000 Threshold: 500 Duration: 2ms 2011-07-13 15:30:36.71 spid74 AUTOSTATS: UPDATED Stats: MyTable.._WA_Sys_0000002F_1C5BBD31 Dbid = # Indid = 4 Rows: 200 Duration: 0ms
Further to this we looked at the Perfstas for the database and looking at the data for that specific table MyTable we noticed that in the SQL Server 2005 all the indexes and column statistics have the NORECOMPUTE option set to YES so at some point UPDATE STATISTICS with the NORECOMPUTE clause must have been run again this table. Whereas when we looked at the same data for the table in the SQL Server 2008 we noticed that the indexes for the table have the NORECOMPUTE option enabled but for the column statistics the option is NOT enabled.
<<Snipp Below>>
SQL Server 2005 :-
-- STATS_DATE and rowmodctr for [DataBaseName].sysindexes --
objname type idxname indid norecompute ------- --------- ------------------------- ------ -----------
MyTable INDEX idx_xxx_xxx_xxx_xxx_xxx 2 *YES* MyTable INDEX idx_xxx_xxx_xxx_xxx 1 *YES* MyTable AUTOSTATS _WA_Sys_00000007_03C58583 3 *YES* MyTable AUTOSTATS _WA_Sys_00000008_03C58583 4 *YES* MyTable AUTOSTATS _WA_Sys_0000000A_03C58583 5 *YES* MyTable AUTOSTATS _WA_Sys_0000000B_03C58583 6 *YES* MyTable AUTOSTATS _WA_Sys_0000000C_03C58583 7 *YES* MyTable AUTOSTATS _WA_Sys_00000018_03C58583 8 *YES* MyTable AUTOSTATS _WA_Sys_0000002F_03C58583 9 *YES* MyTable AUTOSTATS _WA_Sys_00000046_03C58583 10 *YES*
SQL Server 2008:-
-- STATS_DATE and rowmodctr for [Sentinel_RuleServer_Perf].sysindexes --
objname type idxname indid norecompute -------- -------- - ------------------------- ------ ----------- MyTable INDEX idx_xxx_xxx_xxx_xxx_xxx 2 *YES* MyTable INDEX idx_xxx_xxx_xxx_xxx 1 *YES* MyTable AUTOSTATS _WA_Sys_00000046_1C5BBD31 3 no MyTable AUTOSTATS _WA_Sys_0000002F_1C5BBD31 4 no MyTable AUTOSTATS _WA_Sys_00000018_1C5BBD31 5 no MyTable AUTOSTATS _WA_Sys_0000000C_1C5BBD31 6 no MyTable AUTOSTATS _WA_Sys_0000000B_1C5BBD31 7 no MyTable AUTOSTATS _WA_Sys_0000000A_1C5BBD31 8 no MyTable AUTOSTATS _WA_Sys_00000008_1C5BBD31 9 no MyTable AUTOSTATS _WA_Sys_00000007_1C5BBD31 10 no <<Snipp Below>>
The below script will clarify even better the difference in SQL Server 2005 Vs SQL Server 2008 , Watch out!!!
USE MASTER GO DROP DATABASE stattest CREATE DATABASE stattest GO USE stattest GO EXEC Sp_dboption 'stattest', 'auto update statistics', 'false' -- auto stats property off to avoid side-effect EXEC Sp_dboption 'stattest', 'auto create statistics', 'false' GO CREATE PROCEDURE Statproc AS CREATE TABLE #t1 ( c1 INT, c2 INT ) CREATE INDEX ix_c1 ON #t1 (c1) UPDATE STATISTICS #t1 WITH norecompute -- NORECOMPUTE ON SELECT name, auto_created, no_recompute FROM tempdb.sys.stats WHERE object_id = (SELECT TOP 1 object_id FROM tempdb.sys.objects WHERE name LIKE '#t1%')
SELECT * FROM #t1 WHERE c2 = 1 – This is where the Auto stats gets created SELECT name, auto_created, no_recompute FROM tempdb.sys.stats WHERE object_id = (SELECT TOP 1 object_id FROM tempdb.sys.objects WHERE name LIKE '#t1%') GO SET nocount ON DBCC freeproccache EXEC Statproc GO
Executing this in SQL Server 2005 produces the following results:-
Executing this in SQL Server 2008 produces the following results:-
Notice the difference!!!! Yes this can make a huge difference …
SO WHAT DO WE DO NOW (SOLUTION/WORKAROUND):-
1. Before executing the Query / Batch run the following: Update stats MyTable with norecompute 2. Turn off “Auto Update Statistics for the database in Question.