Wskazówka dotycząca liczności programu SQL Server

Czy istnieje sposób „wstrzyknięcia” oszacowania liczności do optymalizatora SQL Server (dowolna wersja)?

tj. coś podobnego do wskazówki dotyczącej liczności Oracle.

Moja motywacja wynika z artykułu „ Jak dobre są naprawdę optymalizatory zapytań? ^[1] , gdzie testują wpływ estymatora liczności na wybór złego planu. Dlatego wystarczyłbym, aby zmusić SQL Server do „oszacowania” liczności właśnie w przypadku złożonych zapytań.

^[1] Leis, Viktor i in. „Jak dobre są naprawdę optymalizatory zapytań?”
Postępowanie w sprawie VLDB Endowment 9.3 (2015): 204-215.

Możesz uzyskać coś podobnego do CARDINALITYwskazówki Oracle, używając strategicznie TOPi zdefiniowanej przez użytkownika funkcji o nazwie MANY() opracowanej przez Adama Machanica . Przeanalizujmy kilka przykładów. Korzystam z darmowej bazy danych AdventureWorks. Załóżmy, że naprawdę muszę kontrolować liczbę wierszy zwracanych przez thtabelę pochodną w następującym zapytaniu:

SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
) th ON p.ProductID = th.ProductID;

W tej chwili otrzymuję oszacowanie 113443 wierszy:

Jeśli muszę obniżyć szacunek od th, mogę użyć TOPwraz ze OPTIMIZE FORwskazówką dotyczącą zapytania, aby ustawić cel wiersza. Oto jeden ze sposobów, aby to zrobić:

DECLARE @row_goal BIGINT = 9223372036854775807;
SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1));

Widzimy, że szacunek jest tylko 1 wiersz:

Ustawić @row_goalna możliwie największej BIGINTwartości w celu uniknięcia zmiany wyników. OPTIMIZE FORPodpowiedź zapytanie instruuje optymalizator optymalizator kwerendy jakby @row_goalwynosi 1. będę uzyskać takie same wyniki, ale zapytanie zostanie zoptymalizowany inaczej.

Zwiększenie szacunku liczności jest trudniejsze. Nie możemy po prostu zwiększyć wartości, TOPponieważ optymalizator zda sobie sprawę, że nie zwróci wystarczającej liczby wierszy. Możemy jednak użyć MANY()funkcji, aby dodać wiersze do oszacowania. Zauważ, że MANY()funkcja zawsze zwróci 0 wierszy, ale oszacowanie wiersza z niej zmienia się wraz z parametrem wejściowym. Załóżmy, że musisz zwiększyć oszacowanie wiersza z tabeli pochodnej o 10X. Jednym ze sposobów na osiągnięcie tego jest:

SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (9223372036854775807) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
    LEFT OUTER JOIN dbo.Many(10) AS m ON 1=1
) th ON p.ProductID = th.ProductID;

Widzimy, że szacunkowa wartość wynosi 10-krotność tabeli podstawowej:

Nadmiar TOPzostał dodany, aby zapobiec przesuwaniu się tabel przez optymalizator. Bez niego MANY()funkcję można zastosować w niewłaściwym miejscu w planie.

Możliwe jest połączenie tych dwóch technik, jeśli chcesz precyzyjnie przecenić, zamiast pomnożyć liczbę wierszy przez współczynnik. Załóżmy na przykład, że naprawdę potrzebujesz oszacowania tabeli pochodnej na dokładnie 1000000 wierszy. Jednym ze sposobów na osiągnięcie tego jest:

DECLARE @row_goal BIGINT = 9223372036854775807;

SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
    LEFT OUTER JOIN dbo.Many(10) AS m ON
        1=1
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1000000));

Widzimy, że oszacowanie wynosi 1000000 wierszy:

Muszę cię ostrzec, że są to zaawansowane techniki, które często nie są potrzebne do optymalizacji zapytań. Jeśli chcesz dowiedzieć się więcej, polecam obejrzenie Celów starcia przedstawionych przez Adama Machanica.

dbo. wiele funkcji

-- By Adam Machanic, reproduced with permission
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Many' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo')
    DROP FUNCTION dbo.Many
GO
CREATE FUNCTION dbo.Many(@n INT)
RETURNS TABLE AS
RETURN
(
    WITH
    a(x) AS
    (
        SELECT
            *
        FROM
        (
            VALUES
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
        ) AS x0(x)
    )
    SELECT TOP(@n)
        1 AS x
    FROM
        a AS a1,
        a AS a2
    WHERE
        a1.x % 2 = 0
)
GO

