0

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:

Non clustered index being used

Non clustered index not being used

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.

2
  • 1
    Aside from providing the actual execution plans, as Dan mentioned, please also provide the Tables and Indexes definitions.
    – J.D.
    Mar 6, 2023 at 12:41
  • A dbfiddle is usually the best option with sample data. Mar 6, 2023 at 12:46

1 Answer 1

3

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.

1
  • I saw the suggestion and it's not the worst one, but we need to factor in the cost of maintenance of a larger index, if we include the 2 columns to an existing non clustered index, since we could deal with millions of notifications. I was just curious why the other index wasn't used, but probably it's SQL deciding that it's better to do a clustered index scan as you suggested. Mar 6, 2023 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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