Nieoczekiwane skanowanie podczas operacji usuwania przy użyciu GDZIE

Mam zapytanie podobne do następującego:

DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
    SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)

tblFEStatsBrowsers ma 553 wierszy.
tblFEStatsPaperHits ma 47.974.301 wierszy.

tblFEStatsBrowsers:

CREATE TABLE [dbo].[tblFEStatsBrowsers](
    [BrowserID] [smallint] IDENTITY(1,1) NOT NULL,
    [Browser] [varchar](50) NOT NULL,
    [Name] [varchar](40) NOT NULL,
    [Version] [varchar](10) NOT NULL,
    CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)

tblFEStatsPaperHits:

CREATE TABLE [dbo].[tblFEStatsPaperHits](
    [PaperID] [int] NOT NULL,
    [Created] [smalldatetime] NOT NULL,
    [IP] [binary](4) NULL,
    [PlatformID] [tinyint] NULL,
    [BrowserID] [smallint] NULL,
    [ReferrerID] [int] NULL,
    [UserLanguage] [char](2) NULL
)

Indeks tblFEStatsPaperHits zawiera klastrowany indeks, który nie zawiera BrowserID. Wykonanie wewnętrznego zapytania będzie zatem wymagało pełnego skanowania tabeli tblFEStatsPaperHits - co jest całkowicie OK.

Obecnie dla każdego wiersza w tblFEStatsBrowsers wykonywane jest pełne skanowanie, co oznacza, że ​​mam 553 skanów pełnej tabeli dla tblFEStatsPaperHits.

Przepisanie tylko do GDZIE ISTNIEJE nie zmienia planu:

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)

Jednak, jak sugeruje Adam Machanic, dodanie opcji HASH JOIN skutkuje optymalnym planem wykonania (tylko jeden skan tblFEStatsPaperHits):

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)

Teraz nie jest to tak bardzo pytanie, jak to naprawić - mogę albo użyć OPCJI (HASH JOIN), albo ręcznie utworzyć tabelę tymczasową. Zastanawiam się bardziej, dlaczego optymalizator zapytań kiedykolwiek używałby planu, który obecnie wykonuje.

Ponieważ QO nie ma żadnych statystyk w kolumnie BrowserID, domyślam się, że przyjmuje on najgorsze - 50 milionów różnych wartości, co wymaga dość dużego stołu roboczego w pamięci / tempdb. W związku z tym najbezpieczniejszym sposobem jest skanowanie dla każdego wiersza w tblFEStatsBrowsers. Nie ma relacji klucza obcego między kolumnami BrowserID w dwóch tabelach, więc QO nie może odjąć żadnych informacji od tblFEStatsBrowsers.

Czy to jest tak prosty, jak się wydaje, powód?

Aktualizacja 1
Aby podać kilka statystyk: OPCJA (HASH JOIN):
208,711 logicznych odczytów (12 skanów)

OPCJA (DOŁĄCZ DO PĘTLI, GRUPA HASH):
11.008.698 odczytów logicznych (~ skanowanie na identyfikator przeglądarki (339))

Brak opcji:
11.008.775 odczytów logicznych (~ skanowanie na identyfikator przeglądarki (339))

Aktualizacja 2
Doskonałe odpowiedzi dla was wszystkich - dzięki! Trudno wybrać tylko jeden. Chociaż Martin był pierwszy, a Remus zapewnia doskonałe rozwiązanie, muszę przekazać go Kiwi, aby zajął się szczegółami :)

„Zastanawiam się, dlaczego optymalizator zapytań kiedykolwiek skorzystałby z planu, który obecnie wykonuje”.

Innymi słowy, pytanie brzmi: dlaczego następujący plan wygląda na optymalizator najtańszy w porównaniu z alternatywami (których jest wiele ).

Wewnętrzna strona złączenia zasadniczo uruchamia zapytanie o następującą postać dla każdej skorelowanej wartości BrowserID:

DECLARE @BrowserID smallint;

SELECT 
    tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
    tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);

Należy zauważyć, że szacowana liczba wierszy wynosi 185,220 (nie 289,013 ), ponieważ porównanie równości domyślnie wyklucza NULL(chyba że ANSI_NULLSjest OFF). Szacunkowy koszt powyższego planu wynosi 206,8 jednostek.

