Dlaczego program SQL Server używa lepszego planu wykonania, gdy wstawiam zmienną?

Mam zapytanie SQL, które próbuję zoptymalizować:

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'

SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

MyTable ma dwa indeksy:

CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)

Kiedy wykonuję zapytanie dokładnie tak, jak napisano powyżej, SQL Server skanuje pierwszy indeks, co daje 189,703 logicznych odczytów i 2-3 sekundowy czas trwania.

Kiedy wstawiam @Idzmienną i ponownie wykonuję zapytanie, SQL Server szuka drugiego indeksu, co daje tylko 104 logiczne odczyty i 0,001 sekundy (w zasadzie natychmiastowe).

Potrzebuję zmiennej, ale chcę, aby SQL używał dobrego planu. Jako rozwiązanie tymczasowe podałem wskazówkę dotyczącą zapytania, a zapytanie jest w zasadzie natychmiastowe. Jednak staram się trzymać z dala od wskazówek indeksu, jeśli to możliwe. Zazwyczaj zakładam, że jeśli optymalizator zapytań nie jest w stanie wykonać swojej pracy, jest coś, co mogę zrobić (lub przestać to robić), aby mu pomóc bez wyraźnego mówienia, co robić.

Dlaczego więc SQL Server oferuje lepszy plan, kiedy wstawiam zmienną?

W SQL Server istnieją trzy popularne formy predykatów nieprzyłączania:

O wartości dosłownej :

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = 1;

Z parametrem :

CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;
END;

Z lokalną zmienną :

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

Wyniki

Kiedy używasz wartości dosłownej , a twój plan nie jest a) Trywialny i b) Prosty sparametryzowany lub c) nie masz włączonej wymuszonej parametryzacji , optymalizator tworzy bardzo specjalny plan tylko dla tej wartości.

Gdy użyjesz parametru , optymalizator utworzy plan dla tego parametru (nazywa się to wąchaniem parametru ), a następnie użyje go ponownie, nie będzie wskazówek dotyczących ponownej kompilacji, wykluczenia pamięci podręcznej planu itp.

Kiedy używasz zmiennej lokalnej , optymalizator tworzy plan dla ... Coś .

Jeśli chcesz uruchomić to zapytanie:

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

Plan wyglądałby tak:

Szacowana liczba wierszy dla tej zmiennej lokalnej wyglądałaby następująco:

Mimo że zapytanie zwraca liczbę 4 744 427.

Zmienne lokalne, ponieważ nie są znane, nie używają „dobrej” części histogramu do szacowania liczności. Używają przypuszczeń opartych na wektorze gęstości.

SELECT 5.280389E-05 * 7250739 AS [poo]

To da ci 382.86722457471przypuszczenie, jakie podejmie optymalizator.

Te nieznane domysły są zwykle bardzo złymi domysłami i często mogą prowadzić do złych planów i złych wyborów indeksu.

Naprawić to?

Ogólnie dostępne opcje to:

• Kruche wskazówki dotyczące indeksu
• Potencjalnie drogie wskazówki dotyczące ponownej kompilacji
• Sparametryzowany dynamiczny SQL
• Procedura składowana
• Popraw bieżący indeks

Twoje opcje to w szczególności:

Ulepszenie bieżącego indeksu oznacza rozszerzenie go na wszystkie kolumny potrzebne w zapytaniu:

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);

Zakładając, że Idwartości są dość selektywne, da ci to dobry plan i pomoże optymalizatorowi, dając mu „oczywistą” metodę dostępu do danych.

Więcej lektur

Więcej informacji na temat osadzania parametrów można znaleźć tutaj:

• Parametr Sniffing, Embedding i RECOMPILE Options autorstwa Paula White'a
• Dlaczego źle dostosowujesz przechowywane procedury (problem ze zmiennymi lokalnymi) , Kendra Little

Zakładam, że masz wypaczone dane, że nie chcesz używać wskazówek zapytań, aby zmusić optymalizator do zrobienia, i że musisz uzyskać dobrą wydajność dla wszystkich możliwych wartości wejściowych @Id. Możesz uzyskać gwarancję, że plan zapytań wymaga tylko kilku garści logicznych odczytów dla dowolnej możliwej wartości wejściowej, jeśli chcesz utworzyć następującą parę indeksów (lub ich odpowiednik):

