SQL query does not use available index (SQL Server 2008)

Question

I have the following table in SQL Server 2008:

Session
(
sessionid varchar(10)
startdate dateteime
enddate dateteime
--rest of the fields go here
)

I have the following two nonclustered indexes created:

Inddex1: SessionID,startdate,enddate
Inddex2: startdate,enddate

I have the following query

select *
from session
where startdate>=@date1 and enddate <=@date2

on executing this query, both these idexes are not used. The query plan only shows the table scan.

Now I tried removing the index1 and executed the same SP Still index2 is not being used.

Any clues on how to make the SP to use index2? (No forced index use please.)

Answer 1

Do you perhaps have a clustered index on the SessionID column? In that case your indexes are basically identical as any nonclustered index will implicitly include the clustered key.

How many rows are in the table, and what is the cardinality/uniqueness of the values? If the table is small enough, a table scan may be more efficient than an index lookup + bookmarp lookups to retrieve the remaining columns.

Answer 2

The composite index won't be used because it can't be used to optimsise that query. You should create a separate index on each column.

Answer 3

Firstly, it's a good idea to have a primary key which you use as a unique, clustered index.

I'm not sure I get the point of those two composite index. Would you not be better with individual index on the dates?

Answer 4

You say the query processor isn't using one of your indexes, but you don't tell us what it is doing. I'll presume a table scan...?

You don't have a covering index for your query. Because you're doing "select *", the query processor knows that, at best, it can only get a "bookmark" from your index (either index would do), and then it will have to cross-reference that bookmark against the actual data pages in your table.

With that knowledge, the query processor will look at the amount of data in your table. If the amount of data there is small (for some definition of small) then it might decide that scanning the table is more efficient than an index seek following by a bookmark-lookup.

Consider changing the query and/or the index so that you only select fields that are entirely covered by the index. Then I would expect you to see index usage. But this isn't necessarily the right thing to do - creating a huge index for a seldom-used query might be wrong.

Answer 5

Any index with "startdate" as first column can help with the search for startdate>=@date1. Any index with "enddate" as first column can help with the search for "enddate <=@date2". But there is no index that can help with both.

And assuming there are almost no enddates with the same startdate, an index on (startdate,enddate) is no more useful than an index on (startdate).

So the table scan seems like a solid choice by Sql Server.

Even if you search for startdate>=@date1, it would still make sense to do a table scan. An index scan would give you a lot of references to the table that you'd have to resolve, which isn't worth it for a lot of data.

One query that should make use of the index is:

select * from session where startdate = @date1

Anyway, if you think you know better than the optimizer, you can enforce the use of an index like:

select *
from session with (index indexname)
where startdate>=@date1 and enddate <=@date2