Wyeliminuj operatora wyszukiwania klucza (klastrowanego), który spowalnia działanie

Jak mogę wyeliminować operatora wyszukiwania kluczowego (klastrowanego) w moim planie wykonania?

Tabela tblQuotesma już indeks klastrowany (włączony QuoteID) i 27 indeksów nieklastrowanych, więc staram się już nie tworzyć.

W QuoteIDzapytaniu umieściłem kolumnę indeksu klastrowego , mając nadzieję, że to pomoże - ale niestety nadal tak samo.

Plan wykonania tutaj .

Lub zobacz:

Oto, co mówi operator Key Lookup:

Pytanie:

declare
        @EffDateFrom datetime ='2017-02-01',
        @EffDateTo   datetime ='2017-08-28'

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data 
CREATE TABLE #Data
(
    QuoteID int NOT NULL,   --clustered index

    [EffectiveDate] [datetime] NULL, --not indexed
    [Submitted] [int] NULL,
    [Quoted] [int] NULL,
    [Bound] [int] NULL,
    [Exonerated] [int] NULL,
    [ProducerLocationId] [int] NULL,
    [ProducerName] [varchar](300) NULL,
    [BusinessType] [varchar](50) NULL,
    [DisplayStatus] [varchar](50) NULL,
    [Agent] [varchar] (50) NULL,
    [ProducerContactGuid] uniqueidentifier NULL
)
INSERT INTO #Data
    SELECT 
        tblQuotes.QuoteID,

          tblQuotes.EffectiveDate,
          CASE WHEN lstQuoteStatus.QuoteStatusID >= 1   THEN 1 ELSE 0 END AS Submitted,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 2 or lstQuoteStatus.QuoteStatusID = 3 or lstQuoteStatus.QuoteStatusID = 202 THEN 1 ELSE 0 END AS Quoted,
          CASE WHEN lstQuoteStatus.Bound = 1 THEN 1 ELSE 0 END AS Bound,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 3 THEN 1 ELSE 0 END AS Exonareted,
          tblQuotes.ProducerLocationID,
          P.Name + ' / '+ P.City as [ProducerName], 
        CASE WHEN tblQuotes.PolicyTypeID = 1 THEN 'New Business' 
             WHEN tblQuotes.PolicyTypeID = 3 THEN 'Rewrite'
             END AS BusinessType,
        tblQuotes.DisplayStatus,
        tblProducerContacts.FName +' '+ tblProducerContacts.LName as Agent,
        tblProducerContacts.ProducerContactGUID
FROM    tblQuotes 
            INNER JOIN lstQuoteStatus 
                on tblQuotes.QuoteStatusID=lstQuoteStatus.QuoteStatusID
            INNER JOIN tblProducerLocations P 
                On P.ProducerLocationID=tblQuotes.ProducerLocationID
            INNER JOIN tblProducerContacts 
                ON dbo.tblQuotes.ProducerContactGuid = tblProducerContacts.ProducerContactGUID

WHERE   DATEDIFF(D,@EffDateFrom,tblQuotes.EffectiveDate)>=0 AND DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <=0
        AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'--Surety
        AND tblQuotes.OriginalQuoteGUID is null

select * from #Data

Plan wykonania:

Kluczowe wyszukiwania różnych odmian występują, gdy procesor zapytań musi uzyskać wartości z kolumn, które nie są przechowywane w indeksie używanym do zlokalizowania wierszy wymaganych do zapytania w celu zwrócenia wyników.

Weźmy na przykład następujący kod, w którym tworzymy tabelę z jednym indeksem:

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    Table1ID int NOT NULL IDENTITY(1,1)
    , Table1Data nvarchar(30) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Table1ID);
GO

Wstawimy 1 000 000 wierszy do tabeli, abyśmy mieli pewne dane do pracy:

INSERT INTO dbo.Table1 (Table1Data)
SELECT TOP(1000000) LEFT(c.name, 30)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Teraz przeszukamy dane z opcją wyświetlenia „rzeczywistego” planu wykonania:

SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

Plan zapytań pokazuje:

Zapytanie sprawdza IX_Table1indeks, aby znaleźć wiersz, Table1ID = 5000000ponieważ przeglądanie tego indeksu jest znacznie szybsze niż skanowanie całej tabeli w poszukiwaniu tej wartości. Aby jednak spełnić wyniki zapytania, procesor zapytań musi również znaleźć wartość dla innych kolumn w tabeli; w tym miejscu pojawia się „RID Lookup”. W tabeli szuka identyfikatora wiersza (RID w RID Lookup) powiązanego z wierszem zawierającym Table1IDwartość 500000, uzyskując wartości z Table1Datakolumny. Jeśli najedziesz myszką na węzeł „Wyszukiwanie RID” w planie, zobaczysz to:

„Lista wyjściowa” zawiera kolumny zwrócone przez wyszukiwanie RID.

Ciekawym przykładem jest tabela z indeksem klastrowanym i indeksem nieklastrowanym. Poniższa tabela ma trzy kolumny; ID, które jest kluczowym klasteryzacji, Datktóra jest indeksowana nieklastrowanych indeksu IX_Tablei trzeciej kolumnie Oth.

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    ID int NOT NULL IDENTITY(1,1) 
        PRIMARY KEY CLUSTERED
    , Dat nvarchar(30) NOT NULL
    , Oth nvarchar(3) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat);
