Dlaczego mój indeks nie jest używany w SELECT TOP?

Oto podsumowanie: wykonuję wybrane zapytanie. Każda kolumna w klauzulach WHEREi ORDER BYznajduje się w jednym indeksie nieklastrowanym IX_MachineryId_DateRecorded, jako część klucza lub jako INCLUDEkolumna. Wybieram wszystkie kolumny, aby uzyskać przeglądanie zakładek, ale biorę tylko TOP (1), więc z pewnością serwer może powiedzieć, że wyszukiwanie musi być wykonane tylko raz, na końcu.

Co najważniejsze, kiedy zmuszam zapytanie do użycia indeksu IX_MachineryId_DateRecorded, działa ono w mniej niż sekundę.Jeśli pozwolę, aby serwer zdecydował, którego indeksu użyć, wybiera IX_MachineryIdi zajmuje to minutę. To naprawdę sugeruje, że poprawiłem indeks, a serwer po prostu źle podejmuje decyzję. Dlaczego?

CREATE TABLE [dbo].[MachineryReading] (
    [Id]                 INT              IDENTITY (1, 1) NOT NULL,
    [Location]           [sys].[geometry] NULL,
    [Latitude]           FLOAT (53)       NOT NULL,
    [Longitude]          FLOAT (53)       NOT NULL,
    [Altitude]           FLOAT (53)       NULL,
    [Odometer]           INT              NULL,
    [Speed]              FLOAT (53)       NULL,
    [BatteryLevel]       INT              NULL,
    [PinFlags]           BIGINT           NOT NULL,
    [DateRecorded]       DATETIME         NOT NULL,
    [DateReceived]       DATETIME         NOT NULL,
    [Satellites]         INT              NOT NULL,
    [HDOP]               FLOAT (53)       NOT NULL,
    [MachineryId]        INT              NOT NULL,
    [TrackerId]          INT              NOT NULL,
    [ReportType]         NVARCHAR (1)     NULL,
    [FixStatus]          INT              DEFAULT ((0)) NOT NULL,
    [AlarmStatus]        INT              DEFAULT ((0)) NOT NULL,
    [OperationalSeconds] INT              DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_dbo.MachineryReading] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] FOREIGN KEY ([MachineryId]) REFERENCES [dbo].[Machinery] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].[Tracker] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId]
    ON [dbo].[MachineryReading]([MachineryId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_TrackerId]
    ON [dbo].[MachineryReading]([TrackerId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded]
    ON [dbo].[MachineryReading]([MachineryId] ASC, [DateRecorded] ASC)
    INCLUDE([OperationalSeconds], [FixStatus]);

Tabela jest podzielona na przedziały miesięcy (choć nadal nie rozumiem, co się tam dzieje).

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-01-01T00:00:00.000') 

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-02-01T00:00:00.000') 
...

CREATE UNIQUE CLUSTERED INDEX [PK_dbo.MachineryReadingPs] ON MachineryReading(DateRecorded, Id) ON PartitionSchemeMonthRange(DateRecorded)

Zapytanie, które normalnie uruchomiłbym:

SELECT TOP (1) [Id], [Location], [Latitude], [Longitude], [Altitude], [Odometer], [ReportType], [FixStatus], [AlarmStatus], [Speed], [BatteryLevel], [PinFlags], [DateRecorded], [DateReceived], [Satellites], [HDOP], [OperationalSeconds], [MachineryId], [TrackerId]
    FROM [dbo].[MachineryReading]
    --WITH(INDEX(IX_MachineryId_DateRecorded)) --This makes all the difference
    WHERE ([MachineryId] = @p__linq__0) AND ([DateRecorded] >= @p__linq__1) AND ([DateRecorded] < @p__linq__2) AND ([OperationalSeconds] > 0)
    ORDER BY [DateRecorded] ASC

Plan zapytań: https://www.brentozar.com/pastetheplan/?id=r1c-RpxNx

Plan zapytań z wymuszonym indeksem: https://www.brentozar.com/pastetheplan/?id=SywwTagVe

Uwzględnione plany są rzeczywistymi planami wykonania, ale w bazie danych pomostowych (około 1/100 wielkości na żywo). Waham się, czy nie bawić się w bazie danych na żywo, ponieważ zacząłem w tej firmie dopiero około miesiąc temu.

Mam wrażenie, że dzieje się tak z powodu partycjonowania, a moje zapytanie zazwyczaj obejmuje każdą partycję (np. Kiedy chcę uzyskać pierwszą lub ostatnią OperationalSecondszapisaną dla jednego komputera). Jednak zapytania, które piszę ręcznie, działają poprawnie 10 - 100 razy szybciej niż to, co wygenerował EntityFramework , więc po prostu utworzę procedurę przechowywaną.

Jeśli pozwolę, aby serwer zdecydował, którego indeksu użyć, wybiera IX_MachineryIdi zajmuje to minutę.

Ten indeks nie jest podzielony na partycje, więc optymalizator rozpoznaje, że można go użyć do zapewnienia kolejności określonej w zapytaniu bez sortowania. Jako nieunikalny indeks nieklastrowany ma również klucze indeksu klastrowego jako podklucze, dzięki czemu można go używać do wyszukiwania MachineryIdi DateRecordedzakresu:

Indeks nie obejmuje OperationalSeconds, więc plan musi sprawdzić tę wartość w górę w wierszu w indeksie klastrowym (podzielonym na partycje) w celu przetestowania OperationalSeconds > 0:

Optymalizator szacuje, że jeden wiersz będzie musiał zostać odczytany z indeksu nieklastrowanego i sprawdzony, aby spełnić TOP (1) . Obliczenia te oparte są na celu rzędu (znajdź szybko jeden wiersz) i zakładają jednolity rozkład wartości.

Z aktualnego planu wynika, że ​​szacunek 1 wiersza jest niedokładny. W rzeczywistości należy przetworzyć 19 039 wierszy, aby stwierdzić, że żaden wiersz nie spełnia warunków zapytania. Jest to najgorszy przypadek optymalizacji celu rzędu (szacowany 1 rząd, wszystkie wiersze faktycznie potrzebne):

Możesz wyłączyć cele wierszy za pomocą flagi śledzenia 4138 . Najprawdopodobniej spowoduje to, że SQL Server wybierze inny plan, prawdopodobnie ten wymuszony. W każdym razie indeks IX_MachineryIdmożna zoptymalizować poprzez włączenie OperationalSeconds.

Dość niecodzienne są indeksy nieklastrowane (indeksy podzielone na partycje w inny sposób niż tabela bazowa, w tym wcale).

To naprawdę sugeruje, że poprawiłem indeks, a serwer po prostu źle podejmuje decyzję. Dlaczego?

Jak zwykle optymalizator wybiera najtańszy plan, jaki uważa.

Szacowany koszt IX_MachineryIdplanu wynosi 0,01 jednostek kosztu, w oparciu o (niepoprawne) założenie celu wiersza, że ​​jeden wiersz zostanie przetestowany i zwrócony.

Szacowany koszt IX_MachineryId_DateRecordedplanu jest znacznie wyższy i wynosi 0,27 jednostki, głównie dlatego, że oczekuje odczytania 5 515 wierszy z indeksu, posortowania ich i zwrócenia tego, który sortuje najniższy (według DateRecorded):

Ten indeks jest podzielony na partycje i nie może DateRecordedbezpośrednio zwracać wierszy w kolejności (patrz później). Może wyszukiwać MachineryIdi DateRecordedzasięg w obrębie każdej partycji , ale wymagane jest sortowanie:

Gdyby ten indeks nie był podzielony na partycje, sortowanie nie byłoby wymagane i byłoby bardzo podobne do innego (niepartycjonowanego) indeksu z dodatkową zawartą kolumną. Niepodzielony filtrowany indeks byłby jeszcze nieco bardziej wydajny.

Należy zaktualizować kwerendy źródłowej tak, że typy danych tych @Fromi @Toparametrów dopasować do DateRecordedkolumny ( datetime). W tej chwili SQL Server oblicza zakres dynamiczny ze względu na niedopasowanie typu w czasie wykonywania (przy użyciu operatora Interwał scalania i jego poddrzewa):

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@From],NULL,(22))">
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@To],NULL,(22))">

