Skąd SQL Server wie, że predykaty są skorelowane?

Podczas diagnozowania zapytań SQL Server 2008 R2 ze słabym oszacowaniem liczności (pomimo prostego indeksowania, aktualnych statystyk itp.), A zatem słabymi planami zapytań, znalazłem być może powiązany artykuł KB: POPRAWKA: Niska wydajność po uruchomieniu zapytania który zawiera skorelowane predykaty AND w SQL Server 2008 lub SQL Server 2008 R2 lub SQL Server 2012

Mogę zgadnąć, co artykuł KB oznacza „skorelowany”, np. Predykat nr 2 i predykat nr 1 w dużej mierze są ukierunkowane na te same wiersze.

Ale nie wiem, skąd SQL Server wie o tych korelacjach. Czy tabela potrzebuje indeksu wielokolumnowego zawierającego kolumny z obu predykatów? Czy SQL używa statystyk do sprawdzenia, czy wartości z jednej kolumny są skorelowane z inną? Czy jest używana inna metoda?

Proszę o to z dwóch powodów:

1. aby ustalić, które z moich tabel i zapytań można poprawić za pomocą tej poprawki
2. wiedzieć, co powinienem zrobić w indeksowaniu, statystykach itp., aby wpłynąć na # 1

Rozważ prosty plan zapytania i wykonania AdventureWorks przedstawiony poniżej. Zapytanie zawiera predykaty związane z AND. Szacunkowa liczność optymalizatora wynosi 41 211 wierszy:

-- Estimate 41,211 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

Korzystanie z domyślnych statystyk

Biorąc pod uwagę tylko statystyki jednokolumnowe, optymalizator dokonuje tego oszacowania, szacując liczność dla każdego predykatu osobno i mnożąc wynikowe selektywności razem. Ta heurystyka zakłada, że ​​predykaty są całkowicie niezależne.

Podział zapytania na dwie części ułatwia obliczenia:

-- Estimate 68,336.4 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336;

Tabela Historia transakcji zawiera łącznie 113 443 wierszy, więc oszacowanie 68 336,4 stanowi selektywność 68336,4 / 113443 = 0,60238533 dla tego predykatu. Oszacowanie to jest uzyskiwane przy użyciu informacji histogramu dla TransactionIDkolumny i stałych wartości określonych w zapytaniu.

-- Estimate 68,413 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

Ten predykat ma oszacowaną selektywność na poziomie 68413.0 / 113443 = 0,60306056 . Ponownie jest on obliczany na podstawie stałych wartości predykatu i histogramu TransactionDateobiektu statystycznego.

Zakładając, że predykaty są całkowicie niezależne, możemy oszacować selektywność dwóch predykatów razem, mnożąc je razem. Ostateczna ocena liczności jest uzyskiwana przez pomnożenie wynikowej selektywności przez 113 443 wierszy w tabeli podstawowej:

0,60238533 * 0,60306056 * 113443 = 41210.987

Po zaokrągleniu jest to 41 211 danych szacunkowych widocznych w pierwotnym zapytaniu (optymalizator używa również matematyki zmiennoprzecinkowej wewnętrznie).

Niezbyt dobre oszacowanie

TransactionIDI TransactionDatekolumny mają ścisły związek w danych AdventureWorks zestaw (jak monotonicznie rosnących kluczy i kolumny dat często robią). Korelacja ta oznacza naruszenie założenia niezależności. W rezultacie plan zapytań po wykonaniu zawiera 68 095 wierszy zamiast szacowanych 41 211:

Flaga śledzenia 4137

Włączenie tej flagi śledzenia zmienia heurystykę używaną do łączenia predykatów. Zamiast zakładać całkowitą niezależność, optymalizator uważa, że ​​selektywność dwóch predykatów jest na tyle bliska, że ​​można je skorelować:

-- Estimate 68,336.4
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13'
OPTION (QUERYTRACEON 4137);

Przypomnijmy, że TransactionIDsam predykat oszacował 68 336,4 wierszy, a TransactionDatesam predykat oszacował 68 413 wierszy. Optymalizator wybrał niższą z tych dwóch wartości szacunkowych zamiast pomnożyć selektywności.

Jest to oczywiście inna heurystyka, ale taka, która może pomóc poprawić oszacowania dla zapytań o skorelowanych ANDpredykatach. Każdy predykat jest brany pod uwagę pod kątem możliwej korelacji i dokonano innych korekt, gdy w ANDgrę wchodzi wiele klauzul, ale ten przykład służy do pokazania jego podstaw.

Statystyka wielokolumnowa

Mogą one pomóc w zapytaniach z korelacjami, ale informacje o histogramie nadal opierają się wyłącznie na wiodącej kolumnie statystyk. Poniższe statystyki kandydatów na wiele kolumn różnią się zatem w istotny sposób:

CREATE STATISTICS
    [stats Production.TransactionHistory TransactionID TransactionDate]
ON Production.TransactionHistory
    (TransactionID, TransactionDate);

CREATE STATISTICS
    [stats Production.TransactionHistory TransactionDate TransactionID]
ON Production.TransactionHistory
    (TransactionDate, TransactionID);

Biorąc tylko jeden z nich, widzimy, że jedyną dodatkową informacją są dodatkowe poziomy gęstości „wszystkiego”. Histogram nadal zawiera tylko szczegółowe informacje o TransactionDatekolumnie.

DBCC SHOW_STATISTICS
    (
        'Production.TransactionHistory', 
        'stats Production.TransactionHistory TransactionDate TransactionID'
    );

Po wprowadzeniu tych wielokolumnowych statystyk ...

SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

... plan wykonania pokazuje oszacowanie, które jest dokładnie takie samo jak wtedy, gdy dostępne były tylko statystyki z jedną kolumną: