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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.