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).
- Open SSMS and right-click the database name and select Properties.
- In the options tab, enable the “auto update statistics” property.
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:
SELECT DISTINCT 'DROP STATISTICS '
+ SCHEMA_NAME(ob.Schema_id) + '.'
+ OBJECT_NAME(s.object_id) + '.'
+s.name DropStatisticsStatement
FROM sys.stats s
INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id
WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys'
AND Auto_Created = 0
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
