Dodgy T-SQL Query Execution driving me crazy

Question

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?

Answer 1

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:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="9" CompileCPU="9" CompileMemory="736">
...
<QueryTimeStats CpuTime="0" ElapsedTime="0" />

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.

Answer 2

Try creating the following indexes (I'm using what I think is something similar to your naming convention for index names):

CREATE INDEX DomainTable$DomainIP$IDNum
ON dbo.DomainTable (DomainIP, IDNum DESC);

CREATE INDEX theDB$idx001
ON dbo.theDB (IDNum DESC)
INCLUDE (theDate, [Status], Rate);

The first index allows SQL Server to do a seek on DomainIP, which will be very quick, and it includes the IDNum column, in descending order, which will eliminate the key lookup operation from your query plan, and eliminate the required descending order sort.

The second index might also allow SQL Server to eliminate the sort - see if it helps or hinders the query execution. If you add a minimal, complete, and verifiable example to your question, I'd be able to test that for you.

Anyway, consider the following modifications to your query:

IF OBJECT_ID(N'tempdb..#statii', N'U') IS NOT NULL
BEGIN
    DROP TABLE #statii;
END
CREATE TABLE #statii 
(
    [status] varchar(20) NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO #statii ([status])
VALUES ('Active')
    , ('To ReActivate')
    , ('To Deactivate')
    , ('Deactivate ASAP')
    , ('SUSPENDED')
    , ('SUSPENDED X')
    , ('SUSPENDED Y')
    , ('SUSPENDED Z');

SELECT TOP 1 theDB.IDNum
    , CONVERT(varchar(30), theDB.theDate, 22)
    , theDB.[Status]
    , theDB.Rate 
FROM theDB 
    INNER JOIN DomainTable ON theDB.IDNum = DomainTable.IDNum 
    INNER JOIN #statii s ON theDB.[status] = s.[status]
WHERE DomainTable.DomainIP = '127.0.0.1' 
ORDER BY theDB.IDNum DESC;

A couple of things to note:

1. instead of using the WHERE ... IN (...) clause, I'm inserting those values into a temporary table and joining on them. You may not notice a huge increase in performance from this methodology, but as the list of items in the IN (...) clause gets bigger, the performance difference becomes more and more noticeable. You may want to revert that to the IN (...) clause if it turns out to hinder performance. I think it's worth testing.

2. Don't use FORMAT - it's slow. Use CONVERT or CAST where possible.

3. Use table aliases, as I've done for the #statii table.

4. Capitalize keywords, keep data types lower case.

5. Never ¹ use keywords as column names. I'm looking at you, status. That column should be named BlahStatus where Blah is the name of the table, or the kind of status, or something.

---

^{1 - ok, maybe never is a bit harsh. Almost never.}