Performance tanks after small change in search date predicate

Question

Can anyone help me understand what's happening here....

I'm trying to get consistent performance from a query being called from a third-party application. Changing the query itself isn't an option. The full query is shared at the bottom

(last minute addition to the post - see the note about the cardinality estimator in BOLD)

The query is pretty ugly and complex. The key part for my problem is caused by the date filter.

It runs in about 1 second like this:

AND [r1].[Setup_Date] BETWEEN '20250820' AND '20250910'

But nearly a minute with ~25% increase in the range, like this:

AND [r1].[Setup_Date] BETWEEN '20250820' AND '20250917'

This is the 'good' plan: https://www.brentozar.com/pastetheplan/?id=JzaR1eYhHI

This is the 'bad' plan: https://www.brentozar.com/pastetheplan/?id=vkcJKorUuw

Both 'REG0001' and 'REG0002' are heaps.

The bad plan is doing an index seek on REG0002 but SQL Server is estimating to do it only once and choosing a nested loop. However, it actually executes it several thousand times. Whereas the good plan is scanning the table once and performing a hash match. If I hint the query to force a hash match, it fixes the issue, but changing the code isn't an option in this case.

This index being seeked in the bad plan looks like this:

CREATE UNIQUE NONCLUSTERED INDEX [REG0002003] ON [dbo].[REG0002]
(
    [STATUS] ASC,
    [AGREEMENT_NO] ASC,
    [PAYMENT_NO] ASC
)

There is a statistic on SETUP_DATE that is up to date. You can see from the statistics that the values are roughly a week apart. The value highlighted shows where performance falls of a cliff.

I did consider forcing the 'good' plan with query store. However, the query isn't parameterized.

I'm really struggling to explain this behaviour and the importance of the small change in date range to the query plan. It's SQL Server 2022 (150). Same problem occurs in 2019 (150). HOWEVER - If I turn LEGACY_CARDINALITY_ESTIMATOR ON, this fixes it in 2019. I haven't tried it in SQL 2022 yet as it's a prod system.

I've provided everything I can think of but if you need more info, please just let me know.

SELECT 32954,
       274252,
       ROW_NUMBER() OVER (ORDER BY CASE
                                       WHEN "Permissions"."Donor" IS NULL THEN
                                           'Z'
                                       ELSE
                                           'Y'
                                   END,
                                   "Don0001"."Name",
                                   "Don0001"."PCSortReference",
                                   "Don0001"."Donor_No",
                                   "r1"."Agreement_No"
                         ),
       "r1"."recnum",
       CASE
           WHEN "Permissions"."Donor" IS NULL THEN
               'Z'
           ELSE
               'Y'
       END,
       "Don0001"."Name",
       "Dt0004"."Description",
       CASE "r1"."Status"
           WHEN '1' THEN
               'Current'
           WHEN '2' THEN
               'Complete'
           WHEN '3' THEN
       ('Terminated ' + CONVERT(CHAR(10), "r1"."Terminated_On", 103))
           ELSE
               ''
       END,
       ISNULL([r2a].[NoPayments], 0),
       ISNULL([r2d].[NoOverDue], 0),
       ISNULL([r2a].[Amount], 0),
       ISNULL([r2d].[OverDueTotal], 0),
       "r1"."Agreement_No",
       "r1"."Amount",
       "r1"."Start_Date",
       "r1"."Next_Date",
       '1753-01-01',
       "r1"."Term",
       CAST("r1"."Payments_Year" AS INT) * CAST("r1"."Term" AS INT)
