Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Updating Statistics

Minimizing index fragmentation will help you to maintain a high level of performance. However, such time-consuming tasks should only be performed during the off-peak hours. In addition, if you're trying to provide a high level of uptime, you probably can't afford to run such jobs more than once a week. Fortunately, you can perform one activity on a regular basis to help your indexes: updating the statistics.

The index statistics inform SQL Server about the distribution of key values in the index. If statistics are outdated, SQL Server might not be able to evaluate index effectiveness appropriately, and might choose to scan a table instead of scanning the index. Up-to-date statistics guarantee effective use of indexes. SQL Server can update statistics automatically, but doing so slows down performance a bit. Alternatively, you can schedule a job that executes the UPDATE STATISTICS statement or executes the system procedure sp_updatestats. The difference between the two is that UPDATE STATISTICS requires the table (view) or index name and performs an update on the specified object. The sp_updatestats procedure simply runs through a cursor containing all user tables and runs UPDATE STATISTICS on each. Updating statistics on large tables with multiple indexes might take a long time, so generally it's better to run UPDATE STATISTICS on such tables separately from the rest of the tables. For instance, run UPDATE STATISTICS on small tables on Monday, Wednesday, and Friday and do the same on large tables on Tuesday, Thursday, Saturday, and Sunday.

Summary

This article showed you how to maintain your database's health. Tuning a database once is not sufficient; you need to perform regular maintenance to keep your application in good shape.

  • + Share This
  • 🔖 Save To Your Account