Ajuste de desempenho em uma consulta

Vamos começar considerando a ordem de junção. Você tem três referências de tabela na consulta. Qual ordem de junção pode oferecer o melhor desempenho? O otimizador de consulta pensa que a junção de DsJobStatto DsAvgeliminará quase todas as linhas (as estimativas de cardinalidade caem de 212195000 para 1 linha). O plano real nos mostra que a estimativa está bem próxima da realidade (11 linhas sobrevivem à junção). No entanto, a junção é implementada como uma junção anti semi-mesclagem direita, portanto, todas as 212 milhões de linhas da DsJobStattabela são verificadas apenas para produzir 11 linhas. Isso certamente poderia estar contribuindo para o longo tempo de execução da consulta, mas não consigo pensar em um operador físico ou lógico melhor para essa junção que teria sido melhor. tenho certeza que oDJS_Dashboard_2index é usado para outras consultas, mas todas as chaves extras e colunas incluídas apenas exigirão mais IO para essa consulta e o deixarão mais lento. Portanto, você potencialmente tem um problema de acesso à tabela com a varredura de índice na DsJobStattabela.

Eu vou assumir que a junção a AJFnão é muito seletiva. No momento, ele não é relevante para os problemas de desempenho que você está vendo na consulta, então vou ignorá-lo no restante desta resposta. Isso pode mudar se os dados na tabela forem alterados.

O outro problema aparente no plano é o operador de spool de contagem de linhas. Este é um operador muito leve, mas está sendo executado mais de 200 milhões de vezes. O operador está lá porque a consulta é escrita com NOT IN. Se houver uma única linha NULL DsAvg, todas as linhas devem ser eliminadas. O carretel é a implementação dessa verificação. Essa provavelmente não é a lógica que você deseja, então seria melhor escrever essa parte para usar NOT EXISTS. O benefício real dessa reescrita dependerá do sistema e dos dados.

Eu zombei de alguns dados com base no plano de consulta para testar algumas reescritas de consulta. Minhas definições de tabela são significativamente diferentes das suas porque teria sido muito trabalhoso simular dados para cada coluna. Mesmo com as estruturas de dados abreviadas, consegui reproduzir o problema de desempenho que você está enfrentando.

CREATE TABLE [dbo].[DsAvg](
    [JobName] [nvarchar](255) NULL
);

CREATE CLUSTERED INDEX CI_DsAvg ON [DsAvg] (JobName);

INSERT INTO [DsAvg] WITH (TABLOCK)
SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE TABLE [dbo].[DsJobStat](
    [JobName] [nvarchar](255) NOT NULL,
    [JobStatus] [nvarchar](255) NULL,
);

CREATE CLUSTERED INDEX CI_JobStat ON DsJobStat (JobName)

INSERT INTO [DsJobStat] WITH (TABLOCK)
SELECT [JobName], 'ACTIVE'
FROM [DsAvg] ds
CROSS JOIN (
SELECT TOP (1000) 1
FROM master..spt_values t1
) c (t);

INSERT INTO [DsJobStat] WITH (TABLOCK)
SELECT TOP (1000) '200001', 'ACTIVE'
FROM master..spt_values t1;

Com base no plano de consulta, podemos ver que existem cerca de 200.000 JobNamevalores únicos na DsAvgtabela. Com base no número real de linhas após a junção a essa tabela, podemos ver que quase todos os JobNamevalores de DsJobStattambém estão na DsAvgtabela. Assim, a DsJobStattabela possui 200.001 valores únicos para a JobNamecoluna e 1.000 linhas por valor.

Acredito que esta consulta representa o problema de desempenho:

SELECT DsJobStat.JobName AS JobName, DsJobStat.JobStatus AS JobStatus
FROM DsJobStat
WHERE DsJobStat.JobName NOT IN( SELECT [DsAvg].JobName FROM [DsAvg] );

Todas as outras coisas em seu plano de consulta ( GROUP BY, HAVING, junção de estilo antigo, etc) acontecem depois que o conjunto de resultados foi reduzido para 11 linhas. Atualmente, não importa do ponto de vista do desempenho da consulta, mas pode haver outras preocupações que podem ser reveladas por dados alterados em suas tabelas.

Estou testando no SQL Server 2017, mas recebo a mesma forma de plano básico que você:

Na minha máquina, essa consulta leva 62.219 ms de tempo de CPU e 65.576 ms de tempo decorrido para ser executada. Se eu reescrever a consulta para usar NOT EXISTS:

SELECT DsJobStat.JobName AS JobName, DsJobStat.JobStatus AS JobStatus
FROM DsJobStat
WHERE NOT EXISTS (SELECT 1 FROM [DsAvg] WHERE DsJobStat.JobName = [DsAvg].JobName);

