SQL Server 2017 not using index

Question

I have a table and a (non-pk) index

CREATE TABLE [UniqueIdentifier]
(
    [Id] [varchar](100) NOT NULL,
    [ShortUI] [varchar](50) NULL,
    [StateId] [tinyint] NOT NULL,
    [TypeId] [tinyint] NOT NULL,
    [GenerationRequestedOn] [datetimeoffset](7) NULL,
    [AnticipatedUsageOn] [datetimeoffset](7) NULL,
    [IssuerNotificationTime] [datetimeoffset](7) NULL,
    [ParentId] [varchar](100) NULL,
    [ProductItemId] [int] NULL,
    [RecalledByCode] [varchar](100) NULL,
    [FacilityId] [varchar](300) NULL,
    [PendingArrival] [bit] NULL,

    CONSTRAINT [PK_UniqueIdentifier] 
        PRIMARY KEY NONCLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_IssuerNotificationTime] 
ON [UniqueIdentifier]([IssuerNotificationTime] 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]

I have a select

SELECT
    * 
FROM
    [UniqueIdentifier] u 
WHERE 
    u.IssuerNotificationTime BETWEEN DATEADD(DAY, -7, DATEADD(YEAR, -5, GETDATE())) 
                                 AND DATEADD(YEAR, -5, GETDATE()) ;

This query should return "only" 972000 rows out of 141000000, but it reads everything!

If I do the explain plan, I see SQL Server does a full table scan, and doesn't use the index at all. Why why why?

Index properties: idx_issuerNotificationTime has PageFullness 95% and fragmentation 95%.

I also tried adding ORDER BY IssuerNotificationTime to the query. This adds a sort step to the execution plan, but index is still not used.

How can I make this use the index? Or, at least, how I can help SQL Server not need to read the entire table to get these rows?

Please share the execution plan via brentozar.com/pastetheplan — Charlieface
– Charlieface, Commented Nov 16, 2024 at 18:41
Do you need SELECT * can you limit the columns to a smaller set? Then add those columns as INCLUDE to the non-clustered index — Charlieface
– Charlieface, Commented Nov 16, 2024 at 18:43

Joel Coehoorn · Accepted Answer · 2024-11-15 17:32:11Z

1

Remember that SQL Server reads data a page at a time, rather than a row at a time. So an index scan (can't use a simple seek, because it's an inequality check) that results in 972000 rows out of 141000000 may only need to read a few pages from the index, but then to go back and get the actual data it's still going to need to read nearly (not exactly) 972000 whole pages. The exact number is smaller, because selected rows will frequently share pages, but we're still reading full pages, with potentially a lot of waste.

As a result, depending on things like the statistics, page size and fill for the raw data (not just index), how many pages are optimistically in memory vs still on disk, etc, the SQL Server query engine may determine using the index is more trouble than it's worth.

edited Nov 15, 2024 at 17:32

answered Nov 15, 2024 at 17:25

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

"can't use a simple seek, because it's an inequality check" a BETWEEN is not an inequality, it's a range and it most definitely can be done with a range seek. And doesn't answer why you need to go back to get "actual data", which is almost certainly because the index doesn't cover the query.

Collectives™ on Stack Overflow

SQL Server 2017 not using index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related