sql-tuningdatabase-tuningquery-tuning

Query Tuning and rewrite - SQL Server


Could you help to optimize the below query to perform better? Can I reduce the cost?

SELECT this_.id               AS id1_20_0_, 
       this_.version          AS version2_20_0_, 
       this_.domain           AS domain4_20_0_, 
       this_.createdate       AS createda5_20_0_, 
       this_.lastmodifydate   AS lastmodi6_20_0_, 
       this_.ownerid          AS ownerid7_20_0_, 
       this_.timeperiod       AS timeperi8_20_0_, 
       this_.type             AS type9_20_0_, 
       this_.capturesource    AS capture10_20_0_, 
       this_.value            AS value11_20_0_, 
       this_.siteid           AS siteid12_20_0_, 
       this_.lastmodifyuserid AS lastmod13_20_0_, 
       this_.classid          AS classId3_20_0_ 
FROM   dbo.pcwdepconstraints this_ 
WHERE  this_.classid = 65 
       AND this_.ownerid = 200000000001 
       AND ( this_.capturesource IS NULL 
              OR this_.capturesource IN ( 1073741826, 1073741827, 0, 1, 2 ) ) 

Execution Plan

I have recreated the ix2_pcwdepcons by below columns, but still there is no change in the execution plan and its cost.

( this_.id , 
       this_.version   , 
       this_.domain ,
       this_.createdate  ,
       this_.lastmodifydate,

       this_.timeperiod  ,
       this_.type  , 
        this_.value   ,       
       this_.siteid       
       this_.lastmodifyuserid )

Solution

  • The execution plan shows that you have a seek (to find the basic information), but then also a "key lookup", which is a rather expensive operation which you should try to avoid if you can.

    Looking at the query, I see you're using classid, ownerid and capturesource in your WHERE clause - so those need to be in an index. If you can, you can also "include" all other columns in your query's SELECT clause into that index, to avoid the "key lookup".

    So I'd say try an index like this:

    CREATE NONCLUSTERED INDEX IX_Test
    ON dbo.pcwdepconstraints_bkp (classid, ownerid, capturesource)
    INCLUDE (id, version, domain, createdate, lastmodifydate, lastmodifyuserid,
             timeperiod, type, value, siteid)
    

    Including that many columns only makes sense, if this is not the whole list of columns from that table. These columns will take up space, so you're trading off increased query performance vs. more disk space needed. But if your table as a lot more columns and your query selects "only" those 13 (since it really needs those), then the INCLUDE can be really helpful to speed things up.