Tenho uma tabela assim:
CREATE TABLE TestTable
(
[TestTableID] [int] IDENTITY(1,1) NOT NULL,
[IntField1] [int] NOT NULL,
[IntField2] [int] NOT NULL,
[IntField3] [int] NOT NULL,
[IntField4] [int] NOT NULL,
[IntField5] [int] NOT NULL,
[DateField1] [datetime] NOT NULL,
[IntField6] [int] NOT NULL,
[IntField7] [int] NOT NULL,
[TextField1] [nvarchar](300) NULL,
[DateField2] [datetime] NULL,
[TextField2] [nvarchar](300) NULL,
[DateField3] [datetime] NULL,
[BoolField1] [bit] NULL
)
Eu criei um índice assim:
CREATE NONCLUSTERED INDEX IX_TestTable_DateField1
ON TestTable(DateField1);
E agora tenho esta consulta:
DECLARE @startDate DATETIME = '20190101'
, @endDate DATETIME = '20200101'
SELECT [TestTableID],
[IntField1],
[IntField2],
[IntField3],
[IntField4],
[IntField5],
[DateField1],
[IntField6],
[IntField7],
[TextField1],
[DateField2],
[TextField2],
[DateField3],
[BoolField1]
FROM TestTable
WHERE DateField1 >= @startDate
AND DateField1 < @endDate
Essa tabela tem quase 10.000.000 de registros e essa consulta retornará cerca de 10.000 registros.
Agora, eu esperava que a consulta pelo menos usasse meu índice IX_TestTable_DateField1 ( Index Scan + Key Lookup ), mas está fazendo um Clustered Index Scan (no campo PK). Acho que é porque a consulta está retornando todos os campos da tabela.
Meu pensamento anterior era:
- Se o índice tiver INCLUÍDO todos os campos, então o SqlServer fará uma Busca de Índice;
- Se não incluir todos os campos, mas se o campo for usado em WHERE ou ORDER, usará Index Scan + Key Lookup;
- Se não for 1 ou 2, ele fará um Clustered Index Scan;
Isso está correto? Por que um "Index Scan + Key Lookup" não está acontecendo?
O SQL Server pode definitivamente fazer uma busca de índice e depois uma pesquisa, mesmo quando você não cobre a consulta.
O otimizador não tem ideia de quais valores você tem em suas variáveis (é assim que as variáveis funcionam). Então ele tem que adivinhar a seletividade. Você pode olhar para o plano de execução real e ver quantas linhas ele adivinha. Aparentemente, ele adivinha tantas linhas, então decide que é melhor fazer uma varredura de tabela (cl ix varredura).
Se você adicionar OPTION(RECOMPILE) no final da consulta, deverá ver uma seletividade estimada diferente e potencialmente o uso do índice (tudo com base na seletividade que você tem no final).
Além disso, se você tiver literais (os valores são conhecidos) ou parâmetros de procedimento armazenado (os valores são rastreados), verá como ele estimará de maneira diferente.
Aqui está o que a Microsoft diz sobre a otimização de instruções SELECT
Como você pode ver, o Query Optimizer escolherá o plano que espera obter a execução mais eficiente. Às vezes, usar um (Index Scan + Key Lookup) não é o método mais eficiente.
Como teste, você pode comparar os resultados do plano de execução STATISTICS TIME e STATISTICS IO que está obtendo agora com os gerados para sua consulta, forçando-a a usar o índice com uma dica de consulta . Por favor, note que não estou aconselhando você a usar esta dica como solução, mas como uma forma de comparar como seria o desempenho da execução se usasse o índice como você deseja.
Para leitura adicional, o artigo de Benjamin Nevarez traz algumas boas informações: O SQL Server Query Optimizer
O problema que você está observando tem a ver com a maneira como o SQL Server determina a melhor maneira de executar uma consulta e é chamado de detecção de parâmetros.
Leitura de referência
O que?
Quando você executou sua consulta pela primeira vez, o SQL Serer Query Optimizer com a ajuda do Cardinality Estimator usou as estatísticas dos índices disponíveis para determinar de que maneira recuperar os dados que você estava solicitando.
Agora, se os valores que você passou para as variáveis foram inicialmente como o seguinte:
..então o Query Optimizer rapidamente determinou que precisaria varrer todos os dados no índice
IX_TestTable_DateField1para recuperar todas as linhas (ou possivelmente até um pouco mais de 50% de todos os dados) para preencher a instrução com eficiência.Em vez de usar o
IX_TestTable_DateField1índice para recuperar todos os registros para corresponder à sua consulta, o otimizador de consulta optou por ler o índice clusterizado porque o índice clusterizado está em vigor nos dados. (Por que ler um índice não clusterizado para recuperar os dados, quando você pode ler o índice clusterizado e já possui os dados).Como essa foi a primeira execução, o Query Optimizer armazenou o plano de execução (otimizado para os valores iniciais) no cache do plano.
Sempre que uma nova consulta (com valores diferentes) chegar ao servidor, o QO verá que já possui um plano de execução que atende aos requisitos da consulta.
O grande contratempo é que o plano de consulta foi otimizado para os valores iniciais e quando você fornece novos valores para os parâmetros, o QO não vai criar um novo plano de consulta, porque isso é "caro".
Solução
Se você quiser que sua consulta use o índice, você terá que:
WITH RECOMPILEWITH OPTIMIZE FOR @startDate = '<value>', @endDate = '<value>'WITH OPTIMIZE FOR UNKNOWNDepois disso, você pode observar que os dados são recuperados usando seu índice.
Leitura de referência