Início / dba / Perguntas / 224036
Accepted
Erik
Asked: 2018-12-04 11:01:49 +0800 CST

Eliminando um impasse intra-consulta, posso evitar restringir o paralelismo?

  • 772

Eu tenho uma instrução de atualização que está gerando impasse de paralelismo intra-consulta. Com base neste link , acredito que minhas duas opções para resolver esse problema são forçar o SQL Server a evitar o paralelismo por meio de uma OPTION (MAXDOP 1)dica de consulta ou adicionar um índice para reduzir o custo para que o SQL Server decida por conta própria não usar um plano paralelo.

Só para ver o impacto, configurei os graus máximos de paralelismo para 1 e dobrou o tempo de execução de 0,5 segundos para 1 segundo. Como essa consulta é executada com frequência, não me sinto confortável em degradar o desempenho já ruim. Até agora, não consegui melhorar significativamente o desempenho da instrução de atualização para remover o paralelismo. A consulta está abaixo e aqui está um link para o plano de execução .

DECLARE @calllist_id int = 1;
DECLARE @customer_id int = NULL;

WITH ToUpdate AS 
(
    SELECT 
        CLQ.CallList_Queue_ID
        , newLastOpportunityCreateDate = MAX(O.CreateDate)
        , newLastOpportunity_ID = MAX(CLQO.Opportunity_ID)
    FROM tbl_CallList_Queue CLQ
        INNER JOIN tbl_CallList_Queue_Opportunity CLQO ON CLQ.CallList_Queue_ID = CLQO.CallList_Queue_ID
        INNER JOIN tbl_Opportunity O ON CLQO.Opportunity_ID = O.Opportunity_ID   
    WHERE 
        CLQ.CallList_ID = @calllist_id
        AND
        (
             @customer_id IS NULL
             OR 
             CLQ.Customer_ID = @customer_id
        )
    GROUP BY
        CLQ.CallList_Queue_ID
        , CLQ.LastOpportunityCreateDate
        , CLQ.LastOpportunity_ID
    HAVING
    (
        CLQ.LastOpportunityCreateDate IS NULL
        OR
        CLQ.LastOpportunityCreateDate < MAX(O.CreateDate)
        OR
        CLQ.LastOpportunity_ID IS NULL
        OR
        CLQ.LastOpportunity_ID < MAX(CLQO.Opportunity_ID)
    )
)
UPDATE CLQ
SET 
    LastOpportunityCreateDate = TU.newLastOpportunityCreateDate
    , LastOpportunity_ID = TU.newLastOpportunity_ID
FROM tbl_CallList_Queue CLQ 
    INNER JOIN ToUpdate TU on CLQ.CallList_Queue_ID = TU.CallList_Queue_ID;

Como você verá no plano, aproximadamente 50% do custo estimado está na atualização das colunas incluídas em um índice não clusterizado. Esse é o melhor lugar para considerar a otimização, já que não tive muita sorte em otimizar em outro lugar? Também há um gatilho que registra as alterações na tabela que não incluí no plano que carreguei. Não acho que o gatilho esteja causando problemas significativos de desempenho, mas está presente.

Aqui está o relatório de impasse obtido do evento estendido de integridade do sistema. Como você pode ver, eu embelezei o SQL um pouco com o que mostrei acima. As mudanças foram apenas para melhorar os nomes dos alias e remover as WITH(NOLOCK)dicas de consulta onipresentes que infelizmente fazem parte do legado histórico dessa base de código.

