Dodgy T-SQL Query Execution driving me crazy

SQL Server 2019. Here's a link to a gist with the xml plan.

Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of the included statuses. When it finds a record that has one of the included statuses it tends to be much faster. I am guessing that is because the query stops once it finds 1 row that matches.

SELECT TOP 1 IDNum,
             FORMAT(Date, 'M/d/yy') AS theDate,
             Status,
             Rate
FROM   theDB
       INNER JOIN DomainTable
               ON theDB.IDNum = DomainTable.IDNum
WHERE  DomainIP = '127.0.0.1'
       AND status IN ( 'Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP',
                       'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z' )
ORDER  BY theDB.IDNum DESC

(DomainIP belongs to DomainTable the others belong to theDB)

In the execution plan the largest cost is the TOP N SORT at 33% There is a clustered Index seek at 29%
There is a key lookup on DomainTable that is using 29%
The Index seek for the IP on DomainTable is 9%

My questions are:

is there any way to get that TOP N to not be so heavy?

0.02 seconds isn't that slow but also this query is pretty light. So I would like to optimize it as much as possible.

There are only four entries in DomainTable where the IP is 127.0.0.1 so it is essentially taking 0.2 sec to decide which one of those to return and it turns out the answer is none of them because the status isn't right on any of them. Is there not a way to just create an index that keeps all of this information in RAM or something?

is there any way to get that TOP N to not be so heavy?

I think there's a little bit of a misunderstanding here about how execution plans work.

That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be. It's not updated at runtime, so even if the operators used very little resources, the estimated costs are still displayed as they were when the plan was created.

Looking at the runtime plan you provided, Sentry One Plan Explorer makes it a little easier to see that some of those "expensive" operators didn't run at all (they are "greyed out"):

I am having a heck of a time understanding why this query is taking 0.02 seconds to execute

I'm not sure how you're measuring the time here, but the execution plan indicates that the entire query took less than one milliseond to run, and took 9 milliseconds to compile. See the excerpts from the plan XML:

...



Hannah has some good suggestions on general improvements to the query, definitely check those out. But I thought it was worth pointing this out as well.

You might be able to eliminate the TopN Sort with an indexed view, but I'm not sure it's worth the overhead at this point.

StackExchange Database Administrators Q#257872, answer score: 15