Auto-create and Auto-update Statistics

For a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide. Auto create and auto update statistics are on by default. If you observe bad plans and suspect that missing or out of date statistics are at fault, verify that auto create and auto update statistics are on.

An alternative to enabling auto create statistics is enabled or make sure to manually create statistics using CREATE STATISTICS or sp_createstats. Note that auto-statistics will not work for read-only databases.

Comments

• Anonymous
  September 25, 2006
  Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start...

• Anonymous
  October 22, 2007
  Is there any way to to auto create and auto update statistics at "fullscan" or at "50% sample" all the time. Most of the specificity/density calculations for columns/indexes for our tables are completely skewed when they are sampled at the default sampling ratio. As a result our query plans get shot after that.  Right now, we are writing scripts to recompute stats at fullscan periodically. That doesnt seem right. I want auto create stats and auto update of stats. But I want to specify the sampling ratio. What is the easiest way of doing that?

• Anonymous
  August 27, 2008
  Auto-Update Statistics For a large majority of SQL Server installations, the most important best practice

• Anonymous
  January 18, 2009
  PingBack from http://www.keyongtech.com/2098799-easy-columns-indexing