Best practices for SQL Server statistics

Auto-Update Statistics

What are statistics in SQL Server?

They are large binary objects (BLOBs) that contain information about the correlation and distribution of values in one or more columns of a table or indexed view.

The Query Optimizer uses statistics to create query plans that improve query performance.

By the way, this check is a part of our SQL Server Health Check service.

Enable Auto-update

It’s a best practice to have this ON.

SQL Server does a pretty good job of figuring out what stats to update, especially if you do not have custom maintenance plans written to deal with them.

To have this setting OFF makes sense, but this is only applying to particular scenarios that are rare (such as write-heavy workload, where stats don’t matter much).

  1. Open SSMS and right-click the database name and select Properties.
  2. In the options tab, enable the “auto update statistics” property.
Figure 1 – Auto update statistics options.

AUTO_UPDATE_STATISTICS_ASYNC option

Consider enabling the AUTO_UPDATE_STATISTICS_ASYNC option when your application frequently executes the same query, similar queries, or similar cached query plans. 

Usually, it’s a recommended option for online transaction processing (OLTP) environments, and it is less beneficial for data warehouse systems.

User-created statics

This may indicate that someone maybe doing something they do not fully understand.

It is fairly rare that stats are added manually by DBA.

This may be slowing things down, especially during stats updates.

Find out why these were added. If no good reason found, consider deleting them.

You can use the query below to each database to generate a script to drop all the user-created statistics:

  1. SELECT DISTINCT 'DROP STATISTICS '
  2. + SCHEMA_NAME(ob.Schema_id) + '.'
  3. + OBJECT_NAME(s.object_id) + '.' 
  4. +s.name DropStatisticsStatement
  5. FROM sys.stats s
  6. INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id
  7. WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys'
  8. AND Auto_Created = 0
  9. AND User_Created = 1

More information

Rajendra Gupta, SQLShack – SQL Server Statistics and how to perform Update Statistics in SQL
Tibor Nagy, MSSQLTips – SQL Server Auto Update and Auto Create Statistics Options
Microsoft – Statistics

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *