none
Consulta em relatório de bloqueio constante RRS feed

  • Pergunta

  • Pessoal, tenho a seguinte consulta abaixo aparecendo constantemente no meu relatório de locks diário, onde analisei o plano e as estatísticas e entendi não ser uma consulta tão ruim assim. Gostaria de ter a opiniões de vocês para tentar encontrar uma justificativa para ocorrência no meu relatório.

    set statistics io on
    set statistics time on
    SELECT rcl_txt, 
           smk_ld, 
           rcl_dthr, 
           rcl_conclusao 
    FROM   rcl, 
           smk 
    WHERE  ( rcl_tpcod = smk_tipo ) 
           AND ( rcl_cod = smk_cod ) 
           AND ( rcl_pac = 521621 ) 
           AND ( rcl_tpcod = 'S' ) 
           AND ( rcl_cod = 'T3T' ) 
           AND ( rcl_dthr = (SELECT Max (rcl_dthr) 
                             FROM   rcl 
                             WHERE  ( rcl_pac = 521621 ) 
                                    AND ( rcl_tpcod = 'S' ) 
                                    AND ( rcl_cod = 'T3T' ) 
                                    AND ( rcl_osm_serie = 118 ) 
                                    AND ( rcl_osm = 190767 ) 
                                    AND ( ( rcl_stat = 'X' 
                                            AND ( 'S' = 'S' 
                                                   OR rcl_ind_avaliado = 'S' ) ) 
                                           OR ( rcl_stat IN ( 'L', 'I', 'E' ) ) )) ) 
    set statistics io off
    set statistics time off

    (1 row(s) affected)
    Table 'RCL'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.
    Table 'SMK'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    (1 row(s) affected)
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 19 ms.
    
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    

    Agradeço desde já!

     
    domingo, 23 de setembro de 2018 15:03

Respostas

  • "mas não é possível ter processo com SPID iguais"
    É possível sim se você reaproveita conexões, controlando o uso das mesmas pelo connection pool do .Net.
    Ele abre uma conexão, executa um comando e não fecha a mesma. Apenas dá um "dispose" para pode executar outro comando na mesma conexão.
    Isso evita que tenhamos perda de performance com o "abre-executa-fecha" de conexões.

    Eu realmente não consigo ver o motivo de um SELECT bloquear o outro, sendo que esse outro ainda por cima tem o WITH (NOLOCK).

    Desculpe por não conseguir ajudar!

    Se conseguir descobrir, coloque a resposta aqui. Tenho certeza que ajudará mais gente!

    []'s

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quarta-feira, 26 de setembro de 2018 16:27
  • Jerfeson, boa tarde.

    Essa consulta está bloqueando as demais ou está sendo bloqueada por alguém? Desculpe se não entendi bem essa parte, mas num "relatório de locks" você pode estar olhando as queries afetadas ou as ofensoras.

    Se ela estiver sendo afetada, você está deixando o nível de isolamento padrão né? (Read committed)
    É que a query é muito muito tranquila (pelas estatísticas e plano de execução). 

    Caso seja ela a afetada, avalie: (1) utilizar o SNAPSHOT ISOLATION LEVEL para essa query e (2) avaliar a(s) querie(s) que está bloqueando essa daí.


    Espero ter ajudado.

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    terça-feira, 25 de setembro de 2018 15:51

Todas as Respostas

  • Deleted
    domingo, 23 de setembro de 2018 15:19
  • José, segue o plano XML:

    <?xml version="1.0" encoding="utf-16"?>
    <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.6560.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
      <BatchSequence>
        <Batch>
          <Statements>
            <StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00657266" StatementText="SELECT rcl_txt, &#xD;&#xA;       smk_ld, &#xD;&#xA;       rcl_dthr, &#xD;&#xA;       rcl_conclusao &#xD;&#xA;FROM   rcl, &#xD;&#xA;       smk &#xD;&#xA;WHERE  ( rcl_tpcod = smk_tipo ) &#xD;&#xA;       AND ( rcl_cod = smk_cod ) &#xD;&#xA;       AND ( rcl_pac = 521621 ) &#xD;&#xA;       AND ( rcl_tpcod = 'S' ) &#xD;&#xA;       AND ( rcl_cod = 'T3T' ) &#xD;&#xA;       AND ( rcl_dthr = (SELECT Max (r2.rcl_dthr) &#xD;&#xA;                         FROM   rcl R2&#xD;&#xA;                         WHERE  ( r2.RCL_PAC = 521621 ) &#xD;&#xA;                                AND ( r2.RCL_TPCOD = 'S' ) &#xD;&#xA;                                AND ( r2.RCL_COD = 'T3T' ) &#xD;&#xA;                                AND ( r2.RCL_OSM_SERIE = 118 ) &#xD;&#xA;                                AND ( r2.RCL_OSM = 190767 ) &#xD;&#xA;                                AND ( ( r2.RCL_STAT = 'X' &#xD;&#xA;                                        AND ( 'S' = 'S' &#xD;&#xA;                                               OR r2.RCL_IND_AVALIADO = 'S' ) ) &#xD;&#xA;                                       OR ( r2.RCL_STAT IN ( 'L', 'I', 'E' ) ) )) ) &#xD;" StatementType="SELECT" QueryHash="0x8E7CF416968BA65D" QueryPlanHash="0xC1F20087694B35A2">
              <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
              <QueryPlan DegreeOfParallelism="0" CachedPlanSize="40" CompileTime="15" CompileCPU="15" CompileMemory="1496">
                <RelOp AvgRowSize="86" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657266">
                  <OutputList>
                    <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_DTHR" />
                    <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_TXT" />
                    <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_CONCLUSAO" />
                    <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[SMK]" Column="SMK_LD" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2313">
                      <OutputList>
                        <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[SMK]" Column="SMK_LD" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[SMK]" Column="SMK_LD" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[SMART]" Schema="[dbo]" Table="[SMK]" Index="[SMK_PK]" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[SMK]" Column="SMK_TIPO" />
                                  <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[SMK]" Column="SMK_COD" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="'S'">
                                    <Const ConstValue="'S'" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="'T3T'">
                                    <Const ConstValue="'T3T'" />
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="84" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00328538">
                      <OutputList>
                        <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_DTHR" />
                        <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_TXT" />
                        <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_CONCLUSAO" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Top RowCount="false" IsPercent="false" WithTies="false">
                        <TopExpression>
                          <ScalarOperator ScalarString="(1)">
                            <Const ConstValue="(1)" />
                          </ScalarOperator>
                        </TopExpression>
                        <RelOp AvgRowSize="91" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16283000">
                          <OutputList>
                            <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_DTHR" />
                            <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_TXT" />
                            <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_CONCLUSAO" />
                            <ColumnReference Column="KeyCo5" />
                            <ColumnReference Column="KeyCo6" />
                            <ColumnReference Column="KeyCo11" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_DTHR" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_TXT" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_CONCLUSAO" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="KeyCo5" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="KeyCo6" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="KeyCo11" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[SMART]" Schema="[dbo]" Table="[RCL]" Index="[RCL_PK]" TableReferenceId="-1" IndexKind="Clustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_PAC" />
                                      <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_TPCOD" />
                                      <ColumnReference Database="[SMART]" Schema="[dbo]" Table="[RCL]" Column="RCL_COD" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="(521621)">
                                        <Const ConstValue="(521621)" />
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="'S'">
                                        <Const ConstValue="'S'" />
                                      </ScalarOperator>
                                      <ScalarOperator ScalarString="'T3T'">
                                        <Const ConstValue="'T3T'" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                            <Predicate>
                              <ScalarOperator ScalarString="[KeyCo5]=(118) AND [KeyCo6]=(190767) AND ([KeyCo11]='E' OR [KeyCo11]='I' OR [KeyCo11]='L' OR [KeyCo11]='X')">
                                <Logical Operation="AND">
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="KeyCo5" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(118)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="KeyCo6" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(190767)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Logical Operation="OR">
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="KeyCo11" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="'E'" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="KeyCo11" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="'I'" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="KeyCo11" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="'L'" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="KeyCo11" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="'X'" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                      </Top>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </QueryPlan>
            </StmtSimple>
          </Statements>
        </Batch>
      </BatchSequence>
    </ShowPlanXML>

    A subconsulta não deveria estar correlacionada com a consulta? É uma consulta oriunda do ERP. No nosso ponto de vista sim, mas não entendi o que o desenvolvimento estava pensando na hora que escreveu a consulta. 

    O código que transcreveu para este tópico é o código que está em produção ou é uma simplificação que você fez para analisar? Código apareceu em nosso relatório de locks que monitoramos o ambiente de produção. Creio que ele não seja um problema diante dos resultados apresentados no plano e pela quantidade de dados retornados, porém não entendo a insistência em surgi sempre em nossos relatórios e muitas vezes o ambiente apresenta lentidão com a presença do wait type LCK_.


    domingo, 23 de setembro de 2018 23:04
  • Deleted
    segunda-feira, 24 de setembro de 2018 18:43
  • (2) Justamente isso amigo José. Sem lógica terem escrito da forma anterior. Porém a mudança não surtiria efeitos diferentes.

    (3) Não é coincidência. Adicionei o R2 pra distinguir. 

    (4) Obtive no momento da execução no cache que deve estar armazenado em memória,


    terça-feira, 25 de setembro de 2018 13:26
  • Jerfeson, boa tarde.

    Essa consulta está bloqueando as demais ou está sendo bloqueada por alguém? Desculpe se não entendi bem essa parte, mas num "relatório de locks" você pode estar olhando as queries afetadas ou as ofensoras.

    Se ela estiver sendo afetada, você está deixando o nível de isolamento padrão né? (Read committed)
    É que a query é muito muito tranquila (pelas estatísticas e plano de execução). 

    Caso seja ela a afetada, avalie: (1) utilizar o SNAPSHOT ISOLATION LEVEL para essa query e (2) avaliar a(s) querie(s) que está bloqueando essa daí.


    Espero ter ajudado.

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    terça-feira, 25 de setembro de 2018 15:51
  • Deleted
    terça-feira, 25 de setembro de 2018 16:25
  • É sim José do ambiente de produção.

    Essa consulta está bloqueando as demais ou está sendo bloqueada por alguém?


    Se ela estiver sendo afetada, você está deixando o nível de isolamento padrão né? SIM.

     
    terça-feira, 25 de setembro de 2018 17:16
  • Não consigo ver a query inteira (a segunda no print), mas se ela está sendo bloqueada pela primeira query, você precisa mexer no isolation level dela (apesar de até onde eu vejo aqui, todos os FROM estão com WITH (NOLOCK) que faz leitura suja e não fica bloqueado.

    Qual é a query que você utiliza para obter esses dados? Por acaso é a "sp_WhoIsActive"?

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    terça-feira, 25 de setembro de 2018 18:20
  • Qual é a query que você utiliza para obter esses dados? Por acaso é a "sp_WhoIsActive"? SIM.

    Não temos como modificar a consulta do ERP.

    terça-feira, 25 de setembro de 2018 18:48
  • Desculpe se a pergunta for "besta" tá? 

    Não existe, no seu período de monitoramento, nenhuma outra query com o SPID = 171?
    Porque não consigo enxergar um motivo pra essa query bloquear a outra.

    De quanto em quanto tempo você deixa a chamada pra sp_whoIsActive?
    As vezes durante o monitoramento, mais de uma query veio no mesmo SPID e você pode estar olhando para a query errada.

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    terça-feira, 25 de setembro de 2018 18:50
  • Não existe, no seu período de monitoramento, nenhuma outra query com o SPID = 171? Não que tenhamos notado Mari, mas não é possível ter processo com SPID iguais.
    De quanto em quanto tempo você deixa a chamada pra sp_whoIsActive? Executa num intervalo de 5 minutos Mari.
    Só usarmos o profiler pra monitorar mais a fundo.
    quarta-feira, 26 de setembro de 2018 15:27
  • "mas não é possível ter processo com SPID iguais"
    É possível sim se você reaproveita conexões, controlando o uso das mesmas pelo connection pool do .Net.
    Ele abre uma conexão, executa um comando e não fecha a mesma. Apenas dá um "dispose" para pode executar outro comando na mesma conexão.
    Isso evita que tenhamos perda de performance com o "abre-executa-fecha" de conexões.

    Eu realmente não consigo ver o motivo de um SELECT bloquear o outro, sendo que esse outro ainda por cima tem o WITH (NOLOCK).

    Desculpe por não conseguir ajudar!

    Se conseguir descobrir, coloque a resposta aqui. Tenho certeza que ajudará mais gente!

    []'s

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quarta-feira, 26 de setembro de 2018 16:27
  • Ótimo Mariana, vamos seguindo investigando qual o problema. Realizamos algumas operações no ambiente e até o momento não ocasionou nenhum lock. Vamos observando.
    quarta-feira, 26 de setembro de 2018 20:43