Strojenie wydajności dla zapytania

Poszukuję pomocy w poprawie wydajności tego zapytania.

SQL Server 2008 R2 Enterprise , maks. Pamięć RAM 16 GB, procesor 40, maks. Stopień równoległości 4.

SELECT DsJobStat.JobName AS JobName
    , AJF.ApplGroup AS GroupName
    , DsJobStat.JobStatus AS JobStatus
    , AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) AS ElapsedSecAVG
    , AVG(CAST(DsJobStat.CpuMSec AS FLOAT)) AS CpuMSecAVG 
FROM DsJobStat, AJF 
WHERE DsJobStat.NumericOrderNo=AJF.OrderNo 
AND DsJobStat.Odate=AJF.Odate 
AND DsJobStat.JobName NOT IN( SELECT [DsAvg].JobName FROM [DsAvg] )         
GROUP BY DsJobStat.JobName
, AJF.ApplGroup
, DsJobStat.JobStatus
HAVING AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) <> 0;

Komunikat o wykonaniu,

(0 row(s) affected)
Table 'AJF'. Scan count 11, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsAvg'. Scan count 2, logical reads 1926, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsJobStat'. Scan count 1, logical reads 3831235, physical reads 85, read-ahead reads 3724396, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
      CPU time = 67268 ms,  elapsed time = 90206 ms.

Struktura tabel:

-- 212271023 rows
CREATE TABLE [dbo].[DsJobStat](
    [OrderID] [nvarchar](8) NOT NULL,
    [JobNo] [int] NOT NULL,
    [Odate] [datetime] NOT NULL,
    [TaskType] [nvarchar](255) NULL,
    [JobName] [nvarchar](255) NOT NULL,
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL,
    [NodeID] [nvarchar](255) NULL,
    [GroupName] [nvarchar](255) NULL,
    [CompStat] [int] NULL,
    [RerunCounter] [int] NOT NULL,
    [JobStatus] [nvarchar](255) NULL,
    [CpuMSec] [int] NULL,
    [ElapsedSec] [int] NULL,
    [StatusReason] [nvarchar](255) NULL,
    [NumericOrderNo] [int] NULL,
CONSTRAINT [PK_DsJobStat] PRIMARY KEY CLUSTERED 
(   [OrderID] ASC,
    [JobNo] ASC,
    [Odate] ASC,
    [JobName] ASC,
    [RerunCounter] ASC
));

-- 48992126 rows
CREATE TABLE [dbo].[AJF](  
    [JobName] [nvarchar](255) NOT NULL,
    [JobNo] [int] NOT NULL,
    [OrderNo] [int] NOT NULL,
    [Odate] [datetime] NOT NULL,
    [SchedTab] [nvarchar](255) NULL,
    [Application] [nvarchar](255) NULL,
    [ApplGroup] [nvarchar](255) NULL,
    [GroupName] [nvarchar](255) NULL,
    [NodeID] [nvarchar](255) NULL,
    [Memlib] [nvarchar](255) NULL,
    [Memname] [nvarchar](255) NULL,
    [CreationTime] [datetime] NULL,
CONSTRAINT [AJF$PrimaryKey] PRIMARY KEY CLUSTERED 
(   [JobName] ASC,
    [JobNo] ASC,
    [OrderNo] ASC,
    [Odate] ASC
));

-- 413176 rows
CREATE TABLE [dbo].[DsAvg](
    [JobName] [nvarchar](255) NULL,
    [GroupName] [nvarchar](255) NULL,
    [JobStatus] [nvarchar](255) NULL,
    [ElapsedSecAVG] [float] NULL,
    [CpuMSecAVG] [float] NULL
);

CREATE NONCLUSTERED INDEX [DJS_Dashboard_2] ON [dbo].[DsJobStat] 
(   [JobName] ASC,
    [Odate] ASC,
    [StartTime] ASC,
    [EndTime] ASC
)
INCLUDE ( [OrderID],
[JobNo],
[NodeID],
[GroupName],
[JobStatus],
[CpuMSec],
[ElapsedSec],
[NumericOrderNo]) ;

CREATE NONCLUSTERED INDEX [Idx_Dashboard_AJF] ON [dbo].[AJF] 
(   [OrderNo] ASC,
[Odate] ASC
)
INCLUDE ( [SchedTab],
[Application],
[ApplGroup]) ;

CREATE NONCLUSTERED INDEX [DsAvg$JobName] ON [dbo].[DsAvg] 
(   [JobName] ASC
)

Plan wykonania:

https://www.brentozar.com/pastetheplan/?id=rkUVhMlXM

Zaktualizuj po otrzymaniu odpowiedzi

Dziękuję bardzo @Joe Obbish

Masz rację w kwestii tego zapytania, które dotyczy między DsJobStat i DsAvg. Nie chodzi o to, jak DOŁĄCZYĆ i nie używać NOT IN.

