We have a table called "MealEvent" which has an index on EventDate,ConsumerId and some other columns. I have a poorly performing query which has an ON clause like this (me2 is MealEvent):
ON me2.CONSUMER_ID=fe.CONSUMER_ID and me2.MEAL_EVENT_DATE between fe.START_DATE and fe.END_DATE
When I look at the execution plan, I see the ConsumerId in the Predicate as opposed to Seek Predicate. I am wondering why does SQL Server opt to not use it in the Seek Predicate? Can I force it to use it in the Seek Predicate?
Any advice is highly appreciated.
You need to flip the order of the key columns in the index. The server cannot use a range predicate and then an equality predicate, the equality must come first
It should be (CONSUMER_ID, MEAL_EVENT_DATE).
The reason is simple: When the server seeks the index for MEAL_EVENT_DATE, it does not have an exact key to look up, it wants a range of keys. It cannot then, within those keys, skip from one key to the next to get a single CONSUMER_ID.
But if CONSUMER_ID is first, then the server can seek directly to that key, then within that can look for the range of MEAL_EVENT_DATE.