Have you ever wondered why statistics are not being updated in your SQL Server database, even though you have performed numerous inserts? In this article, we will explore the reasons behind this issue and discuss how to fix it.

When you create a nonclustered index on a column in SQL Server, statistics are automatically created for that column. These statistics help the query optimizer make informed decisions about the most efficient way to execute queries. However, the statistics are not always updated automatically, which can lead to outdated execution plans and suboptimal query performance.

Let’s take a look at an example to understand this behavior:

-- Create Sample Database
CREATE DATABASE SampleDB
GO
USE SampleDB
GO

-- Create Table
CREATE TABLE ExecTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO

-- Create Nonclustered Index on column City
CREATE NONCLUSTERED INDEX IX_ExecTable1
ON ExecTable (City);
GO

-- Insert One Thousand Records
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

-- Display statistics of the table
sp_helpstats N'ExecTable', 'ALL'
GO

-- Select Statement
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City = 'New York'
GO

-- Display statistics of the table
sp_helpstats N'ExecTable', 'ALL'
GO

-- Clean up Database
DROP TABLE ExecTable
GO

In this example, we create a nonclustered index on the “City” column of the “ExecTable” table. This automatically creates statistics for the same column. We then insert 1000 records into the table and display the statistics. At this point, the statistics are up to date.

However, when we insert another 1000 records into the table and execute the same select statement, we find that the statistics are not updated. This is because the execution plan is reused and the query optimizer does not see the need to update the statistics.

To fix this issue, we can create a nonclustered index on the “City” column. This will cause the index to be updated, which in turn triggers the update of the statistics. Here is the updated code:

-- Create Nonclustered Index on column City
CREATE NONCLUSTERED INDEX IX_ExecTable1
ON ExecTable (City);
GO

By creating the nonclustered index, we ensure that the statistics are updated whenever the index is updated. This leads to more accurate execution plans and improved query performance.

It’s important to note that this behavior does not occur for heaps or columns where the index is auto-created. In those cases, you may need to explicitly update the index to update the statistics.

While clearing the execution plan cache or recompiling each query every time can also update the statistics, it is not recommended for production servers. These methods should only be used for troubleshooting purposes.

In conclusion, understanding SQL Server statistics and how to update them is crucial for optimizing query performance. By creating nonclustered indexes on relevant columns, you can ensure that the statistics are updated automatically, leading to more efficient execution plans.

If you have any suggestions or better solutions for updating statistics in SQL Server, please feel free to share them. We are always open to improving our solutions and providing the best practices for SQL Server administration.

Thank you for reading!