Slow query SQL Server

Question

I have a query that is taking too long to run.

I executed set statistics io on and set statistics time on, and I noticed that Scan count and logical reads are very high

Table 'TABLE_01'. Scan count 6497248, logical reads 26065220, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server parse and compile time: CPU time = 20 ms, elapsed time = 20 ms.

(138 row(s) affected)

SQL Server Execution Times: CPU time = 28969 ms, elapsed time = 30559 ms.

Here's the query:

SELECT Object1.Column1 AS Column2,
       Object1.Column3          AS Column4,
       Object1.Column5,
       Object1.Column6,
       Object1.Column7             AS Column8,
       Object2.Column9                      AS Column10,
       Cast(Object2.Column11 AS VARCHAR)  AS Column12,
       Object2.Column13              AS Column14,
       Object2.Column15                     AS Column16,
       Object2.Column17                   AS Column18,
       Object2.Column19                     AS Column20,
       Object3.Column21                                  AS Column22,
       Object3.Column5                                   AS Column23,
       Object2.Column24              AS Column25,
       Object4.Column21                                 AS Column26,
       Object4.Column5                                  AS Column27,
       Object5.Column21                            AS Column28,
       Object5.Column29                        AS
       Column30,
       Object6.Column31                  AS Column32
FROM   Object1
       INNER JOIN (Object2
                   LEFT JOIN Object7 Object3
                          ON Object2.Column19 = Object3.Column19
                   INNER JOIN Object7 Object4
                           ON Object2.Column24 = Object4.Column19
                   INNER JOIN Object5
                           ON Object2.Column13 = Object5.Column15
                   INNER JOIN Object6
                           ON Object2.Column9 = Object6.Column9)
               ON Object1.Column3 = Object2.Column3
WHERE  Object1.Column3 IN (
   43796020, 43795933, 43795931, 43795681,
       43795672, 43793093, 43793090, 43793085,
       43790358, 43790342, 43789167, 43789155,
       43789154, 43788970, 43788928, 43788924,
       43788776, 43788774, 43788712, 43788699,
       43788674, 43788673, 43788626, 43788625,
       43788227, 43788219, 43787557, 43787530,
       43787088, 43787075, 43787063, 43787041,
       43787002, 43786368, 43786351, 43786244,
       43786243, 43786223, 43786222, 43786151,
       43417954, 43417953, 43417929, 43417294,
       43417293, 43417286, 43416500, 43416334,
       43416304, 43416288, 43416089, 43416034,
       43416016, 43416015, 43416014, 43416013,
       43416012, 43416011, 43416010, 43416009,
       43416008, 43416007, 43415753, 43401569,
       43400701, 41524028, 41316604, 41311555,
       41309174, 41308146, 41308136, 41286794,
       41281375, 41281374, 41281294, 41263751,
       41263749, 40960597, 38574969, 38133930,
       38133868, 38133516, 38132482, 37630401,
       37630400, 37630399, 37630398, 37630397,
       37630396, 37630395, 37629851, 37629846,
       37301414, 37298436, 37298433, 37298431,
       37298429, 37298427, 37298424, 37298398 
       )
ORDER  BY Object1.Column1 ASC 

I would like to know how I can improve this query

The estimated execution plan and the actual execution plan

Answer 1

Hmm 🤔 so as I mentioned in the comments, your actual execution plan reveals there is a severe cardinality estimate issue (screenshot of operation details for reference) on the Clustered Index Seek operation occuring on the atividade table. I'm not seeing much else in the execution plan or your query that jumps out at me explaining why you might be experiencing this type of issue other than the predicate in your WHERE clause being a decent size in the IN list, which essentially is syntactical sugar for a bunch of ORs. This also happens to be what the seek predicates are on for the aforementioned Clustered Index Seek operation, so it's a little suspect.

Unfortunately I think this one might require a little trial and error of query re-writing to see what helps the optimizer in generating a better plan without a cardinality estimate issue. Could you please try re-writing your query like this logical equivalent query (if possible) and re-running it to see if there's any performance improvement?

