SQL Server – Bad SELECT query performance due to missing indexes

by

When you have a slow SELECT query, the most likely cause of the problem is that you’re missing an index. When your table has lots of data, having the right indexes will make a difference in the performance.

Without indexes, SQL Server has to scan through your entire table to find the rows that match your query. Think of this as doing a linear search. With indexes, SQL Server can do an index seek instead. Think of this as doing a binary search, which is an order of magnitude faster than doing a linear search.

In this article, I’ll show how to check for missing indexes and then how to create an index.

1 – Load your table with data

Missing indexes don’t matter if your table has a small amount of data. Even if you have indexes, if your table has a small amount of data, SQL Server may do a scan instead of an index seek. This is because there is overhead cost associated with doing an index seek.

Therefore, the first step is to load your table with lots of data. I’d recommend generating realistic data and then bulk inserting it.

Disclaimer: This is assuming you’re doing your performance testing in a dev / QA environment, not in production.

2 – Check for missing indexes

The simplest way to check for missing indexes is to execute your query in SQL Server Management Studio (SSMS) with Show Actual Execution Plan enabled. It’ll tell you if there’s a missing index.

  • Click Show Actual Execution Plan (Ctrl-M).
  • Execute your query:
SELECT RevisionNumber
FROM [Sales].[SalesOrderHeader]
WHERE ShipDate = GETDATE()
Code language: SQL (Structured Query Language) (sql)
  • Look at the execution plan:
Execution plan showing missing index

The execution plan shows how the query was executed, and tells you if there are any missing indexes. It gives a recommendation about what index it thinks you should create.

Note: You can right-click on the missing index text and click Missing Index Details… if you want to see all the details.

3 – Create the index

Use the recommended index definition as a starting point:

CREATE NONCLUSTERED INDEX [IX_ShipDate] ON [Sales].[SalesOrderHeader]
(
	[ShipDate] ASC
)
Code language: SQL (Structured Query Language) (sql)

Whenever you create indexes, be sure to avoid the 10 Don’ts of Indexes (from SQL Authority).

(Optional) Optimize the index

The recommended index is usually good enough, but you may be able to optimize it further. Think about the actual usage of your queries and tailor your indexes to optimize your most important queries.

For example, recall the query shown in step 2:

SELECT RevisionNumber
FROM [Sales].[SalesOrderHeader]
WHERE ShipDate = GETDATE()
Code language: SQL (Structured Query Language) (sql)

Let’s say this is the most important query in your system. Notice that the query is selecting column RevisionNumber. To optimize the index, you can add RevisionNumber as an included column:

CREATE NONCLUSTERED INDEX [IX_ForShipDateRevisionNumberQuery] ON [Sales].[SalesOrderHeader]
(
	[ShipDate] ASC
)
INCLUDE([RevisionNumber])
Code language: SQL (Structured Query Language) (sql)

This speeds up the query by 50% because now it’s doing a pure index seek.

4 – Execute the query again

After creating the index, execute the original query and check the execution plan:

Execution plan showing it did an index seek

Notice that it did an index seek and there’s no longer a Missing Index warning.

Note: Remember to turn on Show Actual Execution Plan (Ctrl-M).

Related Articles