Statistics and Recompilations, Part II

I recently blogged about an interesting behavior in SQL Server when the database option, Auto Update Statistics, is disabled…and that post requires a sequel.  The summary is that when you have the Auto Update Statistics option disabled for a database, query plans for tables are not recompiled when their statistics are updated.  There is an exception for the inserted and deleted tables that are created by DML INSTEAD OF triggers, but overall this database setting can have a significant impact on a system, which I alluded to in my previous post.  In a customer system I found a query that was doing a full table scan and required 32,000 reads, even though the predicate was unique and statistics had been updated that day with 100% sample.  The SQL Server instance had not been restarted since December (about 45 days before my discovery) and the customer had gone live in December.  My theory was that the full scan query plan was created by the optimizer in December, when there was no data in the system.  The query was executed regularly and never dropped out of the plan cache, therefore it was still being used in January even though by that point there were millions of rows in the table.  To test this, I found the query handle for the plan, and then dropped just that plan from cache.  Immediately I searched for a new query plan for that query, and found the index seek I expected.  The 32,000 read query no longer appeared in the trace I was running.

Now I’m faced with the challenge of figuring out what to recommend to our customers going forward.  For many years we have recommended that customers have Auto Update Statistics disabled, and I listed the reasons previously:

• We do not want statistics updating during production hours if at all possible; we want the customer to manage statistics through a regularly scheduled job.
• We want statistics updated with 100% sample because very often the distribution in critical tables is skewed.  With Auto Update Statistics enabled, the statistics do not update with 100% sample unless the table is less than 8 MB in size.

We cannot continue to make the same recommendation unless we provide more information about the ramifications (notably query plans are not recompiled by an update of statistics); it would be irresponsible of us to not mention this.  Alternatively, we could change our viewpoint entirely and recommend that customers enable Auto Update Statistics.

After a lot of thought, the recommendation is going to go away completely.  That is, we’re going to tell customers that they can either enable or disable the option, it’s their choice and it depends on how they want to manage statistics.  I feel that we need to provide an explanation to customers about why our recommendation has changed, and I also I believe we need to provide a few suggestions on how to manage statistics.  The challenge here is that so many of our customers do not have a full time DBA.  Very often, the application administrator acts as the DBA.  The application administrator will probably not have the time, nor the inclination, to manage statistics at the detailed level to which a full time Production DBA would.  Is there a set of maintenance and configuration options that we can suggest that will work for the Accidental DBA?

Here are the options I have developed, and please read through the entire rest of the post before adding a comment if you disagree:

Option 1

1. Disable Auto Update Statistics for the database
2. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)
3. Clear procedure cache or restart the instance after the update of statistics is complete

Option 2*

1. Disable Auto Update Statistics for the database
2. Before running any update statistics jobs, enable the Auto Update Statistics for the database.
3. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)
4. When the job is complete, disable the Auto Update Statistics for the database

*Joe Fleming ( t ) initially proposed this option in the comments of my original post

Option 3

1. Enable Auto Update Statistics for the database
2. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)

Before you skip straight to the comments to disagree, hear me out.  Remember that the majority of customers are not 24/7 shops, and do not have a full time DBA.  These customers have databases less than 250 GB, and can take the hit of rebuilding every index and updating statistics every week, every other week or once a month.  Is it overkill?  Probably.  But it works for the majority, which is what we have to target.

Do I love the option of clearing query cache?  No, not at all.  That suggestion probably gives many people a heart attack.  I understand.  We can try to mitigate the effect a bit by just clearing the cache for the one database (DBCC FLUSHPROCINDB(<db_id>)).  But yes, clearing the cache will cause every query to be freshly compiled, and this will utilize a lot more CPU, it will cause query duration to go up initially, and it will affect overall performance.  I get that.  But for a system that is not managed by a full time DBA, I will take this hit in order to ensure that the query plans are based on the current set of data.

Now, the ideal option is:

Option 4

1. Enable Auto Update Statistics for the database
2. Create a job to rebuild indexes and update statistics on a regular basis, dependent upon the level of fragmentation in an index and the need to update statistics because of changes to the data

This option is appropriate for any customer with a full time DBA who is comfortable managing fragmentation and statistics through custom scripts and jobs.  For example, many DBAs use Ola Hallengren’s Index and Statistics Maintenance script, or Michelle Ufford’s Index Defrag script.  To take things a step further, you can monitor when an automatic update of statistics occurs (use the Auto Stats Event Class in Trace), and you can capture snapshots of table row counts (sys.partitions) and index updates (sys.dm_db_index_usage_stats) to understand what tables have data that changes frequently.  High volume or volatile tables (those with a lot of inserts, updates and deletes) may require a more aggressive approach to managing statistics.

Ultimately, there is no silver bullet…no one perfect answer, unless that answer is “It depends.”  But again, you have to know what “it” depends on.  In this case, whether you should have the Auto Update Statistics option enabled or disabled for a database depends on your ability to manage statistics for a database, and understanding that having it disabled can lead to out of date query plans.