SELECT 
    id_atributo_andamento AS idatributoandamento,
    id_atividade AS idatividade,
    nome,
    valor,
    id_origem AS idorigem
INTO #atributo_andamento
FROM atributo_andamento
WHERE id_atividade IN 
(
   43796020, 43795933, 43795931, 43795681,
   43795672, 43793093, 43793090, 43793085,
   43790358, 43790342, 43789167, 43789155,
   43789154, 43788970, 43788928, 43788924,
   43788776, 43788774, 43788712, 43788699,
   43788674, 43788673, 43788626, 43788625,
   43788227, 43788219, 43787557, 43787530,
   43787088, 43787075, 43787063, 43787041,
   43787002, 43786368, 43786351, 43786244,
   43786243, 43786223, 43786222, 43786151,
   43417954, 43417953, 43417929, 43417294,
   43417293, 43417286, 43416500, 43416334,
   43416304, 43416288, 43416089, 43416034,
   43416016, 43416015, 43416014, 43416013,
   43416012, 43416011, 43416010, 43416009,
   43416008, 43416007, 43415753, 43401569,
   43400701, 41524028, 41316604, 41311555,
   41309174, 41308146, 41308136, 41286794,
   41281375, 41281374, 41281294, 41263751,
   41263749, 40960597, 38574969, 38133930,
   38133868, 38133516, 38132482, 37630401,
   37630400, 37630399, 37630398, 37630397,
   37630396, 37630395, 37629851, 37629846,
   37301414, 37298436, 37298433, 37298431,
   37298429, 37298427, 37298424, 37298398 
)

CREATE CLUSTERED INDEX IX_Temp_atributo_andamento_idatividade ON #atributo_andamento (idatividade)
CREATE NONCLUSTERED INDEX IX_Temp_atributo_andamento_idatributoandamento ON #atributo_andamento (idatributoandamento);

SELECT 
    atributo_andamento.idatributoandamento,
    atributo_andamento.idatividade,
    atributo_andamento.nome,
    atributo_andamento.valor,
    atributo_andamento.idorigem,
    atividade.id_tarefa AS idtarefaatividade,
    Cast(atividade.id_protocolo AS VARCHAR)  AS idprotocoloatividade,
    atividade.id_unidade_origem AS idunidadeorigematividade,
    atividade.id_unidade AS idunidadeatividade,
    atividade.dth_abertura AS aberturaatividade,
    atividade.id_usuario AS idusuarioatividade,
    u.sigla AS siglausuarioatividade,
    u.nome AS nomeusuarioatividade,
    atividade.id_usuario_origem AS idusuarioorigematividade,
    uo.sigla AS siglausuarioorigematividade,
    uo.nome AS nomeusuarioorigematividade,
    unidade.sigla AS siglaunidadeorigematividade,
    unidade.descricao AS descricaounidadeorigemativi001,
    tarefa.id_tarefa_modulo AS idtarefamodulotarefa
FROM #atributo_andamento AS atributo_andamento
   INNER JOIN atividade
               LEFT JOIN usuario u
                      ON atividade.id_usuario = u.id_usuario
               INNER JOIN usuario uo
                       ON atividade.id_usuario_origem = uo.id_usuario
               INNER JOIN unidade
                       ON atividade.id_unidade_origem = unidade.id_unidade
               INNER JOIN tarefa
                       ON atividade.id_tarefa = tarefa.id_tarefa
           ON atributo_andamento.idatividade = atividade.id_atividade
ORDER  BY atributo_andamento.idatributoandamento ASC

The above query breaks out the logic of the filtering on the id_atividade field for the atributo_andamento table and inserts the results into a temp table first. Then it creates an index on the idatividade field that is used in the INNER JOIN predicate to the atividade table later on. I also am testing creating an index on idatributoandamento which is what is used in the ORDER BY clause at the end of your query. Not sure if the query optimizer will use both indexes, but it's worth trying as a start.

Please run the above and let me know how it turns out. We can try adjusting a couple more things and still end up with a logically equivalent query, to see if we can fix the cardinality estimate issue, and worst case we may need to recalculate the statistics or possibly use a query hint.