NORECOMPUTE : Can lead to SQL server performance degradation!!
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 .