Improve Query Performance when SQL Server Ignores Nonclustered Index

Problem

Do you wonder why SQL Server ignores an index? Do you think about how you can deal with this problem? When you include too many columns in a query you decrease index selectivity. So, if you write a query like “Select * From …” the chance of choosing your nonclustered index will decrease.

Solution

As we know, often there is no single reason for a problem. There are many reasons why SQL Server does not use an index. One is when the query optimizer thinks the cost of key lookups is high.

In this article, we’ll show how avoiding fetching many columns in a query can enable you to create a covering index that SQL Server will use.

What are Key Lookups?

Key lookups occur when the query optimizer uses a nonclustered index and this index does not contain all the columns that the query needs to display results. So, the SQL Server needs to refer to the clustered index to access those columns.

As I said, sometimes the query optimizer detects that the cost of key lookups is high, and it thinks that scanning the whole table is more efficient than performing a nonclustered index seek followed by key lookups. Ultimately, the query optimizer does not use your nonclustered index. It’s as simple as that.

What is a Covering Index?

When a nonclustered index contains all the columns used in a query, this index is a covering index for that query. So, if you run a query where all its columns exist in a nonclustered index, then the query optimizer will use this index. When you execute a query that returns many columns, depending on how many rows will come back, the query optimizer may or may not use your index.

Therefore, if you are searching for a solution that guarantees the SQL Server will use a nonclustered index for a query, reduce the number of columns fetched in the query, only include the columns you need in the SELECT list, and then create an index including all those columns. SQL Server enables you to create an index that contains all columns of a table, but who does this?! Because creating an index that includes many columns slows down DML operations and increases the size of your database.

Set Up Test Environment

Materials for this demo are:

• SQL Server 2022 (compatibility level 160)
• StackOverflow database (medium size)
• Users table
• Creating a nonclustered index on the location column

Use StackOverflow
GO 
 
Alter Database Current Set Compatibility_Level = 160
GO
 
Create Index IX_Location On dbo.Users (Location)
GO

To display the number of pages that SQL Server reads from the Buffer Pool, I use this command:

SET STATISTICS IO ON
GO

To find out how SQL Server executes a query, we need to see the Actual Execution Plan. You can view it by pressing CTRL+M. After executing the query, it will appear in the execution plan tab in SSMS. If I write a query to display the information of people who reside in India and Germany, SQL Server will scan the entire users table and will not use the index that I have created.

Select * From dbo.Users u Where u.Location In (N'India', N'Germany'
GO

SQL Server did not use our index and performed a clustered index scan operation.

The number of logical reads is more than 141,000.

Did SQL Server make the right decision? Absolutely. If I force the Query Optimizer to use the nonclustered index using an index hint, the number of logical reads will be more than when SQL Server scanned the entire table. You can see that the Query Optimizer used the nonclustered index.

But the number of logical reads increased to nearly 225,000.

It was not the Query Optimizer’s choice, we forced it.

I have heard from many DBAs who said using a temporary table instead of the IN operator decreases query execution time. Let’s do this.

Drop Table If Exists #Location
 
Create Table #Location ([Location] Nvarchar(100) Collate SQL_Latin1_General_CP1_CI_AS)
 
Insert Into #Location Values (N'India'), (N'Germany')
 
Select u.* From dbo.Users u Inner Join #Location l On u.Location = l.Location
GO

SQL Server used a nonclustered index seek followed by a key lookup.

The query execution time was reduced to nearly 1 second, which is great compared to when SQL Server scanned the entire table and the elapsed time was higher than 4 seconds. However, the number of logical reads increased to almost 225000 again.

You may ask: When the query execution time has been reduced, what is the importance of the number of logical reads? It doesn’t matter to an end user. But from a DBA’s perspective, it does, since the resources are limited. In other words, each storage system has a limitation on I/O. I need a solution to reduce both the number of logical reads and the query execution time.

First, I will remove an asterisk from the query and fetch the columns I need.

Select u.Location, u.DisplayName, u.Reputation, u.LastAccessDate 
From dbo.Users u 
Where Location in (N'India', N'Germany')
GO

The second step is dropping the nonclustered index and creating a new index that includes all the columns used in the query. It is a covering index.

Drop Index IX_Location On dbo.Users 
 
Create Index IX_Location_Include On dbo.Users (Location) Include (DisplayName, Reputation, LastAccessDate)
GO

To show CPU time and elapsed time for a query, I use the command below:

Set Statistics Time On
GO

If I execute the query one last time in this demo, the Query Optimizer performs only a nonclustered index seek operation. There is no key lookup icon in the image.

The elapsed time is 362 milliseconds and the CPU time is 78 milliseconds:

Pay attention to the number of logical reads. It’s only 541. That is amazing!

Summary

One of the main reasons the Query Optimizer does not use the nonclustered indexes is that it considers the cost of the key lookup to be high. When SQL Server ignores a nonclustered index for the mentioned reason, the solution is to fetch only the columns you need and use a covering index.

Next Steps

• Improve SQL Server Performance with Covering Index Enhancements
• SQL Server Indexes with Key and Non-Key Columns as Covering Indexes to improve Performance
• SQL IN Operator

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