Non clustered index not used

Question

I have the following tables:

• User that has a non clustered Index on UserName and Active columns.
• Notification that has a non clustered index on the UserId

I don't understand why in the first situation the non-clustered index on the username is used, but non in the second:

I expected for the second situation, that SQL will do a non clustered index seek on the User table, then a Clustered Index Seek on the Notification table.

Link to the execution plan: https://www.brentozar.com/pastetheplan/?id=HyG1Kwm1h

The IX_UserName index is defined as unique and it's also filtered on Active = 1.

Answer 1

You haven't provided a means to reproduce the issue, but my guess is the first plan is a trivial plan because the indexes are covering and obvious.

The second query requires the optimizer to make a cost-based choice. It assesses scanning the clustered index as the cheapest option, given the row goal introduced by the use of 'top' in your query.

If you force the nonclustered index to be used with an INDEX hint, you'll likely find the alternative plan has a higher estimated cost, probably due to a key lookup.

The optimizer suggests an indexing change:

/*
Missing Index Details from HyG1Kwm1h.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 93.5617%.
*/

/*
USE [WW7Client]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Notification] ([IsRead],[IsDeleted])
INCLUDE ([UserId])
GO
*/

You should evaluate that suggestion. It might not be the best change to make.