SQL Server não usa índice não clusterizado

Question

Eu tenho uma tabela que contém cerca de 470mln de linhas. Eu gostaria de selecionar dados com base na data. Eu tenho dois índices criados nesta tabela. Um é agrupado e outro não agrupado na coluna de data (a data é armazenada como INT). Tenho uma declaração simples de seleção:

select *
from big_table
where [date] BETWEEN 20200820 AND 20200828

O problema é que o plano de consulta usa varredura de índice clusterizado em vez de busca + keylookup não clusterizada. Planeje o seguinte:

As estimativas geradas no plano de consulta são boas, as estatísticas estão atualizadas. Este intervalo de datas deve fornecer cerca de 5 milhões de linhas. Quando eu forneço a dica de índice, esta seleção é concluída em alguns segundos - sem dica, leva alguns minutos para terminar.

Este é o SQL Server 2019 e, de modo geral, percebi que o banco de dados prefere varreduras de índice clusterizado que usam não clusterizado + keylookups, mesmo em tabelas maiores.

Prefiro não usar a dica porque:

• às vezes eu seleciono intervalos mais amplos onde a varredura de índice clusterizado deve ser desejável
• tabela é usada na visão e não posso fornecer dica de índice para a visão

Existe alguma explicação de por que o db não está usando o índice NC neste caso?

Links para planos de consulta:

• https://www.brentozar.com/pastetheplan/?id=SkaQFqLmD
• https://www.brentozar.com/pastetheplan/?id=Hkh7q587P

Answer 1

Parece que o SQL Server não está usando esse índice por padrão porque:

• é um índice filtrado e
• sua consulta é parametrizada

Você pode ver este aviso no XML do plano de execução:

<UnmatchedIndexes>
  <Parameterization>
    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
  </Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />

O SQL Server não sabe quais são os valores dos parâmetros (porque eles estão em variáveis), portanto, ele não pode usar o índice filtrado com segurança.

Uma solução é usar dicas de índice (como você mencionou, isso não é o ideal).

Outra maneira de contornar isso é usar SQL dinâmico, conforme descrito por Jeremiah Peschka aqui:

Índices filtrados e SQL dinâmico

Não sei como o índice filtrado é ... filtrado. Você pode conseguir incorporar o literal em apenas um dos dois valores, para limitar o inchaço do cache do plano.

Answer 2

Existe alguma explicação de por que o db não está usando o índice NC neste caso?

O custo estimado desse plano é menor. A varredura de índice clusterizado usa E / S sequencial e a verificação de índice não clusterizado + pesquisa de marcador usa I / O mais aleatório. Então, qual é realmente mais rápido pode depender do seu hardware.

Veja as estatísticas de espera da consulta. Para a varredura de índice clusterizado é

        <WaitStats>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="3188040" WaitCount="31753"/>
          <Wait WaitType="CXPACKET" WaitTimeMs="566095" WaitCount="6329619"/>
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="21354" WaitCount="29774"/>
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="11994" WaitCount="8679127"/>
          <Wait WaitType="SLEEP_BPOOL_STEAL" WaitTimeMs="7435" WaitCount="439"/>
          <Wait WaitType="LATCH_EX" WaitTimeMs="206" WaitCount="35"/>
          <Wait WaitType="SESSION_WAIT_STATS_CHILDREN" WaitTimeMs="8" WaitCount="6"/>
          <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="5" WaitCount="2"/>
        </WaitStats>
        <QueryTimeStats ElapsedTime="247180" CpuTime="232769"/>

Para o índice não agrupado é

        <WaitStats>
          <Wait WaitType="CXPACKET" WaitTimeMs="451425" WaitCount="4834017"/>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="43202" WaitCount="41863"/>
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="11453" WaitCount="11288"/>
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="2823" WaitCount="4051831"/>
          <Wait WaitType="LCK_M_S" WaitTimeMs="1366" WaitCount="1"/>
          <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="152" WaitCount="37550"/>
          <Wait WaitType="PAGEIOLATCH_UP" WaitTimeMs="49" WaitCount="4"/>
          <Wait WaitType="LATCH_EX" WaitTimeMs="11" WaitCount="14"/>
          <Wait WaitType="LATCH_SH" WaitTimeMs="1" WaitCount="3"/>
        </WaitStats>
        <QueryTimeStats ElapsedTime="67529" CpuTime="119828"/>

Mas os dois planos são muito caros, então você deve fazer algo a respeito. As opções incluem

1. Substituir o índice clusterizado existente por algo mais útil, como adicionar Date ao primeiro índice e, em seguida, particionar o índice clusterizado por data.
2. Armazenar esta tabela como um Columnstore em cluster em vez de um Índice em cluster
3. Não está em execução select *e adiciona colunas incluídas selecionadas ao índice de data.