jericzech's questions

Aqueles que usam excelentes sp_HumanEvents, talvez o próprio autor, por favor me ajudem a entender algo que estou perdendo (por causa da minha estupidez).

#1

Ao monitorar o bloqueio, o limite do processo bloqueado deve ser definido (em segundos), caso contrário, o block_process_event não seria acionado. Como isso se correlaciona com o parâmetro @blocking_duration_ms?

Exemplo: ID de limite de processo bloqueado definido para 10 segundos @blocking_duration_ms é deixado como padrão = 500 ms

#2

Quando quero registrar resultados na tabela de forma contínua e independente nas reinicializações do servidor, é recomendável usar um trabalho de agente com um exemplo que define um agendamento com o nome sp_HumanEvents: Check-in de 10 segundos , mas executado recorrentemente aos domingos à meia-noite. Não estaria iniciando automaticamente quando o SQL Server Agent iniciasse mais apropriado?

Eu tenho o SQL Server 2019 Express Edition (CU8) no Windows Server 2019 (versão 1809) e todas as minhas consultas são serial com NoParallelPlansInDesktopOrExpressEditionpropriedade NoparallelPlanReason.

Será que a Express Edition nunca fica paralela? Não consigo encontrar nada sobre isso na documentação da Microsoft.

Por que o MS SQL Server se recusa a usar o índice filtrado de suporte neste cenário?

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

Tudo corre como esperado ao usar literais.

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

Gostaria de saber se existe alguma maneira de reescrever a EXPRESSÃO na consulta (apenas expressão, não a consulta inteira) para a avaliação inútil de curto-circuito da fase THEN?

Dados de demonstração:

CREATE TABLE #Docs (
    ID INT NOT NULL
    ,DocType TINYINT NOT NULL
    );

CREATE TABLE #DocsItems (
    IDDocs INT NOT NULL
    ,Amount NUMERIC(19,6)
    );

INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);

Consulta do assunto:

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

Plano de consulta:

Se eu reescrever a consulta (de propósito) para:

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN 2 = 1 /* rewrite*/  THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1

O plano segue do jeito que deveria/poderia/faria na consulta original:

Gostaria de pedir ajuda à comunidade para este cenário de impasse:

<deadlock-list>
 <deadlock victim="process17392264e8">
  <process-list>
   <process id="process17392264e8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="4449" ownerId="394509589" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:41.320" XDES="0x18ed9516f0" lockMode="IX" schedulerid="4" kpid="8348" status="suspended" spid="143" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:41.310" lastbatchcompleted="2019-06-05T12:06:41.310" lastattention="1900-01-01T00:00:00.310" clientapp="Helios Orange" hostname="???" hostpid="508" loginname="???" isolationlevel="read committed (2)" xactid="394509589" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
    <executionStack>
     <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
         FROM TabDavka D 
           INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
         WHERE D.IDDilce=@IDKusovni     </frame>
     <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x010007000f5ba906f09b4b6f0700000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1     </frame>
    </executionStack>
    <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1
SELECT @ret    </inputbuf>
   </process>
   <process id="process15bf6ce8c8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="6020" ownerId="394493191" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:39.750" XDES="0x16fb190ea0" lockMode="IX" schedulerid="1" kpid="3940" status="suspended" spid="83" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:39.750" lastbatchcompleted="2019-06-05T12:06:39.750" lastattention="1900-01-01T00:00:00.750" clientapp="Helios Orange" hostname="???" hostpid="14924" loginname="???" isolationlevel="read committed (2)" xactid="394493191" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
    <executionStack>
     <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
         FROM TabDavka D 
           INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
         WHERE D.IDDilce=@IDKusovni     </frame>
     <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x0100070009fde604c031f1451a00000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1     </frame>
    </executionStack>
    <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1
SELECT @ret    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
    <owner-list>
     <owner id="process15bf6ce8c8" mode="S"/>
     <owner id="process15bf6ce8c8" mode="IX" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process17392264e8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
    <owner-list>
     <owner id="process17392264e8" mode="S"/>
     <owner id="process17392264e8" mode="IX" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process15bf6ce8c8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Está no mesmo recurso (tabela) e está sendo causado pelo mesmo procedimento invocado de duas sessões.

O fluxo simplificado do procedimento é assim:

SELECT @Variable = Column FROM Table WITH (HOLDLOCK)
…
UPDATE Table SET...

Eu apreciaria muito qualquer conselho sobre como evitar esse cenário de impasse?

Suponha que temos uma definição de tabela como esta:

CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn DATETIME NULL
    )

E um índice não clusterizado filtrado como este:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab 
    (GroupByColumn)
WHERE (WhereColumn IS NULL) 

Por que este índice não está "cobrindo" para esta consulta:

SELECT 
    GroupByColumn
    ,COUNT(*)
FROM MyTab
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn

Estou recebendo este plano de execução:

O KeyLookup é para o predicado WhereColumn IS NULL.

Aqui está o plano: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

Com relação ao modelo de execução do SQLOS (estado RUNNING, fila RUNNABLE, lista WAITER), qual será o estado de uma tarefa quando houver uma leitura lógica de páginas na RAM atualmente em andamento?

Se for uma lista de GARÇONS, qual seria o tipo de espera mais prevalente?

Posso de alguma forma medir o tempo que essa operação leva?

Eu sei que muitas leituras lógicas diminuem suas consultas, muitas varreduras de tabela / índice (já residem no buffer pool) diminuem suas consultas - eu só quero saber como elas aparecem nas estatísticas/dmvs ou como distingui-las de outras tipos de espera "clássicos".

Estou vendo um aumento no PAGELATCH, PAGEIOLATCHe WRITELOGaguarda após a migração para o SQL Server 2017 (do SQL Server 2008). Enquanto eu posso explicar o aumento PAGEIOLATCHe WRITELOG(temos outros discos - HDD em vez de SSD), mas não sei porque PAGELATCHsobe também.

Eles estão de alguma forma relacionados?

O que eu vejo ao redor eles não são, porque PAGELATCHpertence à memória, enquanto os outros ao IO.

Você tem uma explicação, por que o otimizador de consulta está escolhendo diferentes índices e modos neste exemplo?

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
    ID INT IDENTITY PRIMARY KEY
    ,CustNo INT NULL
    ,CustNo2 INT NULL
    );

-- populate with data
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
    SELECT CustNo
    FROM #Test
    WHERE CustNo = @FindNo;