Performance issues that seem to be server dependent

Question

I'm dealing with a performance issue that seems to be server dependent. The server in question is version 15.0.4316.3 (X64). Here's the code below.

SELECT b1.ID
    ,ParentID = (
        SELECT TOP 1 b2.ID
        FROM stgBOMv3 b2
        WHERE b2.id <= b1.id
            AND b2.BOMLevel <= 0
        ORDER BY ID DESC
        )
FROM stgBOMv3 b1
WHERE b1.BOMLevel = 1

The query runs fine on my laptop on the exact same dataset, version 15.0.2000.5 (X64). Specifically, it takes about 1 minute to run on my laptop and almost 15 hours on the server. The table has over 6 million rows and appropriate indexes on ID and BOMLevel.

Are there any obvious things that I'm missing that could be causing my query to take 900 times longer on this server?

---

Edited to add some extra details as requested. As to the purpose of this query, this query has been fit for purpose for years. It's only after migrating to a new server that the query performance tanked.

Table creation:

CREATE TABLE dbo.stgBOMv3 (
    id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ,ParentID INT NULL FOREIGN KEY REFERENCES stgBOMv3(id)
    ,TopID INT NULL FOREIGN KEY REFERENCES stgBOMv3(id)
    ,BOMLevel INT NULL
    ,ItemNumber NVARCHAR(255) NULL --and other columns not included
    )
GO

CREATE NONCLUSTERED INDEX [IX_stgBOMv3_BOMLevel]
ON [dbo].[stgBOMv3] ([BOMLevel])
GO

Execution plans:

• Laptop, full dataset, actual execution plan
• Server, full dataset, estimated execution plan
• Laptop, top 10, actual execution plan
• Server, top 10, actual execution plan

Full dataset actual execution plan for the server is not included, as I'd rather not spend 15 hours generating it

Answer 1

The bad plan is doing a backward ordered scan on the inside of the nested loops and will just evaluate the b2.id < b1.id AND b2.BOMLevel <= 0 predicate against every row until it finds the first one matching the predicate.

This plan is catastrophically bad for small b1.id as it needs to scan the whole table before finding the first row matching the predicate.

I assume that it is undercosting the scan because of the TOP 1. See How (and why) does TOP impact an execution plan?

The good plan does seek the b2.id < b1.id part and then does a backward direction scan from that point in the index until it finds the first row matching the b2.BOMLevel <= 0 predicate.

The seek looks objectively better in this case so you could try just forcing that. I also changed the <= to < because we know ID is unique and that a row can't meet both of BOMLevel <= 0 and BOMLevel = 1.

SELECT b1.ID
    ,ParentID = (
        SELECT TOP 1 b2.ID
        FROM stgBOMv3 b2 WITH (FORCESEEK)
        WHERE b2.id < b1.id
            AND b2.BOMLevel <= 0
        ORDER BY ID DESC
        )
FROM stgBOMv3 b1
WHERE b1.BOMLevel = 1

But it looks like the query is returning all rows where BOMLevel = 1 and the ID value from the latest preceding row where BOMLevel <= 0 aliased as ParentID.

TBH this does seem quite confusing as you do have a separate column in the table also called ParentID but you state that it has been "fit for purpose for years".

So I'd be hopeful that the below out performs the current "good" plan

WITH b1 AS
(
SELECT ID, 
       BOMLevel,
       ParentID  = MAX(CASE WHEN BOMLevel <= 0 THEN ID END) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING)
FROM stgBOMv3
)
SELECT ID, 
       ParentID  
FROM b1
WHERE b1.BOMLevel = 1