Rzeczywiście jest stół, jak się domyślacie.

CREATE TABLE [dbo].[DSJobNames](
    [JobName] [nvarchar](255) NOT NULL,
 CONSTRAINT [DSJobNames$PrimaryKey] PRIMARY KEY CLUSTERED 
(   [JobName] ASC
) ); 

Próbowałem twojej sugestii,

SELECT DsJobStat.JobName AS JobName
, AJF.ApplGroup AS GroupName
, DsJobStat.JobStatus AS JobStatus
, AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) AS ElapsedSecAVG
, Avg(CAST(DsJobStat.CpuMSec AS FLOAT)) AS CpuMSecAVG 
FROM DsJobStat
INNER JOIN DSJobNames jn
    ON jn.[JobName]= DsJobStat.[JobName]
INNER JOIN AJF 
    ON DsJobStat.Odate=AJF.Odate 
    AND DsJobStat.NumericOrderNo=AJF.OrderNo 
WHERE NOT EXISTS ( SELECT 1 FROM [DsAvg] WHERE jn.JobName =  [DsAvg].JobName )      
GROUP BY DsJobStat.JobName, AJF.ApplGroup, DsJobStat.JobStatus
HAVING AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) <> 0;   

Komunikat o wykonaniu:

(0 row(s) affected)
Table 'DSJobNames'. Scan count 5, logical reads 1244, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsAvg'. Scan count 5, logical reads 2129, physical reads 0, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsJobStat'. Scan count 8, logical reads 84, physical reads 0, read-ahead reads 83, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AJF'. Scan count 5, logical reads 757999, physical reads 944, read-ahead reads 757311, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 21776 ms,  elapsed time = 33984 ms.

Plan wykonania: https://www.brentozar.com/pastetheplan/?id=rJVkLSZ7f

Zacznijmy od rozważenia kolejności dołączania. W zapytaniu masz trzy odwołania do tabeli. Które zamówienie dołączenia może zapewnić najlepszą wydajność? Optymalizator zapytań uważa, że ​​łączenie od DsJobStatdo DsAvgwyeliminuje prawie wszystkie wiersze (szacunki liczności spadają z 212195000 do 1 wiersza). Rzeczywisty plan pokazuje nam, że oszacowanie jest bardzo zbliżone do rzeczywistości (11 rzędów przetrwa złączenie). Jednak łączenie jest realizowane jako prawidłowe łączenie anty-scalające, więc wszystkie 212 milionów wierszy z DsJobStattabeli są skanowane tylko w celu uzyskania 11 wierszy. Może to z pewnością przyczynić się do długiego czasu wykonywania zapytania, ale nie mogę wymyślić lepszego operatora fizycznego lub logicznego dla tego łączenia, który byłby lepszy. Jestem pewien, żeDJS_Dashboard_2Indeks jest używany do innych zapytań, ale wszystkie dodatkowe klucze i dołączone kolumny będą wymagały tylko więcej operacji wejścia / wyjścia dla tego zapytania i spowolnią. Więc potencjalnie masz problem z dostępem do tabeli podczas skanowania indeksu w DsJobStattabeli.

Zakładam, że dołączenie do AJFnie jest zbyt selektywne. Obecnie nie ma to wpływu na problemy z wydajnością, które widzisz w zapytaniu, więc zignoruję to do końca tej odpowiedzi. To może się zmienić, jeśli zmienią się dane w tabeli.

Innym problemem wynikającym z planu jest operator buforowania liczby wierszy. Jest to bardzo lekki operator, ale wykonuje ponad 200 milionów razy. Jest tam operator, ponieważ zapytanie jest napisane za pomocą NOT IN. Jeśli jest pojedynczy wiersz NULL, DsAvgwszystkie wiersze muszą zostać wyeliminowane. Szpula jest implementacją tej kontroli. Prawdopodobnie nie jest to logika, którą chcesz, więc lepiej byłoby napisać tę część do użycia NOT EXISTS. Rzeczywista korzyść z tego przepisywania będzie zależeć od systemu i danych.

Wyszydziłem niektóre dane w oparciu o plan zapytań, aby przetestować kilka przeróbek zapytań. Moje definicje tabel znacznie się różnią od twoich, ponieważ próbowanie danych dla każdej kolumny byłoby zbyt wielkim wysiłkiem. Nawet przy użyciu skróconych struktur danych udało mi się odtworzyć występujący problem z wydajnością.

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;

Na podstawie planu zapytań możemy zobaczyć, że JobNamew DsAvgtabeli znajduje się około 200 000 unikalnych wartości . Na podstawie rzeczywistej liczby wierszy po złączeniu z tą tabelą możemy zobaczyć, że prawie wszystkie JobNamewartości w DsJobStatsą również w DsAvgtabeli. Tak więc DsJobStattabela ma 200001 unikalnych wartości dla JobNamekolumny i 1000 wierszy na wartość.

