SQL Server why index is not used

Question

I have a following table in SQL Server 2008 database:

CREATE TABLE [dbo].[Actions](
    [ActionId] [int] IDENTITY(1,1) NOT NULL,    
    [ActionTypeId] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](1000) NOT NULL,
    [Comment] [nvarchar](500) NOT NULL,
    [Created] [datetime] NOT NULL,
    [Executed] [datetime] NULL,
    [DisplayText] [nvarchar](1000) NULL,    
    [ExecutedBy] [int] NULL,
    [Result] [int] NULL
)   
CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED 
(
    [CaseActionId] ASC
)
) ON [PRIMARY]

GO


CREATE NONCLUSTERED INDEX [IX_Actions_Executed] ON [dbo].[Actions] 
(
    [Executed] ASC,
    [ExecutedBy] ASC
)

There are 20 000 rows which has Executed date equal to '2500-01-01' and 420 000 rows which has Executed date < '2500-01-01'.

When I execute a query

select CaseActionId, Executed, ExecutedBy, DisplayText from CaseActions
where Executed='2500-01-01'  

the query plans shows that the clustered index scan on PK_Actions is performed and the index IX_Actions_Executed is not used at all.

What funny I got missing index hint which says

/* The Query Processor estimates that implementing the following index could improve the query cost by 99.9901%.
*/

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Actions] ([Executed])

But the index is already there.

Why the index is not used if it would select 5% of the data ?

Answer 1

Most likely, the query optimizer just sees that you're selecting DisplayText as well - so for each of the 20'000 rows found in the NC index, there would need to be a key lookup into the clustered index to get that data - and key lookups are expensive operations! So in the end, it might just be easier and more efficient to scan the clustere index right away.

I bet if you run this query here:

select CaseActionId, Executed, ExecutedBy
from CaseActions
where Executed='2500-01-01'

then the NC index will be used

If you really need the DisplayText and that's a query you'll run frequently, maybe you should include that column in the index as an extra column in the leaf level:

DROP INDEX [IX_Actions_Executed] 

CREATE NONCLUSTERED INDEX [IX_Actions_Executed] 
ON [dbo].[Actions]([Executed] ASC, [ExecutedBy] ASC)
INCLUDE([DisplayText])

This would make your NC index a covering index, i.e. it could return all columns needed for your query. If you run your original query again with this covering index in place, I'm pretty sure SQL Server's query optimizer will indeed use it. The probability that any NC index will be used is significantly increased if that NC index is a covering index, e.g. some queries can get all their columns they need from just the NC index, without key lookups.

The missing index hints are a bit misleading at times - there are also known bugs leading to SQL Server Mgmt Studio to continously recommendation indices that are already in place..... don't bet too much of your money on those index hints!