Conditionally Updating Statistics

Published Mar 23 2019 12:15 PM 350 Views
Microsoft
First published on TECHNET on Jul 19, 2011

SQL Server's query optimization engine uses statistics on indexes to determine the most efficient execution plans. By default, SQL Server automatically updates statistics, but sometimes the automatic processes don't update them soon enough, so there are multiple ways to force them to update to help keep your queries running as efficiently as possible. The main options are the UPDATE STATISTICS command and the sp_updatestats system stored procedure.


You may be aware that you can choose an Update Statistics task in SQL Server Management Studio's (SSMS) Maintenance Plan Wizard, but that task is dumb, in the sense that it will recompute stats on all indexes in a targeted database, whether they need it or not. This means that sp_updatestats is generally a better alternative, to avoid unnecessary CPU load, because sp_updatestats will only update statistics for indexes that need it. However, sp_updatestats executes per-database, and it does not provide fine-level control (e.g. sampling rate, all/column/index, etc.). In my experience, most systems don't need fine-level control, so the best option in most cases is to use sp_msforeachdb to loop through all databases and execute sp_updatestats, which will only update stats on those databases that need it:


EXEC sp_msforeachdb 'USE [?] EXEC sp_updatestats'


Put the command above in a SQL Server Agent job, schedule it, and you're done.


Advanced Options


1. If you happen to manage an instance of SQL Server that needs manual control over updating statistics, you can use the UPDATE STATISTICS command and specify options such as the sampling rate and whether to compute stats for all/column/index. Simply run whatever specific UPDATE STATISTICS commands you need, whenever you need them, and leave your scheduled sp_updatestats job alone. When the sp_updatestats job runs, if the targets of your specific UPDATE STATISTICS commands don't need updating, they'll be skipped.


2. Don't run sp_updatestats on a schedule and just run UPDATE STATISTICS after specific events, such as right after large inserts, updates, or deletes.


Notes


1. Rebuilding an index will cause SQL Server to recompute statistics, but reorganizing an index won't. If you want to experiment with rebuilds, reorgs, and updating stats, or just view a query that shows you index fragmentation and the date/time of the last statistics update of each index, see the attached script.


2. Why not just open sp_updatestats, copy the code, and tweak it to do whatever you want? Mainly because it uses a system function (stats_ver_current) that can only be run from inside a system procedure.



Experiment with statistics.sql

Version history
Last update:
‎Mar 23 2019 12:15 PM
Updated by: