Indexed columns in SQL Server do not appear to work for basic queries according to execution plan

Question

Disclaimer: I'm not a DBA. I have picked up a few things from this board in the past that I'm building from.

I have a table of google analytics session start times. I have an index on each column. I want to filter for all sessions that were started between two dates. Screenshot below shows the query, and the index.

The query runs quickly but I do not believe it's using the index based on the Execution plan which both says that there's a missing index and shows a table scan rather than an index scan:

Why?

Is it because of something about the way I'm searching through the datetime? If instead of looking between dates, I set it equal to a date, the execution plan shows it using the index:

But it's not just this table or datetime. Here's a different table with an index on a varchar column:

And a simple query on this one also tells me I'm missing the index:

I'm stumped.

Answer 1

If you want SQL Server to use that index for that specific query, you need to include the session_id column. Otherwise, for each row it finds it will have to go do a key lookup into the base table. It will choose to do this for small result sets, but once you get above a certain number of rows ("the tipping point"), SQL Server thinks it's more efficient to just scan the whole base table.

You can use the INCLUDE clause of the CREATE INDEX syntax to accomplish this:

CREATE NONCLUSTERED INDEX IX_Start_Time
ON GoogleAnalytics.SessionStartTimes (session_start_time)
INCLUDE (session_id);

Answer 2

Let's break down what's going on, step-by-step:

• The compiler needs to get the value of session_id for each row. session_id is not in the index, so it needs to be looked up from the base table
• Your table has no clustered index, it is a heap table, and every row is located using its RID. A clustered index would need a key lookup instead
• So the compiler needs to decide whether to look up the index and then lookup the base table, or whether to just scan the base table from start to finish. It decides whether to do this based on estimated rowcounts from the statistics histogram.
• Your first query has more estimated rows than the second, for obvious reasons. Sp the compiler has decided that the cost of extra lookups are not worth it.

You have a number of solutions:

• Put session_id in the index. This does not have to be part of the key, it can be an INCLUDE column. I do not advise this as it leaves the table as a heap.
• Change the index to be clustered on session_start_time. This means all columns are included in the index. Remember, a clustered index is not extra to the table, it is the table. It is generally more efficient than a heap table anyway. Note that the PK does not need to be the clustering key.
• Create a clustered index on session_id. Then your non-clustered index will automatically include session_id (because that is the row locator). You may want to make this a PK, which I suspect should be the case anyway. Again, it doesn't have to be the PK.

Which option you choose will depend on what column is normally filtered or grouped. If this query was the only one done on the table then you would cluster on session_start_time.