Introduction

If you are already using Ola’s maintenance script (you should if you don’t) to facilitate SQL Server routine maintenance on your private or public cloud, you are familiar with Ola’s Index and Statistics Maintenance procedure. In a nutshell, Ola’s Maintenance allows for a few easy steps to complement SQL Server installation with routine basic maintenance tasks – backups, corruption checks, and index and statistics. While backups and corruption checks are designed to keep your business running, the index and statistics routine is designed to improve performance.

The following blog assumes that you are using Ola Hallengren maintenance scripts and is going to offer a solution to run Statistics maintenance separately while still using the same IndexOptimize procedure. Additionally, it has logic to know which databases need Statistics maintenance the most.

Problem

In theory, running an Index and Statistics routine as often as possible should make things faster. In practice, running this routine has a price. Generally, more expensive for the index and less expensive for statistics maintenance. According to Microsoft, “Index maintenance, performed by either reorganizing or rebuilding an index, is resource-intensive. It causes a significant increase in CPU utilization, memory used, and storage I/O. Another tip from Microsoft is “Resource cost of updating statistics is minor compared to index rebuild, and the operation often completes in minutes instead of hours that may be required for index rebuilds.”

While Ola’s excellent procedure works great as-is, sooner or later, you might want to revisit that and separate Index maintenance from Statistics maintenance. What that means is that you can run Statistics maintenance more often (like every hour) than Index maintenance and virtually achieve the same performance gain with less of an index maintenance pain. But let’s not stop at just running Statistics separately and more often. We can also include logic to know which databases are frequently used (based on reads and writes) and give those databases a priority.

Solution

The following solution on GitHub will identify “hot” databases and will update stats without touching indexes.

https://github.com/steverezhener/DBA/blob/master/Procedures/bestpractices.usp_refresh_database_stats_on_active_databases.sql

Disclaimer
This blog post is based on the following resources:
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes