Why isn't SQL Server using my index?

Question

In our database we have this table with 200.000 rows ``` CREATE TABLE dbo.UserTask ( UserTask_ID int NOT NULL IDENTITY (1, 1), UserTask_SequenceNumber int NOT NULL DEFAULT 0, UserTask_IdEntitat uniqueidentifier NOT NULL, UserTask_Subject varchar(100) NOT NULL, UserTask_Description varchar(500) NOT NULL, ..... ..... CONSTRAINT [PK_UserTask] PRIMARY KEY CLUSTERED ( [UserTask_ID] ASC ) ON [PRIMARY] ) ON [PRIMARY] ``` I have created an index on `UserTask_IdEntitat` column with ``` CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat ON dbo.UserTask ( UserTask_IDEntitat ) ``` Executing the following query, execution plan shows us that index on `UserTask_IDEntitat` is used to do the query: ``` SELECT UserTask_ID FROM UserTask WHERE UserTask_IdEntitat = @IdEntitat ORDER BY UserTask_LastSendSystemDateTime desc ``` But If we add another column in the `Select` list, then the index is not used ``` SELECT UserTask_ID, UserTask_SequenceNumber, UserTask_IDEntitat, ....., UserTask_Subject FROM UserTask W…

Accepted Answer

OK - as long as you select only the column that's in the index, or something from the clustering key (usually, this is the primary key), then the index will be used, since SQL Server can find all the information it needs (the `UserTask_IDEntitat` column, and the clustered index column(s) ) in the leaf level of the index navigation structure. So it can return the data needed for that `SELECT` query directly from the index's leaf level pages.

However: if you need to select a second column, that is neither in the index definition, nor part of the clustering key, then SQL Server would have to do a so-called bookmark lookup into the actual data pages.

So for every single row it finds in your nonclustered index, it would have to take the clustering index value, search the clustered index to find the actual data page at the leaf level of that clustered index, and then pick out that one column that you want.

Bookmark lookups are great for small numbers of hits - they are totally devastating for performance if you're selecting thousands of rows. In that case, the SQL Server query optimizer correctly uses a clustered index scan instead - since in the clustered index, on the leaf level, it has all the rows available right away.

So: if you have an index on `UserTask_IDEntitat` and you sometimes need a second column `UserTask_SequenceNumber` too - then you could include that column in that nonclustered index of yours:

```
CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat 
ON dbo.UserTask(UserTask_IDEntitat)
INCLUDE(UserTask_SequenceNumber)
```

With this, that additional column is present in the leaf level of that non-clustered index only (it cannot be used in a `WHERE` clause - it's not part of the navigation structure of the index!) - and your second `SELECT` can again be satisfied from the leaf-level nodes of the nonclustered index -> no expensive bookmark lookups are needed -> your index will be used again.

Long story short: unless your nonclustered index is highly selective (e.g. returns 1% of your rows or less), and unless your nonclustered index is a covering index (an index that contains all the columns needed to satisfy a particular query), then changes are pretty high that SQL Server will NOT use your nonclustered index.

For more information:

- SQL Server Indexing Basics

- SQL Server – Learning SQL Server Performance: Indexing Basics – Video

Why isn't SQL Server using my index?

Solution

Problem