CREATE INDEX GetMinSomeTimestamp ON dbo.MyTable (Id, SomeTimestamp) WHERE SomeBit = 1;
CREATE INDEX GetMaxSomeInt ON dbo.MyTable (Id, SomeInt) WHERE SomeBit = 1;

Poniżej znajdują się moje dane testowe. Umieściłem 13 M wierszy w tabeli i sprawiłem, że połowa z nich ma wartość '3A35EA17-CE7E-4637-8319-4C517B6E48CA'dla Idkolumny.

DROP TABLE IF EXISTS dbo.MyTable;

CREATE TABLE dbo.MyTable (
    Id uniqueidentifier,
    SomeTimestamp DATETIME2,
    SomeInt INT,
    SomeBit BIT,
    FILLER VARCHAR(100)
);

INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT NEWID(), CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT '3A35EA17-CE7E-4637-8319-4C517B6E48CA', CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

To zapytanie może początkowo wyglądać trochę dziwnie:

DECLARE @Id UNIQUEIDENTIFIER = '3A35EA17-CE7E-4637-8319-4C517B6E48CA'

SELECT
  @Id,
  st.SomeTimestamp,
  si.SomeInt
FROM (
    SELECT TOP (1) SomeInt, Id
    FROM dbo.MyTable
    WHERE Id = @Id
    AND SomeBit = 1
    ORDER BY SomeInt DESC
) si
CROSS JOIN (
    SELECT TOP (1) SomeTimestamp, Id
    FROM dbo.MyTable
    WHERE Id = @Id
    AND SomeBit = 1
    ORDER BY SomeTimestamp ASC
) st;

Zaprojektowano go, aby skorzystać z uporządkowania indeksów i znaleźć wartość minimalną lub maksymalną za pomocą kilku logicznych odczytów. CROSS JOINJest tam, aby uzyskać poprawne wyniki, gdy nie ma żadnych wierszy pasujących do @Idwartości. Nawet jeśli odfiltruję najpopularniejszą wartość w tabeli (pasującą do 6,5 miliona wierszy), otrzymam tylko 8 logicznych odczytów:

Tabela „MyTable”. Liczba skanów 2, logiczne odczyty 8

Oto plan zapytań:

Oba indeksy szukają 0 lub 1 wierszy. Jest niezwykle wydajny, ale utworzenie dwóch indeksów może być przesadą w Twoim scenariuszu. Zamiast tego możesz rozważyć następujący indeks:

CREATE INDEX CoveringIndex ON dbo.MyTable (Id) INCLUDE (SomeTimestamp, SomeInt) WHERE SomeBit = 1;

Teraz plan zapytania dla pierwotnego zapytania (z opcjonalną MAXDOP 1wskazówką) wygląda nieco inaczej:

Kluczowe wyszukiwania nie są już konieczne. Dzięki lepszej ścieżce dostępu, która powinna działać dobrze dla wszystkich danych wejściowych, nie musisz się martwić, że optymalizator wybierze niewłaściwy plan zapytań ze względu na wektor gęstości. Jednak to zapytanie i indeks nie będą tak wydajne jak inne, jeśli szukasz popularnej @Idwartości.

Tabela „MyTable”. Liczba skanów 1, logiczne odczyty 33757

Nie mogę odpowiedzieć na pytanie, dlaczego tutaj, ale szybki i brudny sposób, aby upewnić się, że zapytanie działa tak, jak chcesz:

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable WITH (INDEX(IX_MyTable_Id_SomeBit_Includes))
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

Wiąże się to z ryzykiem, że tabela lub indeksy mogą ulec zmianie w przyszłości, tak że ta optymalizacja stanie się dysfunkcyjna, ale jest dostępna, jeśli jej potrzebujesz. Mam nadzieję, że ktoś może zaoferować odpowiedź na pierwotną przyczynę, zgodnie z twoją prośbą, zamiast tego obejścia.