Uważam, że to zapytanie reprezentuje problem z wydajnością:

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

Wszystkie pozostałe elementy w twoim planie zapytań ( GROUP BY, HAVINGłączenie w starym stylu itp.) Mają miejsce po zmniejszeniu zestawu wyników do 11 wierszy. Obecnie nie ma to znaczenia z punktu widzenia wydajności zapytania, ale mogą istnieć inne obawy, które mogą zostać ujawnione przez zmienione dane w twoich tabelach.

Testuję w SQL Server 2017, ale otrzymuję ten sam podstawowy plan, co Ty:

Na moim komputerze wykonanie tego zapytania zajmuje 62219 ms czasu procesora i 65576 ms czasu, który upłynął. Jeśli przepiszę zapytanie, aby użyć 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);

Szpula nie jest już wykonywana 212 milionów razy i prawdopodobnie ma zamierzone zachowanie od dostawcy. Teraz zapytanie wykonuje się w 34516 ms czasu procesora i 41132 ms czasu, który upłynął. Większość czasu spędza na skanowaniu 212 milionów wierszy z indeksu.

To skanowanie indeksu jest bardzo niefortunne dla tego zapytania. Średnio mamy 1000 wierszy na unikalną wartość JobName, ale po przeczytaniu pierwszego wiersza wiemy, czy potrzebujemy 1000 poprzednich wierszy. Prawie nigdy nie potrzebujemy tych wierszy, ale i tak musimy je zeskanować. Jeśli wiemy, że rzędy nie są zbyt gęste w tabeli i że prawie wszystkie z nich zostaną wyeliminowane przez połączenie, możemy sobie wyobrazić potencjalnie bardziej wydajny wzorzec IO na indeksie. Co się stanie, jeśli SQL Server przeczyta pierwszy wiersz dla każdej unikalnej wartości JobName, sprawdzi, czy ta wartość była w DsAvgśrodku, i po prostu przejdzie do następnej wartości, JobNamejeśli tak było? Zamiast skanować 212 milionów wierszy można zamiast tego wykonać plan wyszukiwania wymagający około 200 tys. Egzekucji.

Można to w większości osiągnąć za pomocą rekurencji i techniki, którą Paul White był pionierem, który opisano tutaj . Możemy użyć rekurencji do wykonania wzorca IO, który opisałem powyżej:

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);

Na to pytanie warto spojrzeć, więc zalecam uważne sprawdzenie aktualnego planu . Najpierw robimy 200002 indeksów w stosunku do indeksu, DsJobStataby uzyskać wszystkie unikalne JobNamewartości. Następnie łączymy się DsAvgi eliminujemy wszystkie rzędy oprócz jednego. W przypadku pozostałego wiersza połącz się ponownie DsJobStati uzyskaj wszystkie wymagane kolumny.

Wzorzec IO całkowicie się zmienia. Zanim to otrzymaliśmy:

Tabela „DsJobStat”. Liczba skanów 1, odczyt logiczny 1091651, odczyt fizyczny 13836, odczyt z wyprzedzeniem 181966

Z zapytaniem rekurencyjnym otrzymujemy:

Tabela „DsJobStat”. Liczba skanów 200003, odczyt logiczny 1398000, odczyt fizyczny 1, odczyt z wyprzedzeniem 7345

Na moim komputerze nowe zapytanie wykonuje się w zaledwie 6891 ms czasu procesora i 7107 ms czasu, który upłynął. Zauważ, że konieczność użycia rekurencji w ten sposób sugeruje, że czegoś brakuje w modelu danych (a może po prostu nie podano go w opublikowanym pytaniu). Jeśli istnieje stosunkowo mały stół, który zawiera wszystkie możliwe JobNames, znacznie lepiej będzie użyć tego stołu, niż w przypadku rekurencji na dużym stole. Sprowadza się to do tego, że jeśli masz zestaw wyników zawierający wszystkie JobNamespotrzebne dane, możesz użyć indeksów, aby uzyskać resztę brakujących kolumn. Nie możesz tego jednak zrobić z zestawem wyników JobNames, którego NIE potrzebujesz.

Zobacz, co się stanie, jeśli przepiszesz warunek,

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

Do

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

Rozważ także przepisanie połączenia SQL89, ponieważ ten styl jest okropny.

Zamiast

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

Próbować

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

Podejrzewam również, że ten warunek można lepiej napisać, ale musielibyśmy wiedzieć więcej o tym, co się dzieje

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

Czy naprawdę musisz wiedzieć, że średnia nie jest równa zero, czy tylko jeden element w grupie nie jest równy zero?