Dostaję skan, ale spodziewam się poszukiwania

Muszę zoptymalizować SELECTinstrukcję, ale SQL Server zawsze skanuje indeks zamiast wyszukiwania. Oto zapytanie, które oczywiście znajduje się w procedurze przechowywanej:

CREATE PROCEDURE dbo.something
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL    
AS

    SELECT [IdNumber], [Code], [Status], [Sex], 
           [FirstName], [LastName], [Profession], 
           [BirthDate], [HireDate], [ActiveDirectoryUser]
    FROM Employee
    WHERE (@Status IS NULL OR [Status] = @Status)
    AND 
    (
      @IsUserGotAnActiveDirectoryUser IS NULL 
      OR 
      (
        @IsUserGotAnActiveDirectoryUser IS NOT NULL AND       
        (
          @IsUserGotAnActiveDirectoryUser = 1 AND ActiveDirectoryUser <> ''
        )
        OR
        (
          @IsUserGotAnActiveDirectoryUser = 0 AND ActiveDirectoryUser = ''
        )
      )
    )

A to jest indeks:

CREATE INDEX not_relevent ON dbo.Employee
(
    [Status] DESC,
    [ActiveDirectoryUser] ASC
)
INCLUDE (...all the other columns in the table...); 

Plan:

Dlaczego SQL Server wybrał skanowanie? Jak mogę to naprawić?

Definicje kolumn:

[Status] int NOT NULL
[ActiveDirectoryUser] VARCHAR(50) NOT NULL

Parametry statusu mogą być:

NULL: all status,
1: Status= 1 (Active employees)
2: Status = 2 (Inactive employees)

IsUserGotAnActiveDirectoryUser może być:

NULL: All employees
0: ActiveDirectoryUser is empty for that employee
1: ActiveDirectoryUser  got a valid value (not null and not empty)

Nie sądzę, że skanowanie jest spowodowane przez wyszukiwanie pustego ciągu (i chociaż możesz dodać filtrowany indeks dla tego przypadku, pomoże to tylko bardzo specyficznym odmianom zapytania). Bardziej prawdopodobne jest, że padniesz ofiarą wąchania parametrów i jednego planu, który nie został zoptymalizowany dla wszystkich różnych kombinacji parametrów (i wartości parametrów), które będziesz podawać dla tego zapytania.

Nazywam to procedurą „zlewu kuchennego” , ponieważ oczekujesz, że jedno zapytanie zapewni wszystkie rzeczy, łącznie z zlewem kuchennym.

Mam filmy o moim rozwiązaniu tego tutaj i tutaj, a także blog na ten temat , ale w zasadzie najlepsze doświadczenie, jakie mam w przypadku takich zapytań, to:

• Zbuduj instrukcję dynamicznie - pozwoli ci to pominąć klauzule wspominające kolumny, dla których nie podano żadnych parametrów, i zapewni, że będziesz miał plan zoptymalizowany dokładnie dla rzeczywistych parametrów, które zostały przekazane z wartościami.
• UżycieOPTION (RECOMPILE) - zapobiega to wymuszaniu przez określone wartości parametrów niewłaściwego typu planu, co jest szczególnie przydatne, gdy masz zniekształcenie danych, złe statystyki lub gdy pierwsze wykonanie instrukcji używa nietypowej wartości, która doprowadzi do innego planu niż później i częściej egzekucje.
• Użyj opcji serweraoptimize for ad hoc workloads - zapobiega to zanieczyszczeniom pamięci podręcznej planu, które są używane tylko raz.

Włącz optymalizację pod kątem obciążeń ad hoc:

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Zmień procedurę:

ALTER PROCEDURE dbo.Whatever
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS
BEGIN 
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX) = N'SELECT [IdNumber], [Code], [Status], 
     [Sex], [FirstName], [LastName], [Profession],
     [BirthDate], [HireDate], [ActiveDirectoryUser]
   FROM dbo.Employee -- please, ALWAYS schema prefix
   WHERE 1 = 1';

   IF @Status IS NOT NULL
     SET @sql += N' AND ([Status]=@Status)'

   IF @IsUserGotAnActiveDirectoryUser = 1
     SET @sql += N' AND ActiveDirectoryUser <> ''''';
   IF @IsUserGotAnActiveDirectoryUser = 0
     SET @sql += N' AND ActiveDirectoryUser = ''''';

   SET @sql += N' OPTION (RECOMPILE);';

   EXEC sys.sp_executesql @sql, N'@Status INT, @Status;
END
GO

