Mam bazę danych SQL Server, w której zapytania są dość powolne, a blokowanie i blokowanie jest bardzo duże.

Kiedy patrzę na brakujące indeksy DMV i plany zapytań, nie ma żadnych sugestii.

Dlaczego?

sql-server performance index

— Erik Darling
źródło

Istnieje wiele powodów, dla których możesz nie przegapić żądań indeksu!

Przyjrzymy się bardziej szczegółowo kilku przyczynom, a także porozmawiamy o niektórych ogólnych ograniczeniach tej funkcji.

Ogólne ograniczenia

Po pierwsze: Ograniczenia funkcji brakujących indeksów :

Nie określa kolejności używania kolumn w indeksie.

Jak zauważono w tym pytaniu: W jaki sposób SQL Server określa kolejność kolumn kluczowych w brakujących żądaniach indeksu? , kolejność kolumn w definicji indeksu jest podyktowana predykatem Równość vs Nierówność, a następnie pozycją porządkową kolumny w tabeli.

Nie ma domysłów na temat selektywności i może być dostępna lepsza kolejność. Twoim zadaniem jest to rozgryźć.

Specjalne indeksy

Brakujące indeksy nie obejmują również indeksów „specjalnych”, takich jak:

Zgrupowane
Przefiltrowany
Podzielony na partycje
Sprężony
Edycja XML
Edycja przestrzenna
Columnstore-d
Indeksowane Zobacz-ed

Jakie kolumny są brane pod uwagę?

Brakujące kolumny klucza indeksu są generowane z kolumn używanych do filtrowania wyników, takich jak:

DOŁĄCZ
GDZIE klauzula

Brakujące uwzględnione kolumny są generowane z kolumn wymaganych przez zapytanie, takich jak:

WYBIERZ
GRUPUJ WEDŁUG
ZAMÓW PRZEZ

Chociaż dość często kolumny, według których zamawiasz lub grupujesz, mogą być przydatne jako kolumny kluczowe. Wraca to do jednego z ograniczeń:

Nie ma na celu dostrajania konfiguracji indeksowania.

Na przykład to zapytanie nie zarejestruje brakującego żądania indeksu, nawet jeśli dodanie indeksu w LastAccessDate zapobiegnie potrzebie Sortowania (i przelania na dysk).

SELECT TOP (1000) u.DisplayName
FROM dbo.Users AS u
ORDER BY u.LastAccessDate DESC;

To zapytanie grupujące również nie dotyczy lokalizacji.

SELECT TOP (20000) u.Location
FROM dbo.Users AS u
GROUP BY u.Location

To nie wydaje się bardzo pomocne!

No tak, ale to lepsze niż nic. Pomyśl o brakujących żądaniach indeksu, takich jak płaczące dziecko. Wiesz, że jest problem, ale jako dorosły musisz ustalić, na czym polega ten problem.

Nadal mi nie powiedziałeś, dlaczego ich nie mam ...

Odpręż się, skarbie. Dojedziemy tam.

Flagi śledzenia

Jeśli włączysz TF 2330 , brakujące żądania indeksu nie będą rejestrowane. Aby dowiedzieć się, czy masz to włączone, uruchom to:

DBCC TRACESTATUS;

Przebudowa indeksu

Odbudowywanie indeksów usunie brakujące żądania indeksów. Więc zanim przejdziesz do Hi-Ho-Silver-Away, przebudowując każdy indeks, w sekundę wkrada się jota fragmentacji, zastanów się nad informacjami, które usuwasz za każdym razem, gdy to robisz.

W każdym razie możesz zastanowić się, dlaczego defragmentacja indeksów nie pomaga . Chyba że używasz magazynu kolumn .

Dodawanie, usuwanie lub wyłączanie indeksów

Dodanie, usunięcie lub wyłączenie indeksu usunie wszystkie brakujące żądania indeksu dla tej tabeli. Jeśli pracujesz nad kilkoma zmianami indeksu w tej samej tabeli, upewnij się, że wykonałeś wszystkie skrypty przed ich wprowadzeniem.

Trywialne plany

Jeśli plan jest wystarczająco prosty, a wybór dostępu do indeksu jest wystarczająco oczywisty, a koszt wystarczająco niski, otrzymasz prosty plan.

Oznacza to, że optymalizator nie musiał podejmować żadnych decyzji opartych na kosztach.

Via Paul White :

Szczegóły dotyczące tego, jakie typy zapytań mogą korzystać z Trywialnego planu, często się zmieniają, ale takie rzeczy jak sprzężenia, podzapytania i predykaty nierówności zasadniczo uniemożliwiają tę optymalizację.

Gdy plan jest trywialny, dodatkowe fazy optymalizacji nie są badane, a brakujące indeksy nie są wymagane .

Zobacz różnicę między tymi zapytaniami a ich planami :

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2;

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2
AND 1 = (SELECT 1);

Pierwszy plan jest trywialny i nie wyświetla się żadne żądanie. Mogą wystąpić przypadki, w których błędy zapobiegają pojawianiu się brakujących indeksów w planach zapytań; są jednak zwykle bardziej niezawodnie rejestrowane w brakujących indeksach DMV.

SARGability

Przewidywanie, gdzie optymalizator nie byłby w stanie efektywnie korzystać z indeksu, nawet z indeksem, może uniemożliwić ich zalogowanie.

Rzeczy, które zasadniczo nie są SARGable, to:

Kolumny zawinięte w funkcje
Kolumna + SomeValue = SomePredicate
Kolumna + Kolejna kolumna = SomePredicate
Kolumna = @Zmienna LUB @Zmienna JEST NULL

Przykłady:

SELECT *
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 1000) > 1000;


SELECT *
FROM dbo.Users AS u
WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000


SELECT *
FROM dbo.Users AS u
WHERE u.UpVotes + u.DownVotes > 10000000


DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren'
SELECT *
FROM dbo.Users AS u
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo 
      OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;

Żadne z tych zapytań nie zarejestruje brakujących żądań indeksu. Aby uzyskać więcej informacji na ten temat, sprawdź następujące linki:

Masz już dobry indeks

Weź ten indeks:

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

Wygląda dobrze dla tego zapytania:

SELECT p.OwnerUserId, p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20070101'
AND   p.CreationDate < '20181231'
AND   p.Score >= 25000
AND 1 = (SELECT 1)
ORDER BY p.Score DESC;

Plan jest prosty Szukaj ...

Ale ponieważ wiodąca kolumna kluczowa dotyczy mniej selektywnego predykatu, w rezultacie wykonujemy więcej pracy, niż powinniśmy:

Tabela „Posty”. Liczba skanów 13, logiczne odczyty 136890

Jeśli zmienimy kolejność kolumn klucza indeksu, wykonujemy o wiele mniej pracy:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

I znacznie mniej czyta:

Tabela „Posty”. Liczba skanów 1, odczyty logiczne 5

SQL Server tworzy dla Ciebie indeksy

W niektórych przypadkach SQL Server wybierze tworzenie indeksu w locie za pomocą bufora indeksu. Gdy bufor buforowy jest obecny, brakujące żądanie indeksu nie będzie. Z pewnością samodzielne dodanie indeksu może być dobrym pomysłem, ale nie licz na to, że SQL Server pomoże ci to zrozumieć.