Elimine o operador Key Lookup (Clustered) que diminui o desempenho

Pesquisas de chave de vários tipos ocorrem quando o processador de consulta precisa obter valores de colunas que não estão armazenadas no índice usado para localizar as linhas necessárias para a consulta retornar resultados.

Tomemos como exemplo o código a seguir, onde estamos criando uma tabela com um único índice:

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

Vamos inserir 1.000.000 de linhas na tabela para que tenhamos alguns dados para trabalhar:

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

Agora, consultaremos os dados com a opção de exibir o plano de execução "real":

SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

O plano de consulta mostra:

A consulta examina o IX_Table1índice para localizar a linha, Table1ID = 5000000pois examinar esse índice é muito mais rápido do que examinar a tabela inteira procurando esse valor. No entanto, para satisfazer os resultados da consulta, o processador de consulta também deve localizar o valor das outras colunas da tabela; é aí que entra o "RID Lookup". Ele procura na tabela o ID da linha (o RID em RID Lookup) associado à linha que contém o Table1IDvalor 500000, obtendo os valores da Table1Datacoluna. Se você passar o mouse sobre o nó "RID Lookup" no plano, verá isto:

A "Lista de Saída" contém as colunas retornadas pela Pesquisa RID.

Uma tabela com um índice clusterizado e um índice não clusterizado é um exemplo interessante. A tabela abaixo tem três colunas; ID que é a chave de cluster, Datque é indexada por um índice não clusterizado IX_Tablee uma terceira coluna, 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

Veja este exemplo de consulta:

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

We're asking SQL Server to return every column from the table where the Dat column contains the word Test. We have a couple of choices here; we can look at the table (i.e. the clustered index) - but that would entail scanning the entire thing since the table is ordered by the ID column, which tells us nothing about which row(s) contain Test in the Dat column. The other option (and the one chosen by SQL Server) consists of seeking into the IX_Table1 non-clustered index to find the row where Dat = 'Test', however since we need the Oth column as well, SQL Server must perform a lookup into the clustered index using a "Key Lookup" operation. This is the plan for that:

If we modify the non-clustered index so that it includes the Oth column:

DROP INDEX IX_Table1
ON dbo.Table1;
GO

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

Em seguida, execute novamente a consulta:

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

Agora vemos uma única busca de índice não clusterizado, pois o SQL Server simplesmente precisa localizar a linha onde Dat = 'Test'no IX_Table1índice, que inclui o valor de Oth, e o valor da IDcoluna (a chave primária), que está automaticamente presente em todos os dados não-agrupados. índice agrupado. O plano:

A pesquisa de chave é causada porque o mecanismo optou por usar um índice que não contém todas as colunas que você está tentando buscar. Portanto, o índice não está cobrindo as colunas na instrução select e where.

Para eliminar a pesquisa de chave, você precisa incluir as colunas ausentes (as colunas na lista de saída da pesquisa de chave) = ProducerContactGuid, QuoteStatusID, PolicyTypeID e ProducerLocationID ou outra maneira é forçar a consulta a usar o índice clusterizado.

Observe que 27 índices não clusterizados em uma tabela podem ser ruins para o desempenho. Ao executar uma atualização, inserção ou exclusão, o SQL Server deve atualizar todos os índices. Este trabalho extra pode afetar negativamente o desempenho.

Você esqueceu de mencionar o volume de dados envolvidos nesta consulta. Além disso, por que você está inserindo em uma tabela temporária? Se apenas você precisar exibir, não execute uma instrução de inserção.

Para os fins desta consulta, tblQuotesnão são necessários 27 índices não clusterizados. Ele precisa de 1 índice clusterizado e 5 índices não clusterizados ou, talvez, 6 indexex não clusterizados.

Esta consulta gostaria de índices nestas colunas:

QuoteStatusID
ProducerLocationID
ProducerContactGuid
EffectiveDate
LineGUID
OriginalQuoteGUID

Notei também o seguinte código:

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

é NON Sargableou seja, não pode utilizar índices.

Para fazer esse código SARgablealterá-lo para isso:

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

Para responder à sua pergunta principal, "por que você está recebendo uma chave Look up":

Você está recebendo KEY Look upporque algumas das colunas mencionadas na consulta não estão presentes em um índice de cobertura.

Você pode pesquisar no Google e estudar sobre Covering Indexou Include index.

No meu exemplo, suponha que tblQuotes.QuoteStatusID é índice não clusterizado, então também posso cobrir DisplayStatus. Desde que você queira DisplayStatus em Resultset. Qualquer coluna que não esteja presente em um índice e esteja presente no conjunto de resultados pode ser coberta para evitar KEY Look Up or Bookmark lookup. Este é um exemplo de índice de cobertura:

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

**Isenção de responsabilidade:**Lembre-se acima é apenas meu exemplo DisplayStatus pode ser coberto com outro não CI após a análise.

Da mesma forma, você terá que criar índice e índice de cobertura nas outras tabelas envolvidas na consulta.

Você Index SCANtambém está entrando no seu plano.

Isso pode acontecer porque não há índice na tabela ou quando há um grande volume de dados, o otimizador pode decidir varrer em vez de realizar uma busca de índice.

Isso também pode ocorrer devido a High cardinality. Obtendo mais número de linhas do que o necessário devido a uma junção defeituosa. Isso também pode ser corrigido.