7

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.

Query text and index properties

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:

execution

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:

Using index

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

metadata index

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

missing md index

I'm stumped.

10

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);
0
7

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.

6
  • session_id should be unique in that table. I'm nervous to make it formally a PK because a) my ignorance and b) the process I have for updating this table isn't super clean and I'd be worried about having a PK causing problems. Maybe I shouldn't be. This table will probably almost always be queried like this. I'd be doing ad hoc analysis for a certain period of time and I'd come here to grab all sessions for that period, and then go grab the data on those sessions from other tables. Mar 18 at 20:36
  • 3
    OK so make a non-unique clustered index. Clustering does not by itself imply unique (although it is often the case) Mar 18 at 20:41
  • Thank you for these suggestions. I tried the solution above first because it was a bit simpler for me and for the simple queries it worked well. I may re-visit what you've said as I start joining these tables together. Mar 18 at 21:29
  • @Charlieface +1, this is a quality answer with a ton of very good information. The only minor thing I'd say is your first solution is a good one in addition to the second one. So I wouldn't necessarily advise against it, I'd just make it clear to users that both that fix + adding a clustered index to the Table is a good move to make too.
    – J.D.
    Mar 19 at 1:29
  • @J.D. Thank you. I don't think so, they are mutually exclusive (even though it doesn't look it at first sight). 1 and 2 are mutually excl because they duplicate the same index key and include column (I have no info on existence of other columns). 1 and 3 are mutually exclusive because, as I say, clustering implies an include on the NCI anyway, so it's not necessary to actually add it to the NCI. Mar 19 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.