<event name="xml_deadlock_report" package="sqlserver" timestamp="2018-12-03T20:25:08.807Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process3b5d29468" />
        </victim-list>
        <process-list>
          <process id="process3b5d29468" taskpriority="0" logused="0" waitresource="PAGE: 10:1:18686 " waittime="4963" ownerId="3780998682" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.703" XDES="0x3cb856fb0" lockMode="U" schedulerid="2" kpid="8340" status="suspended" spid="352" sbid="0" ecid="18" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process3c1ea6ca8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:127895 " waittime="3736" ownerId="3780998682" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.703" XDES="0x1b6c813050" lockMode="U" schedulerid="5" kpid="9608" status="suspended" spid="352" sbid="0" ecid="21" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process23d219468" taskpriority="0" logused="0" waitresource="PAGE: 10:1:90295 " waittime="4970" ownerId="3780999522" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.733" XDES="0x2a4ece8040" lockMode="U" schedulerid="10" kpid="4084" status="suspended" spid="356" sbid="0" ecid="17" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.740" lastbatchcompleted="2018-12-03T13:25:03.740" lastattention="1900-01-01T00:00:00.740" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780999522" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process2a1982d848" taskpriority="0" logused="0" waitresource="PAGE: 10:1:18686 " waittime="4970" ownerId="3780999522" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.733" XDES="0x1bd782e040" lockMode="U" schedulerid="5" kpid="8040" status="suspended" spid="356" sbid="0" ecid="18" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.740" lastbatchcompleted="2018-12-03T13:25:03.740" lastattention="1900-01-01T00:00:00.740" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780999522" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process11628a0ca8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:127895 " waittime="4966" ownerId="3780999522" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.733" XDES="0x28b357ac90" lockMode="U" schedulerid="7" kpid="6404" status="suspended" spid="356" sbid="0" ecid="24" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.740" lastbatchcompleted="2018-12-03T13:25:03.740" lastattention="1900-01-01T00:00:00.740" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780999522" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process3bd04b088" taskpriority="0" logused="0" waitresource="PAGE: 10:1:90295 " waittime="3736" ownerId="3780998682" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.703" XDES="0x2391ef5ca0" lockMode="U" schedulerid="8" kpid="8168" status="suspended" spid="352" sbid="0" ecid="23" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process1f85527c28" taskpriority="0" logused="10000" waittime="5003" schedulerid="8" kpid="7980" status="suspended" spid="352" sbid="0" ecid="25" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
          <process id="process3b3b22ca8" taskpriority="0" logused="10000" waittime="5057" schedulerid="7" kpid="5344" status="suspended" spid="352" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" loginname="NGIC\AGTC-APP-HCI-Prod" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
       (
              SELECT 
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              , newLastOpportunityCreateDate = Max(c.CreateDate)
              , newLastOpportunity_ID = Max(b.Opportunity_ID)
              FROM tbl_CallList_Queue a WITH(NOLOCK)
              INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
              ON a.CallList_Queue_ID = b.CallList_Queue_ID
              INNER JOIN tbl_Opportunity c WITH(NOLOCK)
              ON b.Opportunity_ID = c.Opportunity_ID   
              WHERE 
              a.CallList_ID = @tmpcalllist_id
              AND
              (
                     @tmpcustomer_id IS NULL
                     OR 
                     a.Customer_ID = @tmpcustomer_id
              )
              GROUP BY
              a.CallList_Queue_ID
              , a.LastOpportunityCreateDate
              , a.LastOpportunity_ID
              HAVING    </frame>
              <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <pagelock fileid="1" pageid="18686" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock34211ea00" mode="U" associatedObjectId="72057605387976704">
            <owner-list>
              <owner id="process2a1982d848" mode="U" requestType="wait" />
            </owner-list>
            <waiter-list>
              <waiter id="process3b5d29468" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="127895" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock111730a200" mode="U" associatedObjectId="72057605387976704">
            <owner-list>
              <owner id="process11628a0ca8" mode="U" requestType="wait" />
            </owner-list>
            <waiter-list>
              <waiter id="process3c1ea6ca8" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="90295" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock281980c00" mode="U" associatedObjectId="72057605387976704">
            <owner-list>
              <owner id="process3b3b22ca8" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process23d219468" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="18686" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock34211ea00" mode="U" associatedObjectId="72057605387976704">
            <owner-list>
              <owner id="process3b3b22ca8" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process2a1982d848" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="127895" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock111730a200" mode="U" associatedObjectId="72057605387976704">
            <owner-list>
              <owner id="process3b3b22ca8" mode="U" />
            </owner-list>
            <waiter-list>
              <waiter id="process11628a0ca8" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="90295" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock281980c00" mode="U" associatedObjectId="72057605387976704">
            <owner-list>
              <owner id="process23d219468" mode="U" requestType="wait" />
            </owner-list>
            <waiter-list>
              <waiter id="process3bd04b088" mode="U" requestType="wait" />
            </waiter-list>
          </pagelock>
          <exchangeEvent id="Port2832c76700" WaitType="e_waitPortClose" nodeId="45">
            <owner-list>
              <owner id="process3bd04b088" />
              <owner id="process3b5d29468" />
              <owner id="process3c1ea6ca8" />
            </owner-list>
            <waiter-list>
              <waiter id="process1f85527c28" />
            </waiter-list>
          </exchangeEvent>
          <exchangeEvent id="Port2832c76100" WaitType="e_waitPortOpen" nodeId="11">
            <owner-list>
              <owner id="process1f85527c28" />
            </owner-list>
            <waiter-list>
              <waiter id="process3b3b22ca8" />
            </waiter-list>
          </exchangeEvent>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>
sql-server sql-server-2014

1 respostas

  1. Best Answer

    Este é um impasse comum. Aqui está a lista de recursos, transformada para mostrar o spid para cada processo:

     <resource-list>

      <pagelock fileid="1" pageid="18686" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock34211ea00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="356-2" mode="U" requestType="wait" />
        </owner-list>
        <waiter-list>
          <waiter id="352-1" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="127895" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock111730a200" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="356-3" mode="U" requestType="wait" />
        </owner-list>
        <waiter-list>
          <waiter id="352-2" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="90295" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock281980c00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="352-5" mode="U" />
        </owner-list>
        <waiter-list>
          <waiter id="356-1" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="18686" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock34211ea00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="352-5" mode="U" />
        </owner-list>
        <waiter-list>
          <waiter id="356-2" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="127895" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock111730a200" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="352-5" mode="U" />
        </owner-list>
        <waiter-list>
          <waiter id="356-3" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="90295" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock281980c00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="356-1" mode="U" requestType="wait" />
        </owner-list>
        <waiter-list>
          <waiter id="352-3" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>

    Você pode ver acima que o spid 356 tem bloqueios de página U que 352 está esperando, e o spid 352 tem bloqueios de página U que o spid 356 está esperando. Os bloqueios 'exchangeEvent' abaixo não mostram nenhum ciclo de propriedade entre os processos para spid 352:

      <exchangeEvent id="Port2832c76700" WaitType="e_waitPortClose" nodeId="45">
    <owner-list>
      <owner id="352-3" />
      <owner id="352-1" />
      <owner id="352-2" />
    </owner-list>
    <waiter-list>
      <waiter id="352-4" />
    </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port2832c76100" WaitType="e_waitPortOpen" nodeId="11">
    <owner-list>
      <owner id="352-4" />
    </owner-list>
    <waiter-list>
      <waiter id="352-5" />
    </waiter-list>
  </exchangeEvent>
</resource-list>

    Não tenho números firmes em mãos, mas esse proc armazenado é executado pelo menos 500 vezes por dia, mas provavelmente mais para nossos clientes maiores.

    Nessa escala, eu apenas envolveria isso em uma transação com uma chamada para sp_getapplock na parte superior da transação e a forçaria a executar uma de cada vez.

    • 3

relate perguntas