Nie ma możliwości wstrzyknięcia oszacowania liczności bezpośrednio do optymalizatora, ale w zależności od tego, co chcesz osiągnąć, istnieje kilka opcji.

Możesz użyć OPTION (FAST N)podpowiedzi zapytania, aby wprowadzić cele wierszy i ewentualnie przepisać zapytanie za pomocą CTE lub podkwerend do wstrzyknięciaTOP...ORDER BY cele wierszy w różnych częściach planu wykonania, ale nie jestem pewien, jak efektywne będzie Twoje wynikowe zapytanie po uruchomieniu bawić się bardziej złożonymi konstrukcjami.

Zobacz Inside the Optimizer: Row Goals In Depth uzyskać dokładniejsze wyjaśnienie, .

Jeśli chcesz wpłynąć na operatorów wybieranych przez optymalizator, nie musisz próbować podawać oszacowań liczności, ale możesz użyć takich rzeczy jak OPTION (MERGE JOIN)lubOPTION (HASH JOIN) na przykład, aby wymusić fizyczne przyłączenie operatorów.

W tym artykule opisano bardziej szczegółowo, jak wpływać na plan za pomocą podpowiedzi: Kontrolowanie planów wykonania za pomocą podpowiedzi

Jeśli chcesz naprawić plan, możesz także skorzystać z przewodnika po planach.

Ponownie nie jest jasne, jaki jest twój rzeczywisty przypadek użycia, a przebieg może się różnić przy użyciu tych technik. W wielu przypadkach lepiej jest po prostu zdecydować optymalizator i upewnić się, że masz aktualne statystyki, aby optymalizator mógł podjąć świadomą decyzję.

Odpowiednia sugestia Microsoft Connect: Pozwól na wskazanie wskazówki dotyczącej selektywności filtru w zapytaniach xor88. Microsoft odpowiedział:

Dziękujemy za opinię. Widzę potencjalną korzyść z tego. Zasadniczo staramy się, aby nasze automatyczne zachowanie było tak dobre, jak to możliwe, i unikamy potrzeby tego rodzaju wskazówek, ale oczywiście mamy wiele innych wskazówek. Rozważymy to w przyszłej wersji, ale wykraczałoby to poza wersję Denali (11.0).

Z pozdrowieniami,
Eric Hanson
Program Manager
Przetwarzanie zapytań SQL Server

Za pomocą OPTIMIZE FORpodpowiedzi do zapytania programu SQL Server można wymusić oszacowanie liczności na podstawie podpowiedzi zamiast korzystać z rzeczywistej wartości (parametrów) lub nieznanej wartości (zmiennych) podczas kompilacji. Aby uzyskać szczegółowe informacje, zobacz temat Wskazówki dotyczące zapytań w dokumentacji programu SQL Server.

Na przykład poniższe zapytanie oszacuje liczbę wierszy na podstawie histogramu statystyk na podstawie podanych wartości zamiast ogólnej średniej liczności, tak jak w przypadku zmiennych lokalnych.

DECLARE 
      @StartDate datetime = '20150101'
    , @EndDate datetime = '20150102';
SELECT *
FROM dbo.Example
WHERE
    DateColumn BETWEEN  @StartDate AND @EndDate
OPTION(OPTIMIZE FOR(@StartDate = '20100101', @EndDate='20100101'));

Podobnie podpowiedź można zastosować do parametrów, dzięki czemu szacunki są oparte na histogramie statystyk z podanych wartości zamiast rzeczywistych wartości parametrów podczas kompilacji.

DECLARE 
      @StartDate datetime = '20150101'
    , @EndDate datetime = '20150102';
EXECUTE sp_executesql N'SELECT *
        FROM dbo.Example
        WHERE
            DateColumn BETWEEN  @StartDate AND @EndDate
        OPTION(OPTIMIZE FOR(@StartDate = ''20100101'', @EndDate=''20100101''));'
    , N'@StartDate datetime, @EndDate datetime'
    , @StartDate = @StartDate
    , @EndDate = @EndDate;

Słowo UNKNOWNkluczowe można podać zamiast literału w podpowiedzi, aby użyć ogólnej średniej liczności zamiast szacowania na podstawie rzeczywistej wartości parametru i histogramu statystyki.