Update Statistics in SQL Server for Better Performance

Problem

Your query runs slow, and clients are complaining that their app is not fast anymore. Things used to run faster, so why are things so much slower now? This tip will show how statistics can impact queries and why you may want to manually update statistics in SQL Server. Also, it will explain what statistics are, the importance of up-to-date statistics for query optimization, and define Cardinality Estimation (CE).

Solution

Statistics are lightweight objects that specify data distribution metrics and created as follows:

Automatically by SQL Server
Manually by users

Modifications such as inserts, updates, deletes, or merges can change the distribution of data within a table or indexed view. These changes could cause the statistics to become outdated. SQL Server updates statistics automatically but there may be times when we still may need to update the statistics manually.

Starting with SQL Server 2016 (13.x) and database compatibility level 130, statistics updates occur more frequently for large tables. However, we will see that even in SQL Server 2022, manually updating statistics can still be a critical task.

Importance of SQL Server Statistics

When you run a query against a table, the query optimizer uses statistics to estimate how many rows will come back from that table. In other words, the statistics help SQL Server build a query execution plan. Based on the estimated number of rows, the query optimizer allocates memory and CPU resources to execute the query. Generally speaking, when the estimated and actual number of rows are similar, SQL Server builds a good query plan. However, this does not always mean that the query runs quickly.

What is Cardinality Estimation (CE)?

The query optimizer estimates the number of rows and contents for each operator in a query plan known as Cardinality Estimation. This estimate is based on the statistics and plays a key role in picking a good plan.

Set Up Test Environment

This tutorial uses SQL Server 2022 at compatibility level 160 and the StackOverflow database.

Use master
GO
 
Alter Database StackOverflow Set Compatibility_Level = 160
GO

There is an option at the database level called AUTO_UPDATE_STATISTICS. This option is enabled by default, but if you need to enable it manually, use the following script:

ALTER DATABASE StackOverflow SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO

In this version of the StackOverflow database, there is a Users table that stores nearly 9 million records. It stores user information such as Reputation, Location, DisplayName, etc. Let’s create a nonclustered index on the location column.

Use StackOverflow 
GO
 
Create Index IX_Location On dbo.Users (Location)
With (Data_Compression = Page)
GO

Upon doing this, SQL Server automatically created a statistic for it. See the details using this command:

DBCC Show_Statistics (Users, IX_Location)
GO

To read more about this command, visit Microsoft’s website: DBCC SHOW_STATISTICS (Transact-SQL).

Here, we can see the results in a visual format below:

We can see that there are 63,133 rows in the Users table where the Location column is empty (”):

Run Query to See How SQL Server Accessed the Data

Let’s write a query that retrieves information about people whose location field is empty. To display the actual execution plan, simply press CTRL + M in SSMS.

Select * From dbo.Users Where Location = N''
Order By Reputation Desc
GO

SQL Server scanned the entire table to display users who did not enter their location.

Let’s assume that these users are invalid and we need to remove them from the Users table.

Delete From dbo.Users Where Location = N''
GO

Now, there are no records in the Users table where the location column is empty. I will first remove all query plans from the plan cache and then run the query once more. To figure out how many data pages SQL Server reads from the buffer pool, let’s turn on STATISTICS IO.

DBCC FREEPROCCACHE
GO
 
SET STATISTICS IO ON
GO
 
Select * From dbo.Users Where Location = N''
Order By Reputation Desc
GO

Look at the image; no rows matched the query, but SQL Server still scanned the table.

SQL Server estimated that nearly 63,000 rows would be returned (the estimated number of rows is 62,686), but in reality, the query returned no results (the actual number of rows is zero). You can see this in the image below:

Look at the number of logical reads: it is over 90,000.

Here, we can see that the granted memory is approximately 370 MB.

This image shows that SQL Server executed the query in parallel mode:

SQL Server mistakenly estimated that there are nearly 63,000 users whose location field is empty. This is because the data has changed, but the statistics have not changed.

DBCC Show_Statistics (Users, IX_Location)
GO

Here you can see that the statistics have not been updated automatically:

Update Statistics Manually and Rerun Query

Now, let’s update the statistics manually.

Update Statistics dbo.Users (IX_Location) With FullScan
GO

Execute the query and check the actual execution plan.

Select * 
From dbo.Users 
Where Location = N''
Order By Reputation Desc
GO

SQL Server performed a non-clustered seek operation followed by a key lookup:

SQL Server executed the query in serial mode and the memory grant has been reduced to only 1 MB.

The latest image shows that the number of logical reads has dropped to 4.

Note: Updating statistics with the FULLSCAN option is a long process on large tables. It is strongly recommended to use Ola Hallengren’s free script for statistics maintenance.

Summary

Overall, when a query is slow, compare the estimated number of rows with the actual number of rows. If they are very different (more than 10x), there are two main reasons for it:

Outdated statistics
Parameter Sniffing issue

It was assumed that there was no missing index on the table. So, by checking the statistics and knowing they are outdated, first check the AUTO_UPDATE_STATISTICS option. If it is enabled, update the statistics manually. It may be necessary to set up a weekly or even nightly job to update statistics.

Next Steps

Mehdi Ghapanvari

Mehdi Ghapanvari is an SQL Server database administrator with 6+ years of experience. His main area of expertise is improving database performance. He is skilled at managing high-performance servers that can handle several terabytes of data and hundreds of queries per second, ensuring their availability and reliability. To enhance the performance of the database, he has implemented various performance-tuning techniques, including query optimization and index tuning. He has also developed backup and recovery strategies to protect critical data in case of disasters. Mehdi currently manages the SQL Server databases for a large organization. Mehdi is actively seeking new opportunities to apply his expertise and contribute to impactful projects. You can reach him at SQLDBA.Mehdi@Gmail.com.

MSSQLTips Awards: Rookie of the Year – 2023
MSSQLTips Trendsetter Award: 2026