The Execution PlanSQL 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.

             FORMAT(Date, 'M/d/yy') AS theDate,
FROM   theDB
       INNER JOIN DomainTable
               ON theDB.IDNum = DomainTable.IDNum
WHERE  DomainIP = ''
       AND status IN ( 'Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP',
                       'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z' )

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.

  • I can't actually because pasting the XML right out of SSMS pastetheplan.com says it is invalid.. I pasted it as an image. – ZCT Jan 21 at 14:17
  • DomainIP belongs to DomainTable the others belong to theDB – ZCT Jan 21 at 14:26
  • as for removing the TOP N SORT... no, if you need the results sorted then it has to sort the results. The only way to get predictable results with a TOP is to use an ORDER BY, so you are stuck with that. Make sure you are filtering results as much as possible to limit the result set that has to be sorted and that's the best you can do. Make sure your tempdb is on fast disks and properly configured (multiple files and such) since a sort almost always spills to tempdb. – Jonathan Fite Jan 21 at 14:39
  • 2
    And use set statistics time on instead of looking at the query elapsed time. – David Browne - Microsoft Jan 21 at 14:43
  • 2
    also, it would be helpful if you added the T-SQL table definitions, along with all indexes present on those tables. Also, do you have auto update statistics enabled for the database (it is by default)? – Max Vernon Jan 21 at 15:41

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"):

enter image description here

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" />

Max 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.

| improve this answer | |

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);

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
    DROP TABLE #statii;
    [status] varchar(20) NOT NULL

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

    , CONVERT(varchar(30), theDB.theDate, 22)
    , theDB.[Status]
    , theDB.Rate 
    INNER JOIN DomainTable ON theDB.IDNum = DomainTable.IDNum 
    INNER JOIN #statii s ON theDB.[status] = s.[status]
WHERE DomainTable.DomainIP = '' 

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 1 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.

| improve this answer | |
  • 7
    True story: I've seen a table named dbo.tblTable ... At least they avoided the reserved keyword???? – AMtwo Jan 22 at 12:43
  • What if I manufacture tables and need a place to store the details of the tables that I build :P – Milney Jan 22 at 13:53
  • With all his server memory, would a table value parameter be even more performant? i.e. CREATE TABLE @statii (......) – HardCode Jan 22 at 20:33
  • @hardcore dba.stackexchange.com/questions/16385/… – Max Vernon Jan 22 at 23:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.