Erik's questions

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>

Recentemente, respondi uma pergunta com o seguinte exemplo de código:

Create Table #Testing
(
  emaildomain varchar(100) -- Still bigger than functionally needed but better than MAX
);
INSERT INTO #Testing VALUES (REVERSE('@myfreepaysite.com')); -- Changed


Create Table #DataToCheck
(
  fullemail varchar(200) -- Still bigger than functionally needed but better than MAX
);
Insert Into #DataToCheck VALUES(REVERSE('[email protected]')); --Changed

Select Top 1 
  REVERSE(fullemail)
  , REVERSE(emaildomain)
FROM #DataToCheck
  INNER JOIN #Testing ON fullemail LIKE emaildomain + '%';

Enquanto escrevia a última linha, me perguntei se concatenar EmailDomaina coluna com o símbolo curinga era uma abertura para injeção de SQL. Isso é algo que devo observar com esse tipo de código ou o SQL Server avaliará isso como um único parâmetro unificado para que não precise me preocupar em usar essa abordagem?

Sou novo no MySQL, mas tenho uma experiência razoável com o SQL Server. Eu sei que no SQL Server devo ser muito cauteloso ao usar MERGEinstruções porque elas podem levar a alguns problemas . Há alguma preocupação especial que devo ter ao usar ON DUPLICATE KEY UPDATE? É considerado uma prática recomendada na comunidade MySQL manter INSERT/UPDATEinstruções separadas como no SQL Server ou é bom usar ON DUPLICATE KEY UPDATEamplamente para upserts simples?

Eu estava lendo os exemplos no MSDN para TRY-CATCHblocos.

A listagem de código para o exemplo B é a seguinte:

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

O que não entendo é por que eles se preocupariam em verificar @@TRANCOUNTo CATCHbloco. É realmente possível que a contagem de transações seja < 1 neste trecho de código ou a cláusula de guarda é apenas um exemplo do princípio de que os exemplos devem ser exemplares ?

De acordo com este blog , os parâmetros para uma função ou um procedimento armazenado são essencialmente passados ​​por valor se não forem OUTPUTparâmetros e tratados essencialmente como uma versão mais segura de passagem por referência se forem OUTPUTparâmetros.

A princípio, pensei que o objetivo de forçar a declaração do TVP READONLYera sinalizar claramente aos desenvolvedores que o TVP não pode ser usado como OUTPUTparâmetro, mas deve haver mais coisas acontecendo porque não podemos declarar não-TVP como READONLY. Por exemplo, o seguinte falha:

create procedure [dbo].[test]
@a int readonly
as
    select @a

Msg 346, Nível 15, Estado 1, Teste de procedimento
O parâmetro "@a" não pode ser declarado READONLY, pois não é um parâmetro com valor de tabela.

1. Como as estatísticas não são armazenadas no TVP, qual é a lógica por trás da prevenção de operações DML?
2. Está relacionado a não querer que o TVP seja OUTPUTparâmetro por algum motivo?

Muito parecido com uma pergunta relacionada por swasheck , tenho uma consulta que historicamente sofreu com problemas de desempenho. Eu estava olhando o plano de consulta no SSMS e notei um Nested Loops (Inner Join)com o aviso:

Nenhum Predicado de Junção

Com base em algumas pesquisas apressadas (confiança inspirando Scary DBA e Brent Ozar ), parece que este aviso está me dizendo que tenho um produto cartesiano oculto em minha consulta. Verifiquei minha consulta algumas vezes e não vejo a junção cruzada. Aqui está a consulta:

DECLARE @UserId INT; -- Stored procedure input
DECLARE @Now DATETIME2(7) = SYSUTCDATETIME();
;WITH AggregateStepData_CTE AS -- Considering converting this CTE into an indexed view
(
    SELECT 
        [UA].[UserId] -- FK to the UserId
        , [UA].[DeviceId] -- FK to the push device's DeviceId (int)
        , SUM(ISNULL([UA].[LatestSteps], 0)) AS [Steps]
    FROM [User].[UserStatus] [UA]
        INNER JOIN [User].[CurrentConnections] [M] ON 
           [M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
    WHERE
        [M].[ShareSteps] = 1 -- Only use step data if we are allowed to see.
        AND
        CAST([UA].[ReportedLocalTime] AS DATE) = 
          CAST(DATEADD(MINUTE, DATEPART(TZOFFSET, [UA].[ReportedLocalTime]), @Now) AS DATE)
          -- Aggregate the steps for today based on the device's time zone.         
    GROUP BY
        [UA].[UserId]
        , [UA].[DeviceId]
)
SELECT
    [UA].[UserId] -- FK to the UserId
    , [UA].[ReportedLocalTime]
    , CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[Latitude] ELSE NULL END AS [Latitude]
    , CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[Longitude] ELSE NULL END AS [Longitude]
    , CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[LocationAccuracy] ELSE NULL END 
         AS [LocationAccuracy]
    , CASE WHEN [M].[ShareSteps] = 1 THEN ISNULL([SD].[Steps], 0) ELSE NULL END AS [Steps]
    , CASE WHEN [M].[ShareBattery] = 1 THEN [UA].[BatteryPercentage] ELSE NULL END 
         AS [BatteryPercentage]
    , CASE WHEN [M].[ShareBattery] = 1 THEN [UA].[IsDraining] ELSE NULL END 
         AS [IsDraining]
    , [PD].[DeviceName]
FROM [User].[LatestUserStatus] [UA]
    INNER JOIN [User].[CurrentConnections] [M] WITH (NOEXPAND) ON 
      [M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
    INNER JOIN [User].[PushDevice] [PD] ON [PD].[PushDeviceId] = [UA].[DeviceId]
    LEFT JOIN [AggregateStepData_CTE] [SD] ON 
      [M].[Monitored] = [SD].[UserId] AND [SD].[DeviceId] = [UA].[DeviceId]
ORDER BY        
    [UA].[UserId]
    , [UA].[ReportedLocalTime] DESC

O plano de consulta pode ser encontrado em: https://gist.github.com/anonymous/d6ac970b45eb75a88b99

Ou devo simplesmente não ter medo do aviso, pois foi a conclusão da pergunta de swasheck , afinal o custo estimado da sub-árvore é bastante baixo em 0,05?

Essa resposta também parece relevante, o que implica que provavelmente é uma otimização que o SQL Server está fazendo em meu nome, porque sabe que posso descartar uma junção.

Esta postagem de blog sugere que os loops aninhados sem problema de predicado podem ser causados ​​por um UDF em uma coluna de junção. Não estou fazendo referência a nenhum UDF nesta consulta.

Aqui está a definição da CurrentConnectionsvisão:

CREATE VIEW [User].[CurrentConnections]
WITH SCHEMABINDING
AS 
    SELECT 
        [M].[Monitor] -- FK to the UserId
        , [M].[Monitored] -- FK to the UserId
        , [M].[MonitoringId]
        , [M].[ShareBattery]
        , [M].[ShareLocation]
        , [M].[ShareSteps]
        , [M].[ShowInSocialFeed]
        , [M].[Created] AS [RelationshipCreated]
        , [AT].[AlertThresholdId]
        , [AT].[EffectiveStartTime]
        , [AT].[EndTime]
        , [AT].[OverNightRedThreshold]
        , [AT].[SendBatteryAlerts]
        , [AT].[SendGeneralAlerts]
        , [AT].[StartTime]
        , [AT].[ThresholdInMinutes]
        , [AT].[Threshold]
        , [U_Monitored].[ProfilePhoto] AS [Monitored_ProfilePhoto]
        , [U_Monitored].[DisplayName] AS [Monitored_DisplayName]
        , [U_Monitored].[Fullname] AS [Monitored_FullName]
        , [U_Monitored].[PhoneNumber] AS [Monitored_PhoneNumber]
    FROM [User].[Monitoring] [M]
        INNER JOIN [User].[AlertThreshold] [AT] ON [AT].[MonitoringId] = [M].[MonitoringId]
        INNER JOIN [User].[User] [U_Monitored] ON [U_Monitored].[UserId] = [M].[Monitored]
    WHERE
        [M].[ArchivedOn] IS NULL
        AND
        [AT].[ArchivedOn] IS NULL

GO

CREATE UNIQUE CLUSTERED INDEX [IDX_User_CurrentConnections_Monitor_Monitored] ON 
   [User].[CurrentConnections]([Monitor], [Monitored]);
GO
CREATE NONCLUSTERED INDEX [IDX_User_CurrentConnections_Monitored] ON 
  [User].[CurrentConnections]([Monitored]) 
  INCLUDE ([Monitor], [ShareBattery], [ShareLocation], [ShareSteps]);

Estou escrevendo uma consulta que será usada para os resultados da página de um feed social. O conceito é que o aplicativo móvel solicitará N itens e fornecerá uma data e hora de início que chamei @CutoffTimeabaixo. O objetivo do tempo limite é estabelecer quando a janela de paginação deve começar. A razão pela qual estamos usando um registro de data e hora em vez de um deslocamento de linha é que o registro de data e hora nos permitirá paginar de um local consistente ao obter postagens mais antigas, mesmo se um conteúdo social mais recente for adicionado.

Como os itens do feed social podem ser seus ou de seus amigos, estou usando um UNIONpara combinar os resultados desses dois grupos. Originalmente, tentei a TheQuery_CTElógica sem o UNIONe foi lento demais.

Isto é o que eu fiz (incluindo esquema de tabela pertinente):

    CREATE TABLE [Content].[Photo]
(
    [PhotoId] INT NOT NULL PRIMARY KEY IDENTITY (1, 1), 
    [Key] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    [FullResolutionUrl] NVARCHAR(255) NOT NULL, 
    [Description] NVARCHAR(255) NULL, 
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
);

CREATE TABLE [Content].[UserPhotoAssociation]
(
    [PhotoId] INT NOT NULL, 
    [UserId] INT NOT NULL, 
    [ShowInSocialFeed] BIT NOT NULL DEFAULT 0,

    CONSTRAINT [PK_UserPhotos] PRIMARY KEY ([PhotoId], [UserId]), 
    CONSTRAINT [FK_UserPhotos_User] FOREIGN KEY ([UserId]) 
        REFERENCES [User].[User]([UserId]), 
    CONSTRAINT [FK_UserPhotos_Photo] FOREIGN KEY ([PhotoId]) 
        REFERENCES [Content].[Photo]([PhotoId])
);

CREATE TABLE [Content].[FlaggedPhoto]
(
    [FlaggedPhotoId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [PhotoId] INT NOT NULL,
    [FlaggedBy] INT NOT NULL,
    [FlaggedOn] DATETIME2(0) NOT NULL DEFAULT SYSDATETIME(),
    [FlaggedStatus] INT NOT NULL DEFAULT 1,
    [ReviewedBy] INT NULL,
    [ReviewedAt] DATETIME2(0) NULL

    CONSTRAINT [FK_Photos_PhotoId_to_FlaggedPhotos_PhotoId] FOREIGN KEY ([PhotoId]) 
        REFERENCES [Content].[Photo]([PhotoId]),
    CONSTRAINT [FK_FlaggedPhotoStatus_FlaggedPhotoStatusId_to_FlaggedPhotos_FlaggedStatus] FOREIGN KEY ([FlaggedStatus]) 
        REFERENCES [Content].[FlaggedContentStatus]([FlaggedContentStatusId]),
    CONSTRAINT [FK_User_UserId_to_FlaggedPhotos_FlaggedBy] FOREIGN KEY ([FlaggedBy]) 
        REFERENCES [User].[User]([UserId]),
    CONSTRAINT [FK_User_UserId_to_FlaggedPhotos_ReviewedBy] FOREIGN KEY ([ReviewedBy]) 
        REFERENCES [User].[User]([UserId])
);


CREATE TABLE [User].[CurrentConnections]
(
    [MonitoringId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Monitor] INT NOT NULL,
    [Monitored] INT NOT NULL,
    [ShowInSocialFeed] BIT NOT NULL DEFAULT 1,

    CONSTRAINT [FK_Monitoring_Monitor_to_User_UserId] FOREIGN KEY ([Monitor]) 
         REFERENCES [dbo].[User]([UserId]),
    CONSTRAINT [FK_Monitoring_Monitored_to_User_UserId] FOREIGN KEY ([Monitored]) 
         REFERENCES [dbo].[User]([UserId])
);

CREATE TABLE [Content].[PhotoLike]
(
    [PhotoLikeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PhotoId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,

    CONSTRAINT [FK_PhotoLike_PhotoId_to_Photo_PhotoId] FOREIGN KEY ([PhotoId]) 
         REFERENCES [Content].[Photo]([PhotoId]),
    CONSTRAINT [FK_PhotoLike_UserId_to_User_UserId] FOREIGN KEY ([UserId]) 
         REFERENCES [User].[User]([UserId])
);

CREATE TABLE [Content].[Comment]
(
    [CommentId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PhotoId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Comment] NVARCHAR(255) NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [CommentOrder] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,

    CONSTRAINT [FK_Comment_PhotoId_to_Photo_PhotoId] FOREIGN KEY ([PhotoId]) 
         REFERENCES [Content].[Photo]([PhotoId]),
    CONSTRAINT [FK_Comment_UserId_to_User_UserId] FOREIGN KEY ([UserId]) 
         REFERENCES [User].[User]([UserId])
);

/*

      End table schema

*/



DECLARE @UserId INT,
    @NumberOfItems INT,
    @CutoffTime DATETIME2(2) = NULL -- Stored Proc input params

-- Make the joins and grab the social data we need once since they are used in subsequent queries that aren't shown
DECLARE @SocialFeed TABLE ([Key] UNIQUEIDENTIFIER, [PhotoId] INT
            , [Description] NVARCHAR(255), [FullResolutionUrl] NVARCHAR(255)
            , [Created] DATETIME2(2), [CreatorId] INT, [LikeCount] INT
            , [CommentCount] INT, [UserLiked] BIT);
-- Offset might be different for each group
DECLARE @OffsetMine INT = 0, @OffsetTheirs INT = 0;

IF @CutoffTime IS NOT NULL
    BEGIN
        -- Get the offsets
        ;WITH [GetCounts_CTE] AS
        (
            SELECT
                [P].[PhotoId] -- INT
                , 1 AS [MyPhotos]
            FROM [Content].[Photo] [P]
                INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
                    [UPA].[PhotoId] = [P].[PhotoId] 
                    AND 
                    [UPA].[ShowInSocialFeed] = 1
                LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
                    [FP].[PhotoId] = [P].[PhotoId] 
                    AND 
                    [FP].[FlaggedStatus] = 3 -- Flagged photos that are confirmed apply to everyone
            WHERE
                [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
                AND
                [UPA].[UserId] = @UserId -- Show the requesting user
                AND
                [P].[Created] >= @CutoffTime -- Get the newer items
            UNION
            SELECT
                [P].[PhotoId] -- INT
                , 0 AS [MyPhotos]
            FROM [Content].[Photo] [P]
                INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
                    [UPA].[PhotoId] = [P].[PhotoId] 
                    AND 
                    [UPA].[ShowInSocialFeed] = 1
                INNER JOIN [User].[CurrentConnections] [M] ON 
                    [M].[Monitored] = [UPA].[UserId] 
                    AND 
                    [M].[Monitor] = @UserId AND [M].[ShowInSocialFeed] = 1 -- this join isn't present above  
                LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
                    [FP].[PhotoId] = [P].[PhotoId] 
                    AND 
                    (
                        [FP].[FlaggedStatus] = 3 
                        OR 
                        ([FP].[FlaggedBy] = @UserId AND [FP].[FlaggedStatus] = 1)
                    ) -- Flagged photos that are confirmed apply to everyone, pending flags apply to the user
            WHERE
                [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
                AND
                [P].[Created] >= @CutoffTime -- Get the newer items
        )
        SELECT
            @OffsetMine = SUM(CASE WHEN [MyPhotos] = 1 THEN 1 ELSE 0 END)
            , @OffsetTheirs = SUM(CASE WHEN [MyPhotos] = 0 THEN 1 ELSE 0 END)
        FROM [GetCounts_CTE]
    END

-- Prevent absence of social data from throwing an error below.
SET @OffsetMine = ISNULL(@OffsetMine, 0); 
SET @OffsetTheirs = ISNULL(@OffsetTheirs, 0);

-- Actually select the data I want
;WITH TheQuery_CTE AS
(
    SELECT
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
        , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount] -- Count distinct used due to common join key
        , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
        , CAST(ISNULL(MAX(CASE WHEN [PL].[UserId] = @UserId THEN 1 END), 0) AS BIT) AS [UserLiked]
    FROM [Content].[Photo] [P]
        INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
            [UPA].[PhotoId] = [P].[PhotoId] 
            AND 
            [UPA].[ShowInSocialFeed] = 1
        LEFT JOIN [Content].[PhotoLike] [PL] ON 
            [PL].[PhotoId] = [P].[PhotoId] 
            AND 
            [PL].[Archived] IS NULL
        LEFT JOIN [Content].[Comment] [C] ON 
            [C].[PhotoId] = [P].[PhotoId] 
            AND 
            [C].[Archived] IS NULL
        LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
            [FP].[PhotoId] = [P].[PhotoId] 
            AND 
            [FP].[FlaggedStatus] = 3 -- Flagged photos that are confirmed apply to everyone
    WHERE
        [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
        AND
        [UPA].[UserId] = @UserId -- Show the requesting user
    GROUP BY
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
    ORDER BY  
        [P].[Created] DESC
        , [P].[Key]  -- Ensure consistent order in case of duplicate timestamps
        OFFSET @OffsetMine ROWS FETCH NEXT @NumberOfItems ROWS ONLY
    UNION
    SELECT
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
        , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount]
        , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
        , CAST(ISNULL(MAX(CASE WHEN [PL].[UserId] = @UserId THEN 1 END), 0) AS BIT) AS [UserLiked]
    FROM [Content].[Photo] [P]
        INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
            [UPA].[PhotoId] = [P].[PhotoId] 
            AND 
            [UPA].[ShowInSocialFeed] = 1
        INNER JOIN [User].[CurrentConnections] [M] ON 
            [M].[Monitored] = [UPA].[UserId] 
            AND 
            [M].[Monitor] = @UserId AND [M].[ShowInSocialFeed] = 1
        LEFT JOIN [Content].[PhotoLike] [PL] ON 
            [PL].[PhotoId] = [P].[PhotoId] 
            AND 
            [PL].[Archived] IS NULL
        LEFT JOIN [Content].[Comment] [C] ON 
            [C].[PhotoId] = [P].[PhotoId] 
            AND 
            [C].[Archived] IS NULL
        LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
            [FP].[PhotoId] = [P].[PhotoId] 
            AND 
            (
                [FP].[FlaggedStatus] = 3 
                OR 
                ([FP].[FlaggedBy] = @UserId AND [FP].[FlaggedStatus] = 1)
            ) -- Flagged photos that are confirmed apply to everyone, pending flags apply to the user
    WHERE
        [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
    GROUP BY
        [P].[Key]
        , [P].[PhotoId]
        , [P].[Description]
        , [P].[FullResolutionUrl]
        , [P].[Created]
        , [UPA].[UserId]
    ORDER BY  
        [P].[Created] DESC
        , [P].[Key]  -- Ensure consistant order in case of duplicate timestamps
        OFFSET @OffsetTheirs ROWS FETCH NEXT @NumberOfItems ROWS ONLY
)
INSERT INTO @SocialFeed ([Key], [PhotoId], [Description], [FullResolutionUrl]
            , [Created], [CreatorId], [LikeCount], [CommentCount], [UserLiked])
SELECT TOP (@NumberOfItems)
    [Key]
    , [PhotoId]
    , [Description]
    , [FullResolutionUrl]
    , [Created]
    , [UserId]
    , [LikeCount]
    , [CommentCount]
    , [UserLiked]
FROM [TheQuery_CTE]
ORDER BY  -- Order here so the top works properly
    [Created] DESC
    , [Key]  -- Ensure consistent order in case of duplicate timestamps

-- Output the social feed
SELECT
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description] AS [PhotoDescription]
    , [P].[FullResolutionUrl]
    , [P].[Created] AS [Posted]
    , [P].[CreatorId]
    , [LikeCount]
    , [CommentCount]
    , [UserLiked]
FROM @Photos [P]

-- Select other data needed to build the object tree in the application layer

Percebo que posso me livrar do UNIONin the, GetCounts_CTEmas não acho que isso realmente resolverá nenhum dos problemas que vejo abaixo.

Vejo alguns possíveis problemas:

1. Isso é muita lógica duplicada, então provavelmente estou tornando a vida mais difícil para mim.
2. Se ocorrer uma inserção entre o cálculo da contagem e a seleção dos dados, estarei desligado. Eu não acho que isso aconteceria com frequência, mas levaria a erros estranhos/difíceis de depurar.
3. Todos os problemas que as pessoas mais inteligentes/mais experientes encontrariam com a configuração acima.

Qual é a melhor maneira de escrever esta consulta? Pontos de bônus a solução torna minha vida mais simples.

Editar:

Não quero selecionar todos os dados e deixar o cliente exibir preguiçosamente os itens, porque não quero abusar dos planos de dados das pessoas, forçando-as a baixar itens que nunca verão. É certo que os dados provavelmente não serão tão grandes no grande esquema das coisas, mas pilhas de centavos ....

Editar 2:

Suspeito fortemente que esta não seja a solução ideal, mas é a melhor que encontrei até agora.

Mover minha UNIONconsulta para algo VIEWsemelhante ao que Greg sugeriu funcionou bem para ocultar essa lógica e fornecer uma consulta mais concisa em meu procedimento armazenado. A view também abstrai a feiúra/complicação da união, o que é legal porque estou usando duas vezes no meu select. Segue o código da view:

CREATE VIEW [Social].[EverFeed]
    AS 
SELECT
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
    , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount] -- Distinct due to common join key
    , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
    , CAST(ISNULL(
        MAX(CASE WHEN [PL].[UserId] = [UPA].[UserId] THEN 1 END), 0) AS BIT) AS [UserLiked]
    , NULL AS [Monitor]
FROM [Content].[Photo] [P]
    INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
        [UPA].[PhotoId] = [P].[PhotoId] 
        AND 
        [UPA].[ShowInSocialFeed] = 1
    LEFT JOIN [Content].[PhotoLike] [PL] ON 
        [PL].[PhotoId] = [P].[PhotoId] 
        AND 
        [PL].[Archived] IS NULL
    LEFT JOIN [Content].[Comment] [C] ON 
        [C].[PhotoId] = [P].[PhotoId] 
        AND 
        [C].[Archived] IS NULL
    LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
        [FP].[PhotoId] = [P].[PhotoId] 
        AND 
        [FP].[FlaggedStatus] = 3 -- Flagged photos that are confirmed apply to everyone
WHERE
    [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
GROUP BY
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
UNION
SELECT
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
    , COUNT(DISTINCT [PL].[PhotoLikeId]) AS [LikeCount]
    , COUNT(DISTINCT [C].[CommentId]) AS [CommentCount]
    , CAST(ISNULL(
        MAX(CASE WHEN [PL].[UserId] = [M].[Monitor] THEN 1 END), 0) AS BIT) AS [UserLiked]
    , [M].[Monitor]
FROM [Content].[Photo] [P]
    INNER JOIN [Content].[UserPhotoAssociation] [UPA] ON 
        [UPA].[PhotoId] = [P].[PhotoId] 
        AND 
        [UPA].[ShowInSocialFeed] = 1
    INNER JOIN [User].[CurrentConnections] [M] ON 
        [M].[Monitored] = [UPA].[UserId] 
        AND 
        [M].[ShowInSocialFeed] = 1
    LEFT JOIN [Content].[PhotoLike] [PL] ON 
        [PL].[PhotoId] = [P].[PhotoId] 
        AND 
        [PL].[Archived] IS NULL
    LEFT JOIN [Content].[Comment] [C] ON 
        [C].[PhotoId] = [P].[PhotoId] 
        AND 
        [C].[Archived] IS NULL
    LEFT JOIN [Content].[FlaggedPhoto] [FP] ON 
        [FP].[PhotoId] = [P].[PhotoId] 
        AND 
        (
            [FP].[FlaggedStatus] = 3 
            OR 
            ([FP].[FlaggedBy] = [M].[Monitor] AND [FP].[FlaggedStatus] = 1)
        ) -- Flagged photos that are confirmed (3) apply to everyone
          -- , pending flags (1) apply to the user
WHERE
    [FP].[FlaggedPhotoId] IS NULL -- Filter out flagged photos
GROUP BY
    [P].[Key]
    , [P].[PhotoId]
    , [P].[Description]
    , [P].[FullResolutionUrl]
    , [P].[Created]
    , [UPA].[UserId]
    , [M].[Monitor]

Using that view I shortened my query to the following. Note I'm setting the OFFSET with a subquery.

DECLARE @UserId INT, @NumberOfItems INT, @CutoffTime DATETIME2(2);

SELECT
    [Key]
    , [PhotoId]
    , [Description]
    , [FullResolutionUrl]
    , [Created]
    , [UserId]
    , [LikeCount]
    , [CommentCount]
    , [UserLiked]
FROM  [Social].[EverFeed] [EF]
WHERE
    (
        ([EF].[UserId] = @UserId AND [EF].[Monitor] IS NULL)
        OR 
        [EF].[Monitor] = @UserId
    )
ORDER BY  -- Order here so the top works properly
    [Created] DESC
    , [Key]  -- Ensure consistant order in case of duplicate timestamps
    OFFSET CASE WHEN @CutoffTime IS NULL THEN 0 ELSE        
            (
                SELECT
                    COUNT([PhotoId])
                FROM [Social].[EverFeed] [EF]
                WHERE
                    (
                        ([EF].[UserId] = @UserId AND [EF].[Monitor] IS NULL)
                        OR 
                        [EF].[Monitor] = @UserId
                    )
                    AND
                    [EF].[Created] >= @CutoffTime -- Get the newer items
            ) END 
    ROWS FETCH NEXT @NumberOfItems ROWS ONLY

The view nicely separates the complexity of the UNION from the filtering. I think the subquery in the OFFSET clause will prevent concurrency issues that I was worried about by making the whole query atomic.

One problem I just found while typing this is: in the code above if two photos with the same creation date are on different "pages" then the photos on the subsequent pages will be filtered out. Consider the following data:

PhotoId | Created | ...
------------------------
   1    | 2015-08-26 01:00.00
   2    | 2015-08-26 01:00.00
   3    | 2015-08-26 01:00.00

With a page size of 1 on the initial page, PhotoId 1 will be returned. With the same page size on the second page no results will be returned. I think in order to resolve this I'm going to have to add the Key Guid as a parameter....

Baixei o SQL Server First Aid Kit do site de Brent Ozar. Quando tentei executar o script sp_BlitzIndex em meu banco de dados mestre por meio do Microsoft Sql Server Management Studio enquanto estava conectado como administrador de nível de servidor de banco de dados do Azure, recebi o seguinte erro:

Msg 262, Nível 14, Estado 18, Procedimento sp_BlitzIndex, Linha 18 Permissão CREATE PROCEDURE negada no banco de dados 'mestre'.

Criei com sucesso o procedimento na instância do banco de dados que queria testar. Ao executar o procedimento, recebi um erro informando:

Msg 50000, Nível 16, Estado 1, Linha 1265 Nome de objeto inválido 'mydatabase.sys.partitions'.

Em seguida, tentei ser inteligente e executar o código do procedimento armazenado diretamente no banco de dados mestre sem criar o procedimento armazenado e obtive o seguinte erro:

Msg 50000, Nível 15, Estado 1, Linha 1267 A referência ao banco de dados e/ou nome do servidor em 'mydatabase.sys.indexes' não é suportada nesta versão do SQL Server.

Não me sinto confiante o suficiente para começar a brincar com o funcionamento interno de ~ 2.700 linhas de lógica heurística de índice. Existe uma maneira rápida e fácil de fazer com que esse procedimento armazenado funcione bem no Banco de Dados SQL do Azure ou devo procurar em outro lugar uma ferramenta/procedimento armazenado de análise de índice?