O spool não é mais executado 212 milhões de vezes e provavelmente tem o comportamento pretendido do fornecedor. Agora a consulta é executada em 34.516 ms de tempo de CPU e 41.132 ms de tempo decorrido. A maior parte do tempo é gasto verificando 212 milhões de linhas do índice.

Essa verificação de índice é muito infeliz para essa consulta. Em média, temos 1.000 linhas por valor único de JobName, mas sabemos depois de ler a primeira linha se precisaremos das 1.000 linhas anteriores. Quase nunca precisamos dessas linhas, mas ainda precisamos digitalizá-las de qualquer maneira. Se soubermos que as linhas não são muito densas na tabela e que quase todas serão eliminadas pelo join podemos imaginar um padrão de IO possivelmente mais eficiente no índice. E se o SQL Server lesse a primeira linha por valor exclusivo de JobName, verificasse se esse valor estava em DsAvge simplesmente pulasse para o próximo valor de JobNamese estivesse? Em vez de verificar 212 milhões de linhas, um plano de busca exigindo cerca de 200 mil execuções poderia ser feito.

Isso pode ser feito principalmente usando recursão junto com uma técnica pioneira de Paul White que é descrita aqui . Podemos usar a recursão para fazer o padrão IO que descrevi acima:

WITH RecursiveCTE
AS
(
    -- Anchor
    SELECT TOP (1)
        [JobName]
    FROM dbo.DsJobStat AS T
    ORDER BY
        T.[JobName]

    UNION ALL

    -- Recursive
    SELECT R.[JobName]
    FROM
    (
        -- Number the rows
        SELECT 
            T.[JobName],
            rn = ROW_NUMBER() OVER (
                ORDER BY T.[JobName])
        FROM dbo.DsJobStat AS T
        JOIN RecursiveCTE AS R
            ON R.[JobName] < T.[JobName]
    ) AS R
    WHERE
        -- Only the row that sorts lowest
        R.rn = 1
)
SELECT js.*
FROM RecursiveCTE
INNER JOIN dbo.DsJobStat js ON RecursiveCTE.[JobName]= js.[JobName]
WHERE NOT EXISTS (SELECT 1 FROM [DsAvg] WHERE RecursiveCTE.JobName = [DsAvg].JobName)
OPTION (MAXRECURSION 0);

Essa consulta é muito para olhar, então eu recomendo examinar cuidadosamente o plano real . Primeiro, fazemos buscas de índice 200002 contra o índice DsJobStatpara obter todos os JobNamevalores exclusivos. Em seguida, juntamos DsAvge eliminamos todas as linhas, exceto uma. Para a linha restante, junte-se novamente DsJobState obtenha todas as colunas necessárias.

O padrão IO muda totalmente. Antes de termos isso:

Tabela 'DsJobStat'. Contagem de varredura 1, leituras lógicas 1091651, leituras físicas 13836, leituras antecipadas 181966

Com a consulta recursiva, obtemos isso:

Tabela 'DsJobStat'. Contagem de varredura 200003, leituras lógicas 1398000, leituras físicas 1, leituras antecipadas 7345

Na minha máquina, a nova consulta é executada em apenas 6891 ms de tempo de CPU e 7107 ms de tempo decorrido. Observe que a necessidade de usar a recursão dessa maneira sugere que algo está faltando no modelo de dados (ou talvez não tenha sido declarado na pergunta postada). Se houver uma tabela relativamente pequena que contenha todas as possibilidades JobNames, será muito melhor usar essa tabela em oposição à recursão na tabela grande. O que se resume a isso é que, se você tiver um conjunto de resultados contendo tudo o JobNamesque precisa, poderá usar o índice procura para obter o restante das colunas ausentes. No entanto, você não pode fazer isso com um conjunto de resultados JobNamesque você NÃO precisa.

Veja o que acontece se você reescrever a condição,

AND DsJobStat.JobName NOT IN( SELECT [DsAvg].JobName FROM [DsAvg] )         

Para

AND NOT EXISTS ( SELECT 1 FROM [DsAvg] AS d WHERE d.JobName = DsJobStat.JobName )

Considere também reescrever sua junção SQL89 porque esse estilo é horrível.

Ao invés de

FROM DsJobStat, AJF 
WHERE DsJobStat.NumericOrderNo=AJF.OrderNo 
AND DsJobStat.Odate=AJF.Odate 

Tentar

FROM DsJobStat
INNER JOIN AJF ON (
  DsJobStat.NumericOrderNo=AJF.OrderNo 
  AND DsJobStat.Odate=AJF.Odate
)

Eu também suspeito que essa condição pode ser escrita melhor, mas teríamos que saber mais sobre o que está acontecendo

HAVING AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) <> 0;

Você realmente precisa saber que a média não é zero, ou apenas que um elemento do grupo não é zero?