W jaki sposób SQL Server określa kolejność kolumn kluczowych w brakujących żądaniach indeksu?

W jaki sposób SQL Server określa kolejność kolumn kluczowych w swoich brakujących zaleceniach dotyczących indeksu dla planu zapytań?

Gdy SQL Server tworzy brakujące zalecenie dotyczące indeksu dla określonego planu zapytań, rozdziela możliwe kolumny kluczy na 2 grupy. Pierwszy zestaw zawiera wszystkie zalecane kolumny, które są częścią predykatu RÓWNOŚĆ. Drugi zestaw zawiera wszystkie zalecane kolumny, które są częścią predykatu NIERÓWNOŚCI.

W ramach każdego zestawu kolumny są uporządkowane według porządkowej pozycji kolumn na podstawie definicji tabeli.

(Wielkie podziękowania dla Brenta Ozara za zbudowanie skryptu repro w bazie danych przepełnienia stosu, aby to udowodnić!)

1. Utwórz 3 identyczne tabele , ale ułóż kolumny w innej kolejności. (Powodem jest tutaj użycie różnych nazw kolumn i typów danych, aby pokazać, że nie ma to wpływu na kolejność kolumn w zaleceniu dotyczącym brakującego indeksu).

CREATE TABLE dbo.NumberLetterDate (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
fINT INT, fNVARCHAR NVARCHAR(40), fDATE DATETIME, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.LetterDateNumber (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
fNVARCHAR NVARCHAR(40), fDATE DATETIME, fINT INT, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.DateNumberLetter (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fDATE DATETIME, fINT INT, fNVARCHAR NVARCHAR(40), AboutMe NVARCHAR(MAX));
GO

2. Wypełnij tabele tymi samymi danymi. Uzyskaj 100 000 wierszy z tabeli Użytkownicy dzięki rzeczywistej dystrybucji danych.

INSERT INTO dbo.NumberLetterDate(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.LetterDateNumber(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.DateNumberLetter(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO

3. Napisz zapytanie, które wymaga indeksu. Zacznij od 3 filtrów równości, filtrując w celu uzyskania dokładnej wartości we wszystkich 3 polach. Pamiętaj, że wszystkie 3 zapytania mają te same pola w tej samej kolejności:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

Wszystkie trzy tabele zawierają dokładnie te same dane, a zapytania są identyczne. Jedyną różnicą jest kolejność pól - i to także różnica w naszych brakujących żądaniach indeksu:

W planach wykonania kolejność kolumn w brakującym żądaniu indeksu dokładnie odpowiada kolejności kolumn w tabeli. Na przykład w dbo.NumberLetterDate kolumna z liczbami jest pierwsza, więc jest też pierwsza w brakującym żądaniu indeksu:

• W dbo.NumberLetterDate brakujący indeks znajduje się na fINT (liczba), fLetter (nvarchar), fDate, w tej samej kolejności pól w tabeli
• W dbo.LetterDateNumber kolejność indeksów przełącza się na fNVARCHAR, fDATE, fINT
• W dbo.DateNumberLetter kolejność indeksowania przełącza się na fDATE, fINT, fNVARCHAR

W przypadku operacji na jednej tabeli, taka jak ta, kolejność pól indeksu nie wydaje się zależeć od selektywności, typu danych lub pozycji w zapytaniu. (Pozostawiam to innym ludziom, aby udowodnić to przy pomocy bardziej złożonych zapytań i dołączeń).

4. Wstaw filtr nierówności. Na przykład w polu INT wpisz <> 100 jako filtr:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

W planach wykonania najpierw zaczynają się pola równości, a następnie pola nierówności - więc tutaj fINT pojawia się jako ostatni we wszystkich 3 brakujących żądaniach indeksu, ponieważ jest to wyszukiwanie nierówności:

5. Użyj 3 filtrów nierówności. Użyj tego samego wyszukiwania dla wszystkich pól (<>):

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);
GO

Ponieważ nie ma wyszukiwania równości, wszystkie 3 pola mają tę samą kolejność priorytetów w brakującej rekomendacji indeksu, a teraz wracamy do sortowania według kolejności pól: