Covering index chooses not to seek

jve 2018-05-07 14:33:57

I have a covering index which is being forced with a able hint.

I don't understand why on earth SQL Server won't seek my CONFIGID column. It seeks only first 3: DATAAREAID, ITEMID, SUMTTSLEVELMAX. And then it could use another 4 columns to seek, but it won't, resulting in high page read for that table. 2209 for particular query but should be much lower. And its not like index is very fragmented (less than 10%)

They have plain equal comparison with AND conditions. No functions. Image

I have attached SentryOne Plan Explorer Session File

jve 2018-05-08 05:54:30
Hmm, looks like I overlooked that SUMTTSLEVELMAX is a range scan. Is this the reason why subsequent columns cannot be used as predicate seek?
Hugo Kornelis 2018-05-09 10:24:02
Seems that you already answered your own question.

I did not manage to parse the entire query text – all the nested parentheses in the WHERE clause make it impossible to parse the exact predicate without putting in more time than I can spare right now. But here is a short generic explanation.

A composite index is similar to a phone book. They arenot the same in every country (and youngsters might not even know what they are since internet has mostly superceded the classic printed phone book), but I still like the analogy. So let's say you have a phone book for a state or province, with entries sorted by city, within city by last name, then by street and then by initials. If you need to find everyone living in New York, named Jefferson, and living in a street that starts with letters D – Z, you'd have to process just a few pages. Knowing they have initials D.M. will not change the pages to scan, though it will reduced the number of matching entries found. On the other hand, if you need to find everyone living on Jefferson Avenue in New York and with last name starting with D – Z, you'll need to scan a lot more pages; knowing the initials will again not help.

It's the same with indexes. So for your index, it can use the first two columns (DATAAREAID and ITEMID) to find the portion of the index that qualifies. But the third index column (SUMMITSLEVELMAX) is used in inequality so it can use this to drill down a subset of the entries (just as you'd start at the letter D and not at A in the phonebook examples). But all other columns can not be used to reduce the amount of rows the index seek has to scan.

Apart from inequality, you also need to think about OR. A simple OR (last name is Jefferson OR Johnson) can be done as two consecutive "normal" searches, or as a range scan (start at Jefferson, end at Johnson, discard everything that's not a perfect match). But for "last name is Jefferson OR initials are D.M.", the phonebook ordering would not help at all, and you'd have to scan all of New York.

Based on your query, if this index is intended for this specific query only I would put these columns first (not necessarily in that order): DATAAREID, ITEMID, CONFIGID, INVENTSIZEID, INVENTCLORID, INVENTLOCATIONID. After that it would be either COMMITTED and then one of the columns used for the inequality, or just one of the columns used for inequality. The remaining columns should be added as INCLUDE() columns and not as indexed columns.

If the index is supposed to help multiple queries (as I would normally advice), then you'll have to consider the effect on those queries as well before you decided to change the index.