FROM "Reg0001" AS "r1"
    LEFT JOIN "Don0001" AS "Permissions"
        ON "Permissions"."Donor" = "r1"."Donor"
           AND "Permissions"."Type" IN ( 'C', '1', 'E', 'F', 'T', 'I', '2', 'S', 'O', '3', '0', 'P', 'Z', 'V' )
    LEFT JOIN "Don0001"
        ON "Don0001"."Donor" = "r1"."Donor"
    LEFT JOIN "Dt0004"
        ON "Dt0004"."Payments_Year" = "r1"."Payments_Year"
    LEFT JOIN
    (
        SELECT MIN("r1"."recnum") AS [recnum],
               COUNT("r2"."amount") AS [NoPayments],
               SUM("r2"."amount") AS [Amount]
        FROM "Reg0002" AS "r2"
            INNER JOIN "Reg0001" AS "r1"
                ON "r2"."Agreement_No" = "r1"."Agreement_No"
        GROUP BY "r2"."Agreement_No"
    ) AS "r2a"
        ON "r2a"."recnum" = "r1"."recnum"
    LEFT JOIN
    (
        SELECT MIN("r1"."recnum") AS [recnum],
               COUNT("r2"."amount") AS [NoPaymentsMade],
               SUM("r2"."amount") AS [TotalMade]
        FROM "Reg0002" AS "r2"
            INNER JOIN "Reg0001" AS "r1"
                ON "r2"."Agreement_No" = "r1"."Agreement_No"
        WHERE [r2].[Status] = '2'
        GROUP BY "r2"."Agreement_No"
    ) AS "r2b"
        ON "r2b"."recnum" = "r1"."recnum"
    LEFT JOIN
    (
        SELECT MIN("r1"."recnum") AS [recnum],
               COUNT("r2"."amount") AS [ConsecPayments],
               SUM("r2"."amount") AS [ConsecTotal]
        FROM "Reg0002" AS "r2"
            LEFT JOIN
            (
                SELECT MIN("r2"."Agreement_No") AS [Agreement_No],
                       MAX("r2"."Payment_No") AS [MissedPayment]
                FROM "Reg0002" AS "r2"
                WHERE "r2"."Status" = 'M'
                GROUP BY "r2"."Agreement_No"
            ) AS "M"
                ON "r2"."Agreement_No" = [M].[Agreement_No]
            INNER JOIN "Reg0001" AS "r1"
                ON "r2"."Agreement_No" = "r1"."Agreement_No"
        WHERE "r2"."Status" = '2'
              AND "r2"."Payment_No" > (ISNULL([M].[MissedPayment], 0))
        GROUP BY "r2"."Agreement_No"
    ) AS "r2c"
        ON "r2c"."recnum" = "r1"."recnum"
    LEFT JOIN
    (
        SELECT MIN("r1"."recnum") AS [recnum],
               COUNT("r2"."amount") AS [NoOverDue],
               SUM("r2"."amount") AS [OverDueTotal]
        FROM "Reg0002" AS "r2"
            INNER JOIN "Reg0001" AS "r1"
                ON "r2"."Agreement_No" = "r1"."Agreement_No"
        WHERE "r2"."Date_Paid" = '1753-01-01'
              AND "r2"."Date_Due" <= '20251014'
              AND "r2"."Status" = '1'
        GROUP BY "r2"."Agreement_No"
    ) AS "r2d"
        ON "r2d"."recnum" = "r1"."recnum"
WHERE [r1].[Status] != '2'
      AND [r1].[Status] != '3'
      AND [Don0001].[Anonymised] <> 'Y'
AND [r1].[Setup_Date] BETWEEN '20250820' AND '20250917'

Ronaldo · Accepted Answer · 2025-10-21 17:34:16Z

If I hint the query to force a hash match, it fixes the issue, but changing the code isn't an option in this case.

I did consider forcing the 'good' plan with query store. However, the query isn't parameterized.

Maybe you could use both forcing the parameterization and using a plan guide for that query. See Specify Query Parameterization Behavior by Using Plan Guides:

When the PARAMETERIZATION database option is set to SIMPLE, you can specify that forced parameterization is attempted on a certain class of queries. You do this by creating a TEMPLATE plan guide on the parameterized form of the query, and specifying the PARAMETERIZATION FORCED query hint in the sp_create_plan_guide stored procedure. You can consider this kind of plan guide as a way to enable forced parameterization only on a certain class of queries, instead of all queries.
[...]
The following script can be used both to obtain the parameterized query and then create a plan guide on it:
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total   
      FROM Production.ProductModel AS pm   
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID   
      WHERE pi.ProductID = 101   
      GROUP BY pi.ProductID, pi.Quantity   
      HAVING sum(pi.Quantity) > 50',  
    @stmt OUTPUT,   
    @params OUTPUT;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';

