MSSQL - query runs for too long when filtering after a certain date

Question

So I would like to understand a concept of MSSQL that is a bit interesting to me, but I didn't really find any answers online until now.

I have a query that runs on a 100mil+ rows table. Lets say that I have a couple rows that were created on '2024-04-14', and there are no rows older than that.

The query looks for entries with this filter:

CreatedAt < '2024-04-15'

The query runs fine and it will return the rows created on '2024-04-14'.

But if I do this:

CreatedAt < '2024-04-14'

The query will run until it either returns a timeout or it will take a very long time to return an empty result set, which is the expected result btw as we don't have rows older than '2024-04-15'.

Can you please tell me what concept of SQL or MSSQL is involved here and how I can avoid this? The thing is this query is used to retrieve data for arhivation, that data will then be deleted, it all happens in batches. We have a 150 days retention period so using that info we calculate a date that we use in the filter. After the arhivation job runs a few times and archives all the data found, the query will stop finding data older then the specified date, but it will continue to run the rest of day.

UPDATE 1

So I have come back with more details.

The table:

CREATE TABLE [dbo].[Notifications](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [AlertId] [int] NOT NULL,
  [ChannelId] [int] NOT NULL,
  [Status] [int] NOT NULL,
  [Identifier] [nvarchar](1024) NOT NULL,
  [CreatedAt] [datetimeoffset](7) NOT NULL,
  [CanBeRetried] [bit] NULL,
  [RetryCount] [int] NOT NULL,
  ...
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Notifications_CreatedAt] ON [dbo].[Notifications]
(
   [CreatedAt] ASC
)

CREATE NONCLUSTERED INDEX [IX_Notifications_AlertId_ChannelId_CreatedAt_Include_Identifier_Status_CanBeRetried_RetryCount] ON [dbo].[Notifications]
(
   [AlertId] ASC,
   [ChannelId] ASC,
   [CreatedAt] ASC
)
INCLUDE([Identifier],[Status],[CanBeRetried],[RetryCount]) 

CREATE NONCLUSTERED INDEX [IX_Notifications_AlertId_ChannelId_Identifier] ON [dbo].[Notifications]
(
   [AlertId] ASC,
   [ChannelId] ASC,
   [Identifier] ASC
)

CREATE NONCLUSTERED INDEX [IX_Notifications_CreatedAt_Status_Include_AlertId] ON [dbo].[Notifications]
(
   [CreatedAt] ASC,
   [Status] ASC
)
INCLUDE([AlertId]) 

Now, the scenarios + execution plans. For more context, I am selecting notifications which I want to archive, notifications are produced by an Alert and I want to exclude from the archivation process notifications that are produced by certain Alerts.

1. CreatedAt < '2024-04-14 09:00:00' AND [n].[AlertId] NOT IN ( '76', '126', '127', '128', '129')

https://www.brentozar.com/pastetheplan/?id=ByV4zrlaA

This one takes around 11 minutes to process. There are no notifications present other than the ones excluded, older than that date. So this will produce and emtpty result set. The optimiser chose an Clustered Index Scan.

2. CreatedAt < '2024-04-15 09:00:00' AND [n].[AlertId] NOT IN ( '76', '126', '127', '128', '129')

https://www.brentozar.com/pastetheplan/?id=SJldhHxa0

This one takes around 2 seconds to process. There are notifications present other than the ones excluded, older than that date. The optimiser chose, again, the same Clustered Index Scan.

3. CreatedAt < '2023-09-13 11:00:00' AND [n].[AlertId] NOT IN ( '76', '126', '127', '128', '129')

https://www.brentozar.com/pastetheplan/?id=SJkp2Hl6R

This one takes 1 second. There are no notifications whatsoever, older than that date. This one uses an Index Seek on the IX_Notifications_CreatedAt index.

I've also found this in a post: https://stackoverflow.com/questions/12846283/why-is-sql-server-not-using-index-for-very-similar-datetime-query

As the range grows (and hence number of lookups required) it estimates that it will be quicker just to scan the whole (covering) clustered index avoiding the lookups. Possibly incorrectly in your case. The point at which it switches from one plan to the other is known as the tipping point.

This would answer would have satisfied me if it was performing poorly in both scenario 1 and 2, but it only runs slow in scenario 1.

Answer 1

rows and goals

The problem you're hitting when data isn't there is somewhat(?) common. I've blogged about it in a post helpfully titled:

• SQL Server Query Performance When You Search For Rare Data Points

By using TOP in your query, you've set a row goal for the optimizer. Paul White blogged about those in a post helpfully titled:

• Inside the Optimizer: Row Goals In Depth

The short story is that even when your query asks for data that isn't there, SQL Server keeps looking for it, trying to satisfy the 2000 rows you've requested from it. After all, SQL Server operates under the assumption that you wouldn't waste its time looking for non-existent rows.

For the two fast queries, SQL Server finds 2000 rows rather quickly. The query plans posted don't match your table and index definitions, though. The Notifications table only has Id as the clustered index key, and the nonclustered index only has CreatedAt as a key column, but it's able to output the AlertId column and apply a predicate to it. Are you sure you've provided things as-is for all three query plans?

Anyway, when you ask for data that isn't there, SQL Server spends a whole lot more time trying to find those 2000 rows, which requires reading through the entire clustered index. Since you're on some damnable Microsoft cloud version of SQL Server, your storage is awful and you don't have near enough memory to keep the data you care about cached in memory. If it were cached in memory, you would have far fewer problems.

You end up waiting on this all this stuff:

    <WaitStats>
      <Wait WaitType="LATCH_EX" WaitTimeMs="3435155" WaitCount="13364" />
      <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="545181" WaitCount="33008" />
      <Wait WaitType="PREEMPTIVE_HTTP_REQUEST" WaitTimeMs="132837" WaitCount="57993" />
      <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="6302" WaitCount="6064599" />
      <Wait WaitType="HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO" WaitTimeMs="1387" WaitCount="91" />
      <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="350" WaitCount="287" />
      <Wait WaitType="PAGEIOLATCH_EX" WaitTimeMs="319" WaitCount="19" />
      <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="255" WaitCount="97" />
      <Wait WaitType="CXSYNC_PORT" WaitTimeMs="24" WaitCount="7" />
    </WaitStats>
    <QueryTimeStats CpuTime="70630" ElapsedTime="692778" />

Most insultingly, you spend 545 seconds waiting to read pages from disk into memory (PAGEIOLATCH_SH), and 132 seconds making http requests to that godawful cloud storage (PREEMPTIVE_HTTP_REQUEST).

The reason I asked about the table and index definitions earlier is because you normally only see SQL Server having a hard time choosing non-covering nonclustered indexes, which yours doesn't appear to be in the plans you've posted. There's no Key Lookup to get involved in costing decisions between clustered and nonclustered. If you can provide some clarification there, I can update my answer with resolutions.

Since:

removing the TOP from the query makes it run ok in the first scenario...

You may need to use this hint to get consistent performance: OPTION(USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));, or use an index hint to force the use of [IX_Notifications_CreatedAt_Status_Include_AlertId].