Dodajmy teraz TOP (1)klauzulę:

DECLARE @BrowserID smallint;

SELECT TOP (1)
    tfsph.BrowserID 
FROM dbo.tblFEStatsPaperHits AS tfsph 
WHERE 
    tfsph.BrowserID = @BrowserID 
OPTION (MAXDOP 1);

Szacowany koszt wynosi teraz 0,00452 jednostek. Dodanie górnego operatora fizycznego wyznacza cel rzędu 1 rzędu u górnego operatora. Powstaje zatem pytanie, jak uzyskać „cel rzędu” dla skanowania indeksu klastrowanego; to znaczy, ile wierszy powinno oczekiwać przetwarzanie, zanim jeden wiersz dopasuje BrowserIDpredykat?

Dostępne informacje statystyczne pokazują 166 różnych BrowserIDwartości (1 / [All Density] = 1 / 0,006024096 = 166). Costing zakłada, że ​​różne wartości są równomiernie rozmieszczone w fizycznych wierszach, więc cel wiersza w skrypcie indeksowania klastrowego jest ustawiony na 166,302 (uwzględniając zmianę liczności tabeli od czasu zebrania próbkowanych statystyk).

Szacunkowy koszt skanowania oczekiwanych 166 wierszy nie jest zbyt duża (nawet wykonany 339 razy, raz na każdej zmianie BrowserID) - pokazy Clustered Index zeskanować szacunkowy koszt 1.3219 jednostek, pokazujący efekt skalowania bramki wiersza. Nieskalowane koszty operatora we / wy i procesora są pokazane odpowiednio jako 153,931 i 52,8698 :

W praktyce jest bardzo mało prawdopodobne, aby pierwsze 166 wierszy skanowanych z indeksu (w dowolnej kolejności, w jakiej zostały zwrócone) zawierało jedną z możliwych BrowserIDwartości. Niemniej jednak DELETEplan kosztuje ogółem 1,40921 jednostek i z tego powodu jest wybierany przez optymalizator. Bart Duncan pokazuje inny przykład tego typu w ostatnim poście zatytułowanym Row Goals Gone Rogue .

Warto również zauważyć, że operator Top w planie wykonania nie jest powiązany z Anti Semi Join (w szczególności wspomniany przez Martina „zwarcie”). Możemy zacząć widzieć, skąd pochodzi Góra, najpierw wyłączając regułę eksploracji o nazwie GbAggToConstScanOrTop :

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

Ten plan ma szacunkowy koszt 364,912 i pokazuje, że Top zastąpił Grupę według agregatu (grupowanie według skorelowanej kolumny BrowserID). Agregacja nie wynika z nadmiarowości DISTINCTw tekście zapytania: jest to optymalizacja, którą można wprowadzić za pomocą dwóch reguł eksploracji: LASJNtoLASJNonDist i LASJOnLclDist . Wyłączenie tych dwóch również powoduje powstanie tego planu:

DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');

Ten plan ma szacowany koszt 40729,3 jednostek.