Ta konwersja uniemożliwia optymalizatorowi prawidłowe rozumowanie związku między rosnącymi identyfikatorami partycji (obejmującymi zakres DateRecordedwartości w porządku rosnącym) a przewidywanymi nierównościami DateRecorded.

Identyfikator partycji jest domyślnym kluczem wiodącym indeksu podzielonego na partycje. Zwykle optymalizator widzi, że kolejność według identyfikatora partycji (gdzie rosnące identyfikatory odwzorowują na rosnące, rozłączne wartości DateRecorded), to DateRecordedjest to samo, co sortowanie DateRecordedpojedynczo (biorąc pod uwagę, że MachineryIDjest stałe). Ten ciąg rozumowania jest przerywany przez konwersję typu.

Próbny

Prosta tabela podzielona na partycje i indeks:

CREATE PARTITION FUNCTION PF (datetime)
AS RANGE LEFT FOR VALUES ('20160101', '20160201', '20160301');

CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

CREATE TABLE dbo.T (c1 integer NOT NULL, c2 datetime NOT NULL) ON PS (c2);

CREATE INDEX i ON dbo.T (c1, c2) ON PS (c2);

INSERT dbo.T (c1, c2) 
VALUES (1, '20160101'), (1, '20160201'), (1, '20160301');

