ROW_NUMBER () OVER (PARTITION BY B, A ORDER BY C) nie używa indeksu na (A, B, C)

Rozważ te dwie funkcje:

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

O ile rozumiem, dają one dokładnie taki sam wynik. Innymi słowy kolejność wyświetlania kolumn w PARTITION BYklauzuli nie ma znaczenia.

Jeśli istnieje indeks (A,B,C), oczekiwałem, że optymalizator użyje tego indeksu w obu wariantach.

Ale, co zaskakujące, optymalizator postanowił wykonać wyjątkowo wyraźne sortowanie w drugim wariancie.

Widziałem to na SQL Server 2008 Standard i SQL Server 2014 Express.

Oto pełny skrypt, którego użyłem do jego odtworzenia.

Wypróbowano na Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 20 lutego 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) na Windows NT 6.1 (kompilacja 7601: Service Pack 1)

i Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) 27 maja 2016 15:33:17 Prawa autorskie (c) Microsoft Corporation Express Edition (64-bit) w systemie Windows NT 6.1 (kompilacja 7601: usługa Paczka 1)

zarówno ze starym, jak i nowym estymatorem liczności przy użyciu OPTION (QUERYTRACEON 9481)i OPTION (QUERYTRACEON 2312).

Skonfiguruj tabelę, indeks, przykładowe dane

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

Zapytania

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

Plany wykonania

PODZIAŁ NA A, B

PODZIAŁ PRZEZ B, A

Obie

Jak widać, drugi plan ma dodatkowe sortowanie. Zamawia B, A, C. Optymalizator najwyraźniej nie jest wystarczająco inteligentny, aby zdać sobie sprawę, że PARTITION BY B,Ajest to samo PARTITION BY A,Bi ponownie sortuje dane.

Co ciekawe, trzecie zapytanie zawiera oba warianty ROW_NUMBERi nie ma dodatkowego sortowania! Plan jest taki sam jak dla pierwszego zapytania. (Projekt sekwencji ma dodatkowe wyrażenie na liście wyników dla dodatkowej kolumny, ale bez dodatkowego sortowania). Tak więc w tym bardziej skomplikowanym przypadku optymalizator wydawał się być wystarczająco inteligentny, aby zdać sobie sprawę, że PARTITION BY B,Ato samo PARTITION BY A,B.

W pierwszym i trzecim zapytaniu operator skanowania indeksu ma właściwość Uporządkowany: Prawda, w drugim zapytaniu jest False.

Jeszcze bardziej interesujące, jeśli ponownie napiszę trzecie zapytanie w ten sposób (zamień dwie kolumny):

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

wtedy dodatkowe Sortowanie pojawi się ponownie!

Czy ktoś mógłby rzucić trochę światła? Co się tutaj dzieje w optymalizatorze?

Wydaje się, że nie ma dobrej ostatecznej „odpowiedzi” na pytanie „co dzieje się w optymalizatorze”, chyba że jesteś jego programistą i znasz jego wewnętrzne elementy.

Tutaj zbiorę komentarze.

Ogólnie wydaje się, że nazwanie go błędem byłoby zbyt trudne, ponieważ wynik końcowy zapytania jest poprawny. W niektórych przypadkach plan wykonania po prostu nie jest optymalny. ypercubeᵀᴹ , Martin Smith i Aaron Bertrand nazywają to „ brakiem optymalizacji”.

• Wygląda na podobne GROUP BY a,bi GROUP BY b,adaje identyczne plany, ale PARTITION BYnie może użyć tej samej transformacji

• Istnieją również inne brakujące optymalizacje, w których funkcje okna z tą samą specyfikacją okna mogą mieć dodatkową operację sortowania, jeśli zostaną oddzielone na liście wyboru przez jedną z inną specyfikacją.

• Tak, wydaje się to kolejną pominiętą optymalizacją i jest ich wiele. Optymalizator jest napisany przez ludzi i nie jest idealny

Istnieje nieco powiązany artykuł Malejące indeksy. Kolejność obliczeń, równoległość i obliczenia rankingu autorstwa Itzika Ben-Gana. Tam Itzik omawia indeksy malejące, a także podaje przykład wpływu kierunku definicji indeksu na funkcje okna z partycjami. Pokazuje przykłady zapytań i wygenerowanych planów z ROW_NUMBERdodatkowym operatorem sortowania, którego optymalizator mógłby uniknąć.

Dla mnie praktycznym rezultatem byłoby zapamiętanie tej szczególnej cechy optymalizatora. Korzystając z funkcji PARTITION BYw oknie, zawsze staraj się dopasować kolejność, w której wyświetlasz kolumny w PARTITION BYkolejności, w jakiej są one wymienione w indeksie. Nawet jeśli to nie powinno mieć znaczenia.

Inną stroną tego środka ostrożności jest przejrzenie indeksów i podjęcie decyzji o zamianie niektórych kolumn w definicji indeksu. Pamiętaj, że możesz przypadkowo wpłynąć na niektóre istniejące zapytania, na które najwyraźniej nie powinno to mieć wpływu. Właśnie w ten sposób zauważyłem osobliwość optymalizatora.

Jeśli tego nie zrobisz, optymalizator może nie być w stanie w pełni wykorzystać indeksu. Nawet jeśli optymalizator wybierze optymalny plan, taki plan może zmienić się na mniej optymalny z najmniejszą niewinną zmianą w zapytaniu, na przykład poprzez zmianę kolejności kolumn w SELECTinstrukcji.