Istnieją trzy różne reguły optymalizatora, które mogą wykonać DISTINCToperację w powyższym zapytaniu. Poniższe zapytanie generuje błąd, który sugeruje, że lista jest wyczerpująca:
SELECT DISTINCT TOP 10 ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, QUERYRULEOFF GbAggToSort, QUERYRULEOFF GbAggToHS, QUERYRULEOFF GbAggToStrm);
Msg 8622, poziom 16, stan 1, wiersz 1
Procesor zapytań nie mógł wygenerować planu zapytania ze względu na wskazówki zdefiniowane w tym zapytaniu. Ponownie wprowadź zapytanie bez podawania wskazówek i bez użycia SET FORCEPLAN.
GbAggToSortimplementuje agregację według grup (odrębne) jako odrębny sort. Jest to operator blokujący, który odczyta wszystkie dane z danych wejściowych przed wygenerowaniem jakichkolwiek wierszy. GbAggToStrmimplementuje agregację według grup jako agregację strumieniową (która również wymaga sortowania danych wejściowych w tym przypadku). Jest to również operator blokujący. GbAggToHSimplementuje jako dopasowanie skrótu, co widzieliśmy w złym planie z pytania, ale może być implementowane jako dopasowanie skrótu (agregacja) lub dopasowanie skrótu (odrębny przepływ).
Operator dopasowania mieszania ( odrębny przepływ ) jest jednym ze sposobów rozwiązania tego problemu, ponieważ nie blokuje. Program SQL Server powinien być w stanie zatrzymać skanowanie, gdy znajdzie wystarczająco wyraźne wartości.
Operator logiczny Flow Distinct skanuje dane wejściowe, usuwając duplikaty. Podczas gdy operator Distinct zużywa wszystkie dane wejściowe przed wytworzeniem danych wyjściowych, operator Flow Distinct zwraca każdy wiersz otrzymany z danych wejściowych (chyba że ten wiersz jest duplikatem, w którym to przypadku jest odrzucany).
Dlaczego zapytanie w pytaniu używa dopasowania skrótu (agregacja) zamiast dopasowania skrótu (odrębny przepływ)? Ponieważ liczba różnych wartości zmienia się w tabeli, spodziewałbym się, że koszt zapytania dopasowania mieszania (odrębnego przepływu) zmniejszy się, ponieważ szacunkowa liczba wierszy, które musi przeskanować do tabeli powinna się zmniejszyć. Spodziewałbym się, że koszt planu dopasowania (agregacji) wzrośnie, ponieważ tabela skrótów, którą musi zbudować, będzie się powiększać. Jednym ze sposobów sprawdzenia tego jest stworzenie przewodnika po planach . Jeśli utworzę dwie kopie danych, ale zastosuję przewodnik po planie do jednego z nich, powinienem być w stanie porównać dopasowanie hash (agregacja) do dopasowania hash (odrębne) obok siebie z tymi samymi danymi. Pamiętaj, że nie mogę tego zrobić, wyłączając reguły optymalizatora zapytań, ponieważ ta sama reguła dotyczy obu planów ( GbAggToHS).
Oto jeden ze sposobów uzyskania przewodnika po planie, którego szukam:
DROP TABLE IF EXISTS X_PLAN_GUIDE_TARGET;
CREATE TABLE X_PLAN_GUIDE_TARGET (VAL VARCHAR(10) NOT NULL);
INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT CAST(N % 10000 AS VARCHAR(10))
FROM dbo.GetNums(10000000);
UPDATE STATISTICS X_PLAN_GUIDE_TARGET WITH FULLSCAN;
-- run this query
SELECT DISTINCT TOP 10 VAL FROM X_PLAN_GUIDE_TARGET OPTION (MAXDOP 1)
Pobierz uchwyt planu i użyj go, aby utworzyć przewodnik po planie:
-- plan handle is 0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000
SELECT qs.plan_handle, st.text FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%X[_]PLAN[_]GUIDE[_]TARGET%'
ORDER BY last_execution_time DESC;
EXEC sp_create_plan_guide_from_handle
'EVIL_PLAN_GUIDE',
0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000;
Przewodniki po planach działają tylko na dokładnym tekście zapytania, więc skopiujmy go z przewodnika po planach:
SELECT query_text
FROM sys.plan_guides
WHERE name = 'EVIL_PLAN_GUIDE';
Zresetuj dane:
TRUNCATE TABLE X_PLAN_GUIDE_TARGET;
INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);
Uzyskaj plan zapytania dla zapytania z zastosowanym przewodnikiem planu:
SELECT DISTINCT TOP 10 VAL FROM X_PLAN_GUIDE_TARGET OPTION (MAXDOP 1)
Ma to operator dopasowania mieszania (odrębny przepływ), który chcieliśmy z naszymi danymi testowymi. Zauważ, że SQL Server spodziewa się odczytać wszystkie wiersze z tabeli i że szacowany koszt jest dokładnie taki sam jak w przypadku planu z dopasowaniem mieszania (agregacja). Testy, które przeprowadziłem, zasugerowały, że koszty dla dwóch planów są identyczne, gdy cel wiersza dla planu jest większy lub równy liczbie różnych wartości, których SQL Server oczekuje od tabeli, co w tym przypadku można po prostu uzyskać z Statystyka. Niestety (dla naszego zapytania) optymalizator wybiera dopasowanie mieszające (agregujące) zamiast dopasowania mieszającego (odrębne dla przepływu), gdy koszty są takie same. Jesteśmy więc 0,0000001 magicznych jednostek optymalizujących z dala od pożądanego planu.
Jednym ze sposobów na zaatakowanie tego problemu jest zmniejszenie celu rzędu. Jeśli cel wiersza z punktu widzenia optymalizatora jest mniejszy niż wyraźna liczba wierszy, prawdopodobnie uzyskamy dopasowanie mieszania (odrębny przepływ). Można to zrobić za pomocą OPTIMIZE FORwskazówki dotyczącej zapytania:
DECLARE @j INT = 10;
SELECT DISTINCT TOP (@j) VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, OPTIMIZE FOR (@j = 1));
Dla tego zapytania optymalizator tworzy plan tak, jakby zapytanie wymagało tylko pierwszego wiersza, ale po wykonaniu zapytania odzyskuje pierwsze 10 wierszy. Na moim komputerze to zapytanie skanuje 892800 wierszy zi X_10_DISTINCT_HEAPkończy się w 299 ms przy 250 ms czasu procesora i 2537 logicznych odczytach.
Zauważ, że ta technika nie będzie działać, jeśli statystyki zgłaszają tylko jedną wyraźną wartość, co może się zdarzyć w przypadku próbkowanych statystyk w odniesieniu do wypaczonych danych. Jednak w takim przypadku jest mało prawdopodobne, aby dane były wystarczająco gęsto upakowane, aby uzasadnić zastosowanie takich technik. Nie możesz wiele stracić, skanując wszystkie dane w tabeli, zwłaszcza jeśli można to zrobić równolegle.
Innym sposobem na zaatakowanie tego problemu jest zwiększenie liczby oszacowanych odrębnych wartości, które SQL Server spodziewa się uzyskać z tabeli podstawowej. To było trudniejsze niż się spodziewano. Zastosowanie funkcji deterministycznej nie może zwiększyć wyraźnej liczby wyników. Jeśli optymalizator zapytań jest świadomy tego faktu matematycznego (niektóre testy sugerują, że jest to przynajmniej do naszych celów), wówczas zastosowanie funkcji deterministycznych ( obejmujących wszystkie funkcje łańcuchowe ) nie zwiększy szacowanej liczby różnych wierszy.
Wiele niedeterministycznych funkcji też nie działało, w tym oczywiste wybory NEWID()i RAND(). Jednak LAG()sztuczka dla tego zapytania. Optymalizator zapytań oczekuje 10 milionów różnych wartości w stosunku do LAGwyrażenia, które zachęci do planu dopasowania mieszania (odrębnego przepływu) :
SELECT DISTINCT TOP 10 LAG(VAL, 0) OVER (ORDER BY (SELECT NULL)) AS ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);
Na moim komputerze to zapytanie skanuje 892800 wierszy zi X_10_DISTINCT_HEAPkończy się w 1165 ms z 1109 ms czasu procesora i 2537 odczytami logicznymi, więc LAG()dodaje sporo względnego obciążenia. @Paul White zasugerował, aby spróbować przetwarzania w trybie wsadowym dla tego zapytania. Na SQL Server 2016 możemy uzyskać przetwarzanie w trybie wsadowym nawet z MAXDOP 1. Jednym ze sposobów uzyskania przetwarzania w trybie wsadowym dla tabeli magazynu wierszy jest dołączenie do pustego CCI w następujący sposób:
CREATE TABLE #X_DUMMY_CCI (ID INT NOT NULL);
CREATE CLUSTERED COLUMNSTORE INDEX X_DUMMY_CCI ON #X_DUMMY_CCI;
SELECT DISTINCT TOP 10 VAL
FROM
(
SELECT LAG(VAL, 1) OVER (ORDER BY (SELECT NULL)) AS VAL
FROM X_10_DISTINCT_HEAP
LEFT OUTER JOIN #X_DUMMY_CCI ON 1 = 0
) t
WHERE t.VAL IS NOT NULL
OPTION (MAXDOP 1);
Ten kod powoduje powstanie tego planu zapytań .
Paul zwrócił uwagę, że muszę zmienić zapytanie, aby użyć, LAG(..., 1)ponieważ LAG(..., 0)nie wydaje się, że kwalifikuje się do optymalizacji agregacji okien. Ta zmiana skróciła czas, który upłynął do 520 ms, a czas procesora do 454 ms.
Zauważ, że LAG()podejście nie jest najbardziej stabilne. Jeśli Microsoft zmieni założenie o unikatowości funkcji, może ona przestać działać. Ma inny szacunek dla starszej wersji CE. Również ten rodzaj optymalizacji w stosunku do sterty nie jest konieczny, dobry pomysł. Jeśli tabela zostanie przebudowana, możliwe jest zakończenie w najgorszym przypadku, w którym prawie wszystkie wiersze muszą zostać odczytane z tabeli.
Przeciwko tabeli z unikalną kolumną (taką jak przykład indeksu klastrowego w pytaniu) mamy lepsze opcje. Na przykład możemy oszukać optymalizator, używając SUBSTRINGwyrażenia, które zawsze zwraca pusty ciąg. SQL Server nie uważa, że SUBSTRINGzmieni liczbę odrębnych wartości, więc jeśli zastosujemy ją do unikalnej kolumny, takiej jak PK, wówczas szacunkowa liczba różnych wierszy wynosi 10 milionów. Poniższe zapytanie pobiera operator dopasowania skrótu (odrębny przepływ):
SELECT DISTINCT TOP 10 VAL + SUBSTRING(CAST(PK AS VARCHAR(10)), 11, 1)
FROM X_10_DISTINCT_CI
OPTION (MAXDOP 1);
Na moim komputerze to zapytanie skanuje 900000 wierszy zi X_10_DISTINCT_CIkończy się w 333 ms przy 297 ms czasu procesora i 3011 logicznych odczytach.
Podsumowując, optymalizator zapytań wydaje się zakładać, że wszystkie wiersze będą odczytywane z tabeli dla SELECT DISTINCT TOP Nzapytań, gdy N> = liczba szacowanych odrębnych wierszy z tabeli. Operator dopasowania skrótu (agregujący) może mieć taki sam koszt jak operator dopasowania skrótu (odrębny dla przepływu), ale optymalizator zawsze wybiera operator agregacji. Może to prowadzić do niepotrzebnych odczytów logicznych, gdy w pobliżu początku skanowania tabeli znajduje się wystarczająco wyraźna wartość. Dwa sposoby na nakłonienie optymalizatora do użycia operatora dopasowania mieszania (odrębnego przepływu) to obniżenie celu wiersza za pomocą OPTIMIZE FORpodpowiedzi lub zwiększenie szacunkowej liczby różnych wierszy za pomocą LAG()lub SUBSTRINGw unikalnej kolumnie.
