2

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 

enter image description here

I would like to know how I can improve this query

The estimated execution plan and the actual execution plan

4
  • @Erik, The WHERE operator is bringing the Activity ID. – FernandoSatonni Feb 23 at 22:25
  • @FernandoSatonni Can you please upload the execution plan on Paste The Plan so we can analyze the additional details not shown in a screenshot? We'll be able to advise better. – J.D. Feb 23 at 23:00
  • 1
    @FernandoSatonni So there is 100% a cardinality estimate issue going on. Check this screenshot from your actual execution plan out. Notice how the Actual Number of Rows is over 6 million but the Estimated Number of Rows is 1. SQL Server thinks that part of the query is only going to return 1 row and likely is severely under-allocating the resources needed to serve up your query's data. This is also happening on the other Index Seek operation as well, double whammy! :( I'm going to analyze your plan and query further to find the root cause. – J.D. Feb 24 at 0:25
  • Did you try update statistics object2 perhaps even with fullscan? – Charlieface Feb 25 at 1:05
2

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.

4
  • 1
    It worked perfectly, thank you very much for the clarification. – FernandoSatonni Feb 24 at 12:53
  • @FernandoSatonni Great! I'm very glad it helped! Just curious, it looked like originally you were seeing about 30 seconds runtime, what is it down to now? :) – J.D. Feb 24 at 13:09
  • 1
    Now a query runs in 2 seconds, very good ... – FernandoSatonni Feb 28 at 15:30
  • @FernandoSatonni Great! 🙂 – J.D. Feb 28 at 16:34

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.