Thanks, using a plan guide to force parameterization, and then using query store to force the legacy cardinality estimator has worked! Oddly, this appears to work to change it globally: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON However, I'm running SQL 2022 Compat 150, and the option has been removed in SSMS! — FrugalShaun
– FrugalShaun, Commented Oct 23 at 16:35

Charlieface · Accepted Answer · 2025-10-22 02:33:13Z

As you find very often with a parameter-sniffing problem, there isn't a good and a bad plan. There is a bad plan and a really bad plan. What you really need to do is improve the bad plan so it always gets chosen.

Both 'REG0001' and 'REG0002' are heaps.

That's the start of your issues. The rest of your tables are also heaps. And most of the joins end up with full table scans plus hash match, which is pushing it into parallelization as well. The few index seeks need bookmark lookups due to missing INCLUDE columns.

So you probably want to add better indexing. Given the index on Reg0002 is already unique, you should probably make it a clustered primary key.

ALTER TABLE dbo.REG0002
ADD CONSTRAINT REG0002003 PRIMARY KEY CLUSTERED
(
    [STATUS] ASC,
    [AGREEMENT_NO] ASC,
    [PAYMENT_NO] ASC
)

Also a filtered index

Reg0002 (Agreement_No, Date_Due) INCLUDE (recnum, Date_Paid, Date_Due, Status, amount)
  WHERE (Date_Paid = '17530101' AND Status = '1')

You should also add indexing on the other tables. Whether they should be clustered and PK, or non-clustered non-unique is dependent on the relationships. Either way the tables should have a clustered primary key.

Reg0001 (Agreement_No, recnum)

Reg0001 (Start_Date) INCLUDE (Agreement_No, DONOR, recnum, AMOUNT, TERM, PAYMENTS_YEAR, NEXT_DATE, STATUS, TERMINATED_ON)

Don0001 (Donor) INCLUDE (Type, Name, PCSortReference, Donor_No, Anonymised)

Dt0004 (Payments_Year) INCLUDE (Description)

Apart from applying a plan guide of PARAMETERIZATION FORCED, you can also apply USE HINT (N'FORCE_LEGACY_CARDINALITY_ESTIMATION'). And to get rid of the hash spill, you can use MIN_GRANT_PERCENT = 1 or some suitable grant percentage.

I know you said you can't modify the query, but given its very poor quality I would seriously consider decompiling the third-party code and modifying it. Note for example grouping and aggregating by the same column, which makes no sense. Also a left-join which is logically an inner-join. Some of the subqueries are entirely unused.

The query can be very much simplified, by using window functions, eliminating all of the subqueries. The duplicate join on the Permissions alias looks like it can be removed also.

The filtered index on WHERE (Date_Paid = '17530101' AND Date_Due <= '20251014' seems a bit silly, given that it's already October 21, 2025. I understand that's how the query is presented, but part of the problem is that it's executed with literal values and not parameters, so the date range is quite likely to change as time progresses ever forward towards the abyss. — Erik Darling
– Erik Darling, Commented Oct 22 at 2:18
You're right, missed that. The filter on Date_Paid can stay though, that's just MinDate. — Charlieface
– Charlieface, Commented Oct 22 at 2:33
Correct. The dates are passed in a literals from the application. By default, the 'to' date is way in the future. Also, you mention parameter sniffing but the performance is poor even if I add a recompile hint. It seems like there is no 'good' (or 'less bad' plan) once the dates pass that boundary. I'm going to look into the plan guide solution. Thanks. — FrugalShaun
– FrugalShaun, Commented Oct 23 at 8:26

Stack Exchange Network

Performance tanks after small change in search date predicate

2 Answers 2

Hot Network Questions

Performance tanks after small change in search date predicate

2 Answers 2

Related

Hot Network Questions