Share This Post

Updating Statistics in SQL (and why you need to do it)

SQL server uses statistics to keep track of values in an index, and determine when and how to use that particular index while processing a query. This is a horribly simplified definition (because I barely understand it), but basically it means that statistics are a way for SQL to find the best index to use. By default when you create a database in SQL 2005 (such as the ConfigMgr Database), the Auto Update Statistics option is turned on. You can check it by opening SQL Management Studio, right click on the database, select properties, then select the Options.

Now that you know what they are, it’s important to know when to manually kick off an update to the statistics. There are times when the key values in an index will change – especially in the ConfigMgr database. Patch Tuesday, for example – there is a lot of new data flooding the ConfigMgr and WSUS databases as clients scan and report back patch status. After large distributions also change a large amount of data in the indexes (status from distribution and advertisements).

Auto-Update of Statistics will catch these changes, but there will be times when you want your queries to execute at their fastest without waiting for the system task to kick off. There are also times when the system task will take a lower priority to other tasks, effectively keeping your statistics out of date. When you need to update the stats on index manually, use the following command:

UPDATE STATISTICS TABLENAME –replace Tablename with appropriate table

This works great on a single table, but who wants to do that for an entire database? Use the built-in stored procedure to update all statistics on all indexes in your database. Be aware that this can take some time, and if you don’t have Async Auto Update Statistics on, could cause queries to time-out while it’s running.

/******Code Below Here******/
USE ConfigMgr --change to the name of your databaseEXEC sp_updatestats /******Code Above Here******/

 

We use this on a set schedule, every 12 hours, to keep our stats update to date, and avoid any priority problems with the auto-update process. This does have an impact on indexes, so be sure you test accordingly.

Share This Post

Leave a Reply