How to automate Update Statistics
For SQL Server 2005 here are some options to update statistics with the default settings that samples the data instead of reading every row:
1) If you are also defragmenting your database with REBUILD and/or REORGANIZE you will want to integrate your Update Statistics into that schedule. When you do a REBUILD you are essential getting the equivalent of an UPDATE STATISTICS WITH FULLSCAN so you do not want to turn around do a sampling of statistics or another FULLSCAN too soon after the REBUILD.
Paul Randal talks about the interaction of the two: https://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx
A great script that lets you schedule your defragmentation and statistics maintenance all at once: SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization
If you choose to run this script you will need three components created in your master database.
CommandExecute and DatabaseSelect and IndexOptimize
2) Here is how to loop through all database and do an UPDATE STATISTICS on all objects (within the restrictions of the sp_updatestats procedure). It uses the undocumented and unsupported (but widely used) sys.sp_MSforeachdb. The query also captures how long it takes per database:
exec master.sys.sp_MSforeachdb ' USE [?];
DECLARE @starttime datetime, @endtime datetime
SELECT @starttime = GETDATE()
SELECT db_name() as CurrentDB, @starttime as DBStartTime
EXEC sp_updatestats
SELECT @endtime = GETDATE()
SELECT @starttime as StartTime, @endtime as EndTime, DATEDIFF(MINUTE,@starttime,@endtime) as TotalMinutes
3) If you want more control over which statistics are updated, you can specify an individual object or index:
UPDATE STATISTICS [table_name]
or
UPDATE STATISTICS [table_name] [index_name/statistics_name]
4) Create an SSIS package that uses the "UPDATE STATISTICS Task" as part of a maintenance plan. You can create it manually or with the maintenance plan wizard. Update Statistics Task (Maintenance Plan) https://msdn.microsoft.com/en-us/library/ms178678.aspx
Considerations:
· In most cases you will want auto update and auto create statistics ON and you will NOT want to use the RECOMPUTE option as that turns off the automatic updating. This (having auto stats on) works best when you have a regularly scheduled UPDATE STATISTICS (on a schedule and/or after large data modifications/batches) to reduce the chance that the auto update has to kick in during a busy time.
· In many cases you will also want AUTO_UPDATE_STATISTICS_ASYNC on so that auto stats don't make the query that pushed the stats over the edge to wait for updated statistics. The downside of this is that the query that ran right after the statistics reach the "stale" threshold will use the old statistics. Whether it is worth it for any given query to wait depends on how long it takes to update the statistics, whether the query plan changes afterwards, and whether a different query plan causes a significant difference in total response time.
· Note from BOL: For databases with a compatibility level below 90, executing sp_updatestats resets the automatic UPDATE STATISTICS setting for all indexes and statistics on every table in the current database. For more information, see sp_autostats (Transact-SQL). For databases with a compatibility level of 90 or higher, sp_updatestats preserves the automatic UPDATE STATISTICS setting for any particular index or statistics.
References:
· Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 https://technet.microsoft.com/en-us/library/cc966419.aspx
· Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 https://msdn.microsoft.com/en-us/library/dd535534.aspx