Best practices for SQL Server statistics

Category: Performance

Item: 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.

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

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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