Index Not Being Utilized in Plan by SQL Server

Question

This appears to be a simple query:

SELECT CUS_ID, CUS_LOCATION, CUS_COU_ABBREV FROM CUS WHERE CUS_ID > 1800000 AND CUS_LOCATION_ATTEMPTED = 'N'

There is an index covering all of these columns:

CREATE NONCLUSTERED INDEX [IX_CUS_59] ON [dbo].[CUSTOMER]
(
    [CUS_ID] ASC,
    [CUS_LOCATION_ATTEMPTED] ASC,
    [CUS_LOCATION] ASC,
    [CUS_COU_ABBREV] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Strangely, to me the accidental DBA just trying to keep the wheels on the bus, it's not being used in the query plan.

The database is choosing a different index:

CREATE NONCLUSTERED INDEX [IX_CUS_9] ON [dbo].[CUSTOMER]
(
    [CUS_LOCATION_ATTEMPTED] ASC
)
INCLUDE([CUS_LOCATION],[CUS_COU_ABBREV]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

It seems strange to me since IX_CUS_59 includes every column as a key column. Shouldn't that be better?

Looking at the Actual Exec Plan, the estimated CPU cost (and other costs) are lower for the second query so we know why SQL Server is picking this second index.

I assumed the first index should be preferred b/c it includes everything the query needs.

My question is how would the estimated CPU cost be lower for IX_CUS_9 over IX_CUS_59?

An assumption is that SQL Server already uses an index to retrieve CUS_ID so it doesn't need that in an index, however, still this second index includes the other three columns as key columns. Shouldn't that be better/ (more efficient) than 1 key and two included?

As requested, I've added the links to both query plans.

No index hint: https://www.brentozar.com/pastetheplan/?id=r1Xbebxut

With index hint for IX_CUS_59: https://www.brentozar.com/pastetheplan/?id=r1qmgbx_F

Answer 1

This looks like text book index selectivity, the golden rule of indexing states that “When you have multiple columns in your index, you can access using the column in the index so long as you use equality predicates on all prior columns in the index”.

Your multi column index starts with the column that you are using a range (non-equality) predicate so using it will mean reading the index for all rows which match the CUS_ID > 1800000 predicate, the statistics must suggest that this is more effort than reading the index pages from the other index which match the CUS_LOCATION_ATTEMPTED = 'N' Filter.

Looking at an execution plan forcing either index will show exactly what effort your query planner is expecting each will take but we know that it believes the multicolumn index is more expensive. In fact, now that you've added both query plans, we see exactly this. The plan with the index hint for IX_CUS_59 shows the storage engine had to read over a million rows to locate the ~13k rows needed to satisfy the query, whereas the plan without the index hint (using IX_CUS_9) only read 13,633 rows.

Looking at the properties for the index seek on IX_CUS_59 shows the following:

The index seek on IX_CUS_9 shows the following: