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     WHERE UserTask_IdEntitat = @IdEntitat   ORDER BY UserTask_LastSendSystemDateTime desc 

Why adding a column different from the primary key makes that the SQL Server execution plan doesn't use the index on the UserTask_IDEntitat column?

Following this link http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index it seems that the number of times that the filtered value is repeated on the column, It can make that the index is not used, but I have tried doing the query with an @IdEntitat value that is repeated 60.000 times and other that is repeated only 175 times and the results are the same, the index on IDEntitat column is ignored.

This is taking me crazy!!!

Thanks for your help.

Answer 1

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

Answer 2

You can use the query hints in the query to make use of Index. Following is a link for further details: http://msdn.microsoft.com/en-us/library/ms181714.aspx