Indeks SEEK nie jest używany, chyba że OPCJA (RECOMPILE)?

(Pytanie przeniesione z SO)

Mam tabelę (dane fikcyjne) z indeksem klastrowym zawierającym 2 kolumny:

Teraz uruchamiam te dwa zapytania:

declare 
@productid int =1 , 
@priceid  int = 1




SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)  


SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

Rzeczywisty plan wykonania obu zapytań to:

Jak widać, pierwszy korzysta ze SKANOWANIA, a drugi SZUKAJ.

Jednak - dodanie OPTION (RECOMPILE)do pierwszego zapytania spowodowało, że plan wykonania również używał SEEK:

Przyjaciele na czacie DBA powiedzieli mi, że:

W zapytaniu @ productid = 1, co oznacza, że ​​(productID = @ productID LUB @productID IS NULL) można uprościć (productID = @ productID). Pierwszy wymaga skanu do pracy z dowolną wartością @productID, drugi może użyć funkcji seek. Tak więc, gdy użyjesz RECOMPILE, SQL Server sprawdzi, jaką wartość faktycznie masz w @productID i przygotuje najlepszy plan. Przy wartości innej niż null w @productID wyszukiwanie jest najlepsze. Jeśli wartość @productID jest nieznana, plan musi pasować do każdej możliwej wartości w @productID, która wymagałaby skanowania. Uwaga: Opcja (RECOMPILE) wymusi rekompilację planu za każdym razem, gdy go uruchomisz, co doda kilka milisekund do każdego wykonania. Jest to jednak problem tylko wtedy, gdy zapytanie jest uruchamiane bardzo często.

Również :

Jeśli @productID ma wartość null, jakiej wartości byś szukał? Odpowiedź: nie ma czego szukać. Wszystkie wartości się kwalifikują.

Rozumiem, że OPTION (RECOMPILE)zmusza program SQL Server do zobaczenia, jakie rzeczywiste wartości mają parametry, i do sprawdzenia, czy można go poszukać.

Ale teraz tracę korzyści z kompilacji z wyprzedzeniem.

Pytanie

IMHO - SKANOWANIE nastąpi tylko wtedy, gdy parametr jest pusty.
W porządku - pozwól, aby SQL SERVER stworzył plan wykonania dla SCAN.
ALE jeśli SQL Server widzi, że uruchamiam to zapytanie wiele razy z wartościami 1,1, to dlaczego nie tworzy INNEGO planu wykonania i nie używa do tego SEEK?

AFAIK - SQL tworzy plan wykonania dla najbardziej trafionych zapytań .

• Dlaczego SQL SERVER nie zapisuje planu wykonania dla:

  @productid int =1 , @priceid int = 1

(Uruchamiam go wiele razy z tymi wartościami)

• Czy można zmusić SQL do zachowania tego planu wykonania (który używa SEEK) - do przyszłego wywołania?

Pełny skrypt tworzenia tabeli + dane

Podsumowując niektóre główne punkty naszej dyskusji na czacie :

Ogólnie rzecz biorąc, SQL Server buforuje jeden plan dla każdej instrukcji . Ten plan musi być ważny dla wszystkich możliwych przyszłych wartości parametrów .

Nie można buforować planu wyszukiwania dla zapytania, ponieważ ten plan nie byłby ważny, jeśli na przykład @productid ma wartość null.

W niektórych przyszłych wersjach SQL Server może obsługiwać jeden plan, który dynamicznie wybiera pomiędzy skanowaniem a wyszukiwaniem, w zależności od wartości parametrów środowiska wykonawczego, ale nie jest to coś, co mamy dzisiaj.

Ogólna klasa problemu

Twoje zapytanie jest przykładem wzorca zwanego inaczej zapytaniem „złap wszystko” lub „wyszukiwanie dynamiczne”. Istnieją różne rozwiązania, z których każde ma swoje zalety i wady. W nowoczesnych wersjach SQL Server (2008+) główne opcje to:

• IF Bloki
• OPTION (RECOMPILE)
• Dynamiczny SQL za pomocą sp_executesql

Najbardziej wszechstronną pracą na ten temat jest prawdopodobnie Erland Sommarskog, który jest zawarty w źródłach na końcu tej odpowiedzi. Nie można uciec od złożonych zawiłości, dlatego trzeba poświęcić trochę czasu na wypróbowanie każdej opcji, aby zrozumieć kompromisy w każdym przypadku.

IF Bloki

Aby zilustrować IFrozwiązanie blokowe dla konkretnego przypadku w pytaniu:

IF @productid IS NOT NULL AND @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid
        AND T.priceID = @priceid;
END;
ELSE IF @productid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid;
END;
ELSE IF @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.priceID = @priceid;
END;
ELSE
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T;
END;

Zawiera osobną instrukcję dla czterech możliwych przypadków zerowych lub zerowych dla każdego z dwóch parametrów (lub zmiennych lokalnych), więc istnieją cztery plany.

Istnieje potencjalny problem z wąchaniem parametrów, który może wymagać OPTIMIZE FORpodpowiedzi do każdego zapytania. Zobacz sekcję referencji, aby poznać tego rodzaju subtelności.

Przekompiluj

Jak wspomniano powyżej w pytaniu, możesz również dodać OPTION (RECOMPILE)wskazówkę, aby uzyskać nowy plan (wyszukiwanie lub skanowanie) przy każdym wywołaniu. Biorąc pod uwagę stosunkowo niską częstotliwość połączeń w twoim przypadku (średnio co dziesięć sekund, z czasem kompilacji poniżej milisekundy) wydaje się prawdopodobne, że ta opcja będzie dla Ciebie odpowiednia:

SELECT
    T.productID,
    T.priceID
FROM dbo.Transactions AS T
WHERE
    (T.productID = @productid OR @productid IS NULL)
    AND (T.priceID = @priceid OR @priceid IS NULL)
OPTION (RECOMPILE);

Możliwe jest również łączenie funkcji z powyższych opcji w kreatywny sposób, aby w pełni wykorzystać zalety każdej metody, przy jednoczesnym zminimalizowaniu wad. Naprawdę nie ma skrótu do szczegółowego zrozumienia tych rzeczy, a następnie dokonania świadomego wyboru popartego realistycznymi testami.

Dalsza lektura

• Wąchanie parametrów, osadzanie i RECOMPILEopcje
• Warunki dynamicznego wyszukiwania w T-SQL autorstwa Erlanda Sommarskoga
• Procedury wysokiej wydajności autorstwa Kimberly Tripp