GO

INSERT INTO dbo.Table1 (Dat, Oth)
SELECT TOP(1000000) CRYPT_GEN_RANDOM(30), CRYPT_GEN_RANDOM(3)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Weź przykładowe zapytanie:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

Prosimy SQL Server o zwrócenie każdej kolumny z tabeli, w której Datkolumna zawiera słowo Test. Mamy tutaj kilka możliwości do wyboru; możemy spojrzeć na tabelę (tj. indeks klastrowany) - ale wymagałoby to skanowania całej rzeczy, ponieważ tabela jest uporządkowana według IDkolumny, co nie mówi nam nic o tym, które wiersze (wiersze) zawierają Testw Datkolumnie. Druga opcja (i ta wybrana przez SQL Server) polega na przeszukiwaniu IX_Table1indeksu nieklastrowanego w celu znalezienia wiersza, w którym Dat = 'Test'jednak potrzebna jest również Othkolumna, SQL Server musi przeprowadzić wyszukiwanie w indeksie klastrowym za pomocą klucza „ Wyszukiwanie ”. Oto plan:

Jeśli będziemy modyfikować indeks nieklastrowanym tak, że zawiera w Othkolumnie:

DROP INDEX IX_Table1
ON dbo.Table1;
GO

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat)
INCLUDE (Oth);        <---- This is the only change
GO

Następnie ponownie uruchom zapytanie:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

Widzimy teraz pojedyncze wyszukiwanie indeksu nieklastrowego, ponieważ SQL Server musi po prostu zlokalizować wiersz Dat = 'Test'w IX_Table1indeksie, który zawiera wartość Othi wartość IDkolumny (klucz podstawowy), która jest automatycznie obecna w każdym non-non indeks klastrowy. Plan:

Wyszukiwanie klucza jest spowodowane, ponieważ silnik wybrał indeks, który nie zawiera wszystkich kolumn, które próbujesz pobrać. Tak więc indeks nie obejmuje kolumn w instrukcji select i where.

Aby wyeliminować wyszukiwanie klucza, musisz dołączyć brakujące kolumny (kolumny na liście wyników wyszukiwania klucza) = ProducerContactGuid, QuoteStatusID, PolicyTypeID i ProducerLocationID lub innym sposobem jest wymuszenie użycia zapytania zamiast indeksu klastrowanego.

Należy zauważyć, że 27 indeksów nieklastrowanych w tabeli może mieć negatywny wpływ na wydajność. Podczas uruchamiania aktualizacji, wstawiania lub usuwania SQL Server musi zaktualizować wszystkie indeksy. Ta dodatkowa praca może negatywnie wpłynąć na wydajność.

Zapomniałeś wspomnieć o ilości danych zaangażowanych w to zapytanie. Również dlaczego wstawiasz do tabeli tymczasowej? Jeśli tylko musisz wyświetlić, nie uruchamiaj instrukcji insert.

Na potrzeby tego zapytania tblQuotesnie potrzebuje 27 indeksów nieklastrowych. Potrzebuje 1 indeksu klastrowego i 5 indeksów nieklastrowych lub 6 indeksów nieklastrowych.

To zapytanie wymaga indeksów w tych kolumnach:

QuoteStatusID
ProducerLocationID
ProducerContactGuid
EffectiveDate
LineGUID
OriginalQuoteGUID

Zauważyłem również następujący kod:

DATEDIFF(D, @EffDateFrom, tblQuotes.EffectiveDate) >= 0 AND 
DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <= 0

oznacza NON Sargableto, że nie może korzystać z indeksów.

Aby ten kod SARgablezmienić na następujący:

tblQuotes.EffectiveDate >= @EffDateFrom 
AND  tblQuotes.EffectiveDate <= @EffDateFrom

Aby odpowiedzieć na główne pytanie: „dlaczego otrzymujesz klucz Wyszukaj”:

Dostajesz, KEY Look upponieważ część kolumny, która jest wymieniona w zapytaniu, nie występuje w indeksie obejmującym.

Możesz google i uczyć się o Covering Indexlub Include index.

W moim przykładzie załóżmy, że tblQuotes.QuoteStatusID jest indeksem nieklastrowanym, a następnie mogę również objąć DisplayStatus. Ponieważ chcesz DisplayStatus w zestawie wyników. Dowolną kolumnę, która nie jest obecna w indeksie i jest obecna w zestawie wyników, można zakryć, aby tego uniknąć KEY Look Up or Bookmark lookup. Oto przykład obejmujący indeks:

create nonclustered index tblQuotes_QuoteStatusID 
on tblQuotes(QuoteStatusID)
include(DisplayStatus);

** Zastrzeżenie: ** Pamiętaj, że powyżej jest tylko mój przykład DisplayStatus może być objęty innym Non CI po analizie.

Podobnie będziesz musiał utworzyć indeks i indeks obejmujący inne tabele biorące udział w zapytaniu.

Dostajesz się Index SCANtakże do swojego planu.

Może się to zdarzyć, ponieważ w tabeli nie ma indeksu lub gdy jest duża ilość danych, optymalizator może zdecydować się na skanowanie zamiast przeszukiwania indeksu.

Może to również wystąpić z powodu High cardinality. Uzyskanie większej liczby rzędów niż jest to wymagane z powodu wadliwego łączenia. Można to również poprawić.