Zapytanie z dopasowanymi typami

-- Types match (datetime)
DECLARE 
    @From datetime = '20010101',
    @To datetime = '20090101';

-- Seek with no sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 >= @From
AND T2.c2 < @To
ORDER BY 
    T2.c2;

Zapytanie z niedopasowanymi typami

-- Mismatched types (datetime2 vs datetime)
DECLARE 
    @From datetime2 = '20010101',
    @To datetime2 = '20090101';

-- Merge Interval and Sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 >= @From
AND T2.c2 < @To
ORDER BY 
    T2.c2;

Indeks wydaje się całkiem dobry dla zapytania i nie jestem pewien, dlaczego optymalizator go nie wybrał (statystyki? Podział? Ograniczenie? Lazur ?, naprawdę nie ma pojęcia).

Ale filtrowany indeks byłby jeszcze lepszy dla konkretnego zapytania, jeśli > 0jest to stała wartość i nie zmienia się z jednego wykonania zapytania na drugie:

CREATE NONCLUSTERED INDEX IX_MachineryId_DateRecorded_filtered
    ON dbo.MachineryReading
        (MachineryId, DateRecorded) 
    WHERE (OperationalSeconds > 0) ;

Istnieją dwie różnice między indeksem, w którym OperationalSecondsznajduje się trzecia kolumna, a indeksem filtrowanym:

• Po pierwsze, filtrowany indeks jest mniejszy, zarówno pod względem szerokości (węższy), jak i liczby wierszy.
  To sprawia, że ​​filtrowany indeks jest ogólnie bardziej wydajny, ponieważ SQL Server potrzebuje mniej miejsca do przechowywania go w pamięci.

• Po drugie, jest to bardziej subtelne i ważne dla zapytania, ponieważ ma tylko wiersze pasujące do filtra zastosowanego w zapytaniu. Może to być niezwykle ważne, w zależności od wartości tej trzeciej kolumny.
  Na przykład określony zestaw parametrów dla MachineryIdi DateRecordedmoże dać 1000 wierszy. Jeśli wszystkie lub prawie wszystkie z tych wierszy są zgodne z (OperationalSeconds > 0)filtrem, oba indeksy będą się dobrze zachowywać. Ale jeśli wiersze pasujące do filtra są bardzo nieliczne (lub tylko ostatni lub wcale), pierwszy indeks będzie musiał przejść wiele lub wszystkie te 1000 wierszy, aż znajdzie pasujące. Z drugiej strony filtrowany indeks wymaga tylko jednej próby znalezienia pasującego wiersza (lub zwrócenia 0 wierszy), ponieważ przechowywane są tylko wiersze pasujące do filtru.