Don’t rely solely on SQL Server’s missing index recommendations

Problem

If you are facing a slow query and SQL Server suggests a nonclustered index to improve its performance, don’t rely solely on the SQL Server missing indexes​ suggestion. Before creating it, review the index carefully; you may need to change it to achieve better performance. In this article, I will set up a scenario where SQL Server recommends a missing index to improve query performance, but I will change it to achieve even better performance.

Solution

The main goal of creating indexes is to reduce query execution time. Before diving in too deep, understand that a well-designed index can reduce disk I/O operations and consume fewer system resources. To see more details about row-based indexes, review clustered and nonclustered indexes.

SQL Server attempts to help identify missing indexes. When viewing a query execution plan, you may notice that SQL Server suggests a missing index. Indeed, the Query Optimizer informs you that creating these proposed indexes can improve query performance. Missing index recommendation is a built-in SQL Server tool that helps you find missing indexes. Learn more about this tool: Tune nonclustered indexes with missing index suggestions.

Set Up Test Environment

This demonstration uses SQL Server 2022 and StackOverflow, an open-source database from StackOverflow.com. Run the code below in the master database to set the database compatibility level to 160 after restoring.

Use master
GO
Alter Database StackOverflow Set Compatibility_Level = 160
GO

I’m switching to the StackOverflow database and executing the following query. To display the actual execution plan, press CTRL + M in SQL Server Management Studio (SSMS).

Select u.DisplayName, u.Location, COUNT(u.Reputation) AS [Count]
From dbo.Users u Where u.Reputation = 9
Group By u.DisplayName, u.Location
Order By COUNT(u.Reputation) Desc
GO

This query retrieves the DisplayName, Location, and the count of users who have a reputation of 9. The results are grouped by DisplayName and Location, and ordered by count in descending order. Since the Users table only has a unique clustered index on the Id column (the primary key), SQL Server scans the entire table to return the query results.

You can see part of the actual execution plan in the image below:

SQL Server has suggested a nonclustered index to improve query performance. If you right-click on the execution plan and select Missing Index Details, you will see a script like this:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Users] ([Reputation])
INCLUDE ([DisplayName],[Location])

To create the suggested index, specify a name for it and use the compression option (this option causes the index to be created faster on my laptop).

CREATE NONCLUSTERED INDEX IX_Reputation_Include
ON [dbo].[Users] ([Reputation])
INCLUDE ([DisplayName],[Location])
With (Data_Compression = Page)
GO

After creating the index suggested by SQL Server, execute the query once more:

Select u.DisplayName, u.Location, COUNT(u.Reputation) AS [Count]
From dbo.Users u Where u.Reputation = 9
Group By u.DisplayName, u.Location
Order By COUNT(u.Reputation) Desc
GO

As shown below, SQL Server first performed a nonclustered index seek and then sorted the rows before sending them to the Stream Aggregate operator.

SQL Server sorts the rows because the Stream Aggregate operator requires sorted data. If you are interested in learning more about this operator, visit Stream Aggregate. All performance tuners know that the Sort operator is resource-intensive and blocking.

SQL Server fetches the rows from the nonclustered index and then sorts them based on the DisplayName and Location columns, because these columns are not part of the index key, and the data was not sorted in the nonclustered index based on them. The DisplayName and Location columns are only placed in the INCLUDE section.

If you’re running this demo in SSMS, hover your mouse over the Sort operator to view these details visually.

How to Remove the Sort Operator from the Execution Plan

The solution is straightforward. In order to deliver sorted data to the Stream Aggregate operator, we need to put the DisplayName and Location columns in the index key. Create a new nonclustered index, which is a composite index. A composite key index is simply an index with multiple key columns.

CREATE NONCLUSTERED INDEX IX_Reputation_DisplayName_Location
ON [dbo].[Users] ([Reputation], [DisplayName],[Location])
With (Data_Compression = Page)
GO
 
Select u.DisplayName, u.Location, COUNT(u.Reputation) AS [Count]
From dbo.Users u Where u.Reputation = 9
Group By u.DisplayName, u.Location
Order By COUNT(u.Reputation) Desc
GO

In the image below, SQL Server performs a nonclustered index seek operation and then pushes the rows to the Stream Aggregate operator without sorting them. Note: SQL Server ignored the old index and used the new index.

What is the Difference Between the Two Indexes?

When all columns in the query are included in the index, either as key or nonkey columns, the performance of the query can improve significantly. For more details, see Create Indexes with Included Columns. Both indexes used in our examples are covering indexes because they cover all the columns used in the query. However, the index suggested by SQL Server is an index with included columns. A nonclustered index is only sorted based on its key column(s). It is not sorted by included or non-key columns. As you saw above, when SQL Server used the index with included columns, it sorted the rows based on the non-key columns before sending them to the Stream Aggregate operator.

Summary

In this article, I implemented a scenario and showed that you should not rely solely on SQL Server’s missing index recommendations. You should review it based on your knowledge, because you may need to change the index to achieve better performance.

Next Steps

• SQL Server non-clustered Indexes
• What are the Aggregate Functions in SQL
• SQL Server T-SQL Aggregate Functions
• Query Plan Example