October 22, 2011 at 10:30 am
We have a SQL Server 2008 Ent x64 instance on which we host a SAP database with millions of tables. For couple of tables we decided to disable "Auto Update Statistics", so as to avoid the extra work load during production hours. Just to make sure we have the latest statistics we setup a Full DB update statistics task, via the maintenance plan, that runs once every week.
I noticed that the "Auto Update Statistics" setting, for the few tables that we disabled, is being being turned ON after the Full DB updated statistics task is run. Is this how it should be? I mean, why would the update statistics task enable the "Auto Update Statistics" setting?
Any comments?
October 22, 2011 at 10:36 am
Firstly, a weekly update is likely far from adequate to keep stats updated and query plans optimal, unless those tables only have a few rows changing per day.
As for the auto update, the maint plan runs UPDATE STATISTICS with the default options. The default for NORECOMPUTE is off. From Books Online:
NORECOMPUTE
Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS, for the specified statistics. If this option is specified, the query optimizer completes this statistics update and disables future updates.
To re-enable the AUTO_UPDATE_STATISTICS option behavior, run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats.
The bolded section is exactly what the maintenance plan does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply