SQL Server Performance Tuning

Best Practices For SQL Server Statistics

Updated
3 min read
Written by
Mark Varnas

What are statistics in SQL Server?

SQL statistics are like Google Maps guiding you to your destination.

SQL stats show SQL Server where data lives in the table and how to locate it. The Query Optimizer then uses these stats to create efficient query plans, similar to a Google Map for data access. If the “map” is outdated, you’ll still reach your data, but it may take significantly longer.

It’s the same with SQL Statistics—if they’re outdated, your queries will run slower. One way to resolve this is to:

Enable the “statistics auto-update” option

Best practice to have this setting set to ON.

SQL Server generally does a solid job of updating necessary stats, especially if there aren’t custom maintenance plans in place.

It’s rare to find cases where turning this setting OFF is beneficial, but it can be useful for write-heavy workloads where the latest stats aren’t essential. However, since write-heavy workloads are uncommon, this likely doesn’t apply to you.

To check that “Auto Update Statistics” is ON, here’s what I do:

  1. Open SSMS
  2. Right-click on the database name
  3. Choose Properties
  4. Go to the Options tab
  5. Set the Auto Update Statistics property to “True.”

AUTO_UPDATE_STATISTICS_ASYNC option


Enable the AUTO_UPDATE_STATISTICS_ASYNC option if your application frequently runs the same or similar queries with cached query plans. This setting is generally recommended for online transaction processing (OLTP) environments and is less useful for data warehouse systems.

And one more thing. Look out for:

User-created statistics


If user-created statistics are present, especially in large numbers or for complex data, it may signal that someone unfamiliar with best practices has made adjustments. It’s uncommon for even experienced DBAs to add stats manually, as these additions can slow performance, particularly during the Update Statistics process.


Determine the reason for adding user-created stats—removing them is often the best approach.


Here’s a query I run on each database to script out all user-created statistics, allowing you to manually choose which stats to drop.

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

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials