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 (4)

  1. Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start…

  2. msdngtnexus says:

    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?

  3. Auto-Update Statistics For a large majority of SQL Server installations, the most important best practice