Bez transformacji z Grupowania według na górę optymalizator „naturalnie” wybiera plan łączenia z BrowserIDagregacją przed agregacją częściową:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers 
WHERE BrowserID NOT IN 
(
    SELECT DISTINCT BrowserID 
    FROM tblFEStatsPaperHits WITH (NOLOCK) 
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

I bez ograniczenia MAXDOP 1 plan równoległy:

Innym sposobem na „naprawienie” pierwotnego zapytania byłoby utworzenie brakującego indeksu na BrowserIDpodstawie raportów planu wykonania. Zagnieżdżone pętle działają najlepiej, gdy strona wewnętrzna jest indeksowana. Oszacowanie liczności dla połączeń częściowo jest najlepszym wyzwaniem. Brak odpowiedniego indeksowania (duża tabela nie ma nawet unikalnego klucza!) W ogóle nie pomoże.

Pisałem o tym więcej w Row Goals, Part 4: The Anti Join Anti Pattern .

Po uruchomieniu skryptu w celu utworzenia bazy danych zawierającej wyłącznie statystyki i zapytania w pytaniu otrzymuję następujący plan.

Tabele Kardynalności przedstawione w planie są

• tblFEStatsPaperHits: 48063400
• tblFEStatsBrowsers : 339

Szacuje się, że konieczne będzie wykonanie skanowania tblFEStatsPaperHits339 razy. Każdy skan ma skorelowany predykat, tblFEStatsBrowsers.BrowserID=tblFEStatsPaperHits.BrowserID AND tblFEStatsPaperHits.BrowserID IS NOT NULLktóry jest wypychany do operatora skanowania.

Plan nie oznacza jednak, że będzie 339 pełnych skanów. Ponieważ znajduje się pod operatorem antyprzyłączenia, gdy tylko pierwszy pasujący wiersz na każdym skanie zostanie znaleziony, może on zwierać resztę. Szacowany koszt poddrzewa dla tego węzła wynosi, 1.32603a cały plan kosztuje 1.41337.

W przypadku Hash Join daje to poniższy plan

Ogólny plan 418.415kosztuje (około 300 razy drożej niż plan zagnieżdżonych pętli) z pojedynczym pełnym skanowaniem indeksu klastrowego na tblFEStatsPaperHitskoszt 206.8samego. Porównaj to z 1.32603podanymi wcześniej szacunkami 339 skanów częściowych (Średni szacowany koszt częściowego skanowania = 0.003911592).

Oznaczałoby to, że każde częściowe skanowanie kosztuje 53 000 razy mniej niż pełne skanowanie. Gdyby kalkulacje były skalowane liniowo z liczbą wierszy, oznaczałoby to, że zakłada się, że średnio trzeba przetworzyć 900 wierszy w każdej iteracji, zanim znajdzie pasujący wiersz i może spowodować zwarcie.

Nie sądzę jednak, by koszty były skalowane w taki liniowy sposób. Myślę, że zawierają one również element stałego kosztu uruchomienia. Próbowanie różnych wartości TOPw poniższym zapytaniu

SELECT TOP 147 BrowserID 
FROM [dbo].[tblFEStatsPaperHits] 

147Daje najbliższy szacunkowy koszt poddrzewa na 0.003911592co 0.0039113. Tak czy inaczej, oczywiste jest, że opiera on kalkulację na założeniu, że każdy skan będzie musiał przetworzyć tylko niewielką część tabeli, rzędu setek rzędów, a nie milionów.

Nie jestem pewien, na jakiej dokładnie matematyce opiera się to założenie i tak naprawdę nie sumuje się ono z szacunkami liczby wierszy w pozostałej części planu (236 oszacowanych wierszy wychodzących z połączenia zagnieżdżonej pętli oznaczałoby, że było 236 przypadki, w których nie znaleziono w ogóle pasującego wiersza i wymagane było pełne skanowanie). Zakładam, że jest to tylko przypadek, w którym przyjęte założenia modelowania nieco upadają i pozostawiają plan zagnieżdżonych pętli znacznie zaniżony.

W mojej książce nawet jeden skan 50 mln wierszy jest niedopuszczalny ... Moją zwykłą sztuczką jest zmaterializowanie różnych wartości i przekazanie silnika na bieżąco:

create view [dbo].[vwFEStatsPaperHitsBrowserID]
with schemabinding
as
select BrowserID, COUNT_BIG(*) as big_count
from [dbo].[tblFEStatsPaperHits]
group by [BrowserID];
go

create unique clustered index [cdxVwFEStatsPaperHitsBrowserID] 
  on [vwFEStatsPaperHitsBrowserID]([BrowserID]);
go

To daje zmaterializowany indeks jeden wiersz na identyfikator przeglądarki, eliminując potrzebę skanowania 50 milionów wierszy. Silnik zachowa go dla Ciebie, a QO użyje go „tak, jak jest” w opublikowanym oświadczeniu (bez przepisywania podpowiedzi i zapytań).

Minusem jest oczywiście spór. Każda operacja wstawiania lub usuwania w tblFEStatsPaperHits(i wydaje mi się, że jest to tabela rejestrowania z dużymi wstawkami) będzie musiała serializować dostęp do danego BrowserID. Istnieją sposoby, które sprawiają, że jest to wykonalne (opóźnione aktualizacje, 2 etapowe rejestrowanie itp.), Jeśli chcesz kupić.