Gdy masz już obciążenie oparte na tym zestawie zapytań, które możesz monitorować, możesz analizować wykonania i sprawdzać, które z nich najbardziej skorzystałyby z dodatkowych lub różnych indeksów - możesz to zrobić z różnych punktów widzenia, od prostej ", która kombinacja parametry są podawane najczęściej? ” na „które zapytania mają najdłuższe czasy działania?” Nie możemy odpowiedzieć na te pytania na podstawie Twojego kodu, możemy jedynie zasugerować, że dowolny indeks będzie pomocny tylko dla podzbioru wszystkich możliwych kombinacji parametrów, które próbujesz wesprzeć. Na przykład jeśli@Statusma wartość NULL, wówczas żadne wyszukiwanie w stosunku do tego nieklastrowanego indeksu nie jest możliwe. Tak więc w przypadkach, gdy użytkownicy nie dbają o status, dostaniesz skan, chyba że masz indeks, który spełnia inne klauzule (ale taki indeks też nie będzie przydatny, biorąc pod uwagę bieżącą logikę zapytań - albo pusty ciąg, albo niepusty ciąg nie jest dokładnie selektywny).

W takim przypadku, w zależności od zestawu możliwych Statuswartości i rozkładu tych wartości, OPTION (RECOMPILE)może nie być to konieczne. Ale jeśli masz jakieś wartości, które dadzą 100 wierszy i niektóre wartości, które dadzą setki tysięcy, możesz chcieć tam (nawet przy koszcie procesora, który powinien być marginalny, biorąc pod uwagę złożoność tego zapytania), abyś mógł szukaj w jak największej liczbie przypadków. Jeśli zakres wartości jest wystarczająco skończony, możesz nawet zrobić coś trudnego z dynamicznym SQL, w którym powiesz: „Mam tę bardzo selektywną wartość @Status, więc kiedy ta konkretna wartość zostanie przekazana, dokonaj tej niewielkiej zmiany w tekście zapytania, aby jest to uważane za inne zapytanie i zoptymalizowane pod kątem tej wartości parametru ”.

Oświadczenie : Niektóre elementy w tej odpowiedzi mogą powodować wzdrygnięcie DBA. Podchodzę do tego z czystego punktu widzenia wydajności - jak uzyskać indeksowanie, gdy zawsze dostajesz indeksy.

Z tym nie ma mowy, oto idzie.

Twoje zapytanie to tak zwane „zapytanie dotyczące zlewu kuchennego” - pojedyncze zapytanie przeznaczone do spełnienia szeregu możliwych warunków wyszukiwania. Jeśli użytkownik ustawi @statuswartość, chcesz filtrować według tego stanu. Jeśli @statustak NULL, zwróć wszystkie statusy i tak dalej.

Powoduje to problemy z indeksowaniem, ale nie są one związane z możliwościami wyszukiwania, ponieważ wszystkie warunki wyszukiwania są „równe” kryteriom.

To jest do sargable:

WHERE [status]=@status

Nie jest to możliwe do sargowania, ponieważ SQL Server musi oceniać ISNULL([status], 0)dla każdego wiersza zamiast szukać pojedynczej wartości w indeksie:

WHERE ISNULL([status], 0)=@status

Odtworzyłem problem zlewu kuchennego w prostszej formie:

CREATE TABLE #work (
    A    int NOT NULL,
    B    int NOT NULL
);

CREATE UNIQUE INDEX #work_ix1 ON #work (A, B);

INSERT INTO #work (A, B)
VALUES (1,  1), (2,  1),
       (3,  1), (4,  1),
       (5,  2), (6,  2),
       (7,  2), (8,  3),
       (9,  3), (10, 3);

Jeśli spróbujesz wykonać następujące czynności, otrzymasz skanowanie indeksu, mimo że A jest pierwszą kolumną indeksu:

DECLARE @a int=4, @b int=NULL;

SELECT *
FROM #work
WHERE (@a IS NULL OR @a=A) AND
      (@b IS NULL OR @b=B);

Powoduje to jednak wyszukiwanie indeksu:

DECLARE @a int=4, @b int=NULL;

SELECT *
FROM #work
WHERE @a=A AND
      @b IS NULL;

Tak długo, jak używasz możliwej do zarządzania liczby parametrów (w twoim przypadku dwóch), prawdopodobnie możesz po prostu UNIONwyszukać kilka zapytań - w zasadzie wszystkie kombinacje kryteriów wyszukiwania. Jeśli masz trzy kryteria, będzie to bałagan, z czterema będzie to całkowicie niemożliwe do zarządzania. Zostałeś ostrzeżony.

DECLARE @a int=4, @b int=NULL;

SELECT *
FROM #work
WHERE @a=A AND
      @b IS NULL
UNION ALL
SELECT *
FROM #work
WHERE @a=A AND
      @b=B
UNION ALL
SELECT *
FROM #work
WHERE @a IS NULL AND
      @b=B
UNION ALL
SELECT *
FROM #work
WHERE @a IS NULL AND
      @b IS NULL;

Jednak, aby trzeci z tych czterech mógł korzystać z funkcji wyszukiwania indeksu, będziesz potrzebować drugiego indeksu (B, A). Oto, w jaki sposób zapytanie może wyglądać z tymi zmianami (w tym przez przeredagowanie zapytania, aby było bardziej czytelne).

DECLARE @Status int = NULL,
        @IsUserGotAnActiveDirectoryUser bit = NULL;

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName],
       [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE [Status]=@Status AND
      @IsUserGotAnActiveDirectoryUser IS NULL

UNION ALL

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName],
       [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE [Status]=@Status AND
      @IsUserGotAnActiveDirectoryUser=1 AND ActiveDirectoryUser<>''

UNION ALL

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName],
       [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE [Status]=@Status AND
      @IsUserGotAnActiveDirectoryUser=0 AND (ActiveDirectoryUser IS NULL OR ActiveDirectoryUser='')

UNION ALL

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName],
       [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE @Status IS NULL AND
      @IsUserGotAnActiveDirectoryUser IS NULL

UNION ALL

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName],
       [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE @Status IS NULL AND
      @IsUserGotAnActiveDirectoryUser=1 AND ActiveDirectoryUser<>''

UNION ALL

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName],
       [Profession], [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE @Status IS NULL AND
      @IsUserGotAnActiveDirectoryUser=0 AND (ActiveDirectoryUser IS NULL OR ActiveDirectoryUser='');

... plus będziesz potrzebować dodatkowego indeksu przy Employeeodwróconych dwóch kolumnach indeksu.

Dla kompletności powinienem wspomnieć, że x=@xdomyślnie oznacza to, że xnie może być, NULLponieważ NULLnigdy nie jest równy NULL. To trochę upraszcza zapytanie.

I tak, dynamiczna odpowiedź SQL Aarona Bertranda jest lepszym wyborem w większości przypadków (tj. Ilekroć możesz żyć z rekompilacjami).

Twoim podstawowym pytaniem wydaje się „dlaczego” i myślę, że możesz znaleźć odpowiedź około 55 minut na tę wspaniałą prezentację Adama Machanica na TechEd kilka lat temu.

Wspominam o 5 minutach w 55 minucie, ale cała prezentacja jest warta czasu. Jeśli spojrzysz na plan zapytań dla twojego zapytania, jestem pewien, że okaże się, że ma Residual Predicates dla wyszukiwania. Zasadniczo SQL nie „widzi” wszystkich części indeksu, ponieważ niektóre z nich są ukryte przez nierówności i inne warunki. Wynikiem jest skanowanie indeksu dla super zestawu opartego na predykacie. Ten wynik jest buforowany, a następnie ponownie skanowany przy użyciu predykatu resztkowego.

Sprawdź właściwości operatora skanowania (F4) i sprawdź, czy na liście właściwości znajdują się zarówno „Szukaj predykatu”, jak i „Predykat”.

Jak wskazali inni, zapytanie jest trudne do indeksowania. Pracowałem ostatnio nad wieloma podobnymi i każde wymagało innego rozwiązania. :(

Zanim zapytamy, czy wyszukiwanie indeksu jest preferowane zamiast skanowania indeksu, jedną z podstawowych zasad jest sprawdzenie, ile wierszy jest zwracanych w stosunku do łącznej liczby wierszy podstawowej tabeli. Na przykład jeśli spodziewasz się, że zapytanie zwróci 10 wierszy z 1 miliona wierszy, wyszukiwanie indeksu jest prawdopodobnie wysoce preferowane niż skanowanie indeksu. Jeśli jednak z kwerendy ma zostać zwróconych kilka tysięcy wierszy (lub więcej), wyszukiwanie indeksu niekoniecznie musi być preferowane.

Twoje zapytanie nie jest skomplikowane, więc jeśli możesz opublikować plan wykonania, możemy mieć lepsze pomysły na pomoc.

to tylko oryginalne sformatowane

DECLARE @Status INT = NULL,
        @IsUserGotAnActiveDirectoryUser BIT = NULL    

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName], [Profession],
       [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE (@Status IS NULL OR [Status]=@Status)  
AND (            @IsUserGotAnActiveDirectoryUser IS NULL 
      OR (       @IsUserGotAnActiveDirectoryUser IS NOT NULL 
           AND (     @IsUserGotAnActiveDirectoryUser = 1 
                 AND ActiveDirectoryUser <> '') 
           OR  (     @IsUserGotAnActiveDirectoryUser = 0 
                 AND ActiveDirectoryUser =  '')
         )
    )

to jest wersja - nie w 100% pewna, ale (może) spróbuj,
nawet jeden LUB prawdopodobnie będzie to problem,
który zepsuje się na ActiveDirectoryUser null

  WHERE isnull(@Status, [Status]) = [Status]
    AND (      (     isnull(@IsUserGotAnActiveDirectoryUser, 1) = 1 
                 AND ActiveDirectoryUser <> '' ) 
           OR  (     isnull(@IsUserGotAnActiveDirectoryUser, 0) = 0 
                 AND ActiveDirectoryUser =  '' )
        )