'Why isn't SQL Server using my index?

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.

sql-serverindexing


Solution 1:[1]

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

Solution 2:[2]

I faced with situation when the same query makes different plan on different databases. On the one DB it use non-clustered index and on the other - table scan.

Also this index doesn't have all field in INCLUDE, and the best solution here would be to add all necessary selected fields to index INCLUDE. In my case drop free cache helps though.

DBCC freeproccache

Sometimes query plan builder ignores index if it has fragmentation more 50%, because it spend more time to find row in index than to scan the entire table.

Solution 3:[3]

I have seen it be the case where covering indexes are not used when there isn't a sufficient amount of data in the tables to sufficiently populate Statistics that the SQL Query Optimizer uses for Cardinality Estimation which ultimately impacts which indexes (if any) are chosen for the execution plan.

For instance, I ran the same sql query with the same input data across 3 distinct databases. Running the stored procedure against each of the 3 databases yields the same output. All 3 databases have the same database options, settings, COMPATIBILITY_LEVEL, and schema. There were two only differences between the databases:

  1. the amount of data. The first 2 where the expected indexes were used had about 1000x more data than the 3rd where the indexes were not used.
  2. The SQL-created statistics (prefixed with _WA_Sys). There were more auto-generated statistics in the databases where there was much more data. The other had a few of the auto-generated statistics, but not all.

As a result, the Histograms for the covered indexes were much different causing the Query Optimizer to choose different execution plans.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sonam
Solution 2 RobIII
Solution 3 Dave Black

Related Questions