SQL Server no usa índice no agrupado

Question

Tengo una tabla que contiene aproximadamente 470 mln de filas. Me gustaría seleccionar datos basados ​​en la fecha. Tengo dos índices creados en esta tabla. Uno está agrupado uno y el otro no está agrupado en la columna de fecha (la fecha se almacena como INT). Tengo una declaración de selección simple:

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

El problema es que el plan de consultas utiliza un escaneo de índice agrupado en lugar de buscar no agrupado + keylookup. Planifique de la siguiente manera:

Las estimaciones generadas en el plan de consulta están bien, las estadísticas están actualizadas. Este intervalo de fechas debería generar alrededor de 5 millones de filas. Cuando proporciono una pista de índice, esta selección se completa en un par de segundos; sin ninguna pista, tarda un par de minutos en finalizar.

Este es SQL Server 2019 y, en términos generales, noté que db prefiere los análisis de índices agrupados que el uso de búsquedas de claves no agrupadas, incluso en tablas más grandes.

Preferiría no usar la pista porque:

• a veces selecciono rangos más amplios donde el escaneo de índice agrupado debería ser deseable
• la tabla se usa en la vista y no puedo proporcionar una sugerencia de índice a la vista

¿Hay alguna explicación de por qué db no usa el índice NC en este caso?

Enlaces a planes de consulta:

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

Answer 1

Parece que SQL Server no usa ese índice de forma predeterminada porque:

• es un índice filtrado y
• su consulta está parametrizada

Puede ver esta advertencia en el XML del plan de ejecución:

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

SQL Server no sabe cuáles son los valores de los parámetros (porque están en variables), por lo que no puede usar de forma segura el índice filtrado.

Una solución es usar sugerencias de índice (como mencionaste, esto no es ideal).

Otra forma de evitarlo es usar SQL dinámico, como lo describe Jeremiah Peschka aquí:

Índices filtrados y SQL dinámico

No sé cómo se ... filtra el índice filtrado. Es posible que pueda salirse con la suya incrustando el literal en solo uno de los dos valores, para limitar la acumulación de caché del plan.

Answer 2

¿Hay alguna explicación de por qué db no usa el índice NC en este caso?

El costo estimado de ese plan es menor. El escaneo de índice agrupado usa más IO secuencial y el escaneo de índice no agrupado + búsqueda de marcadores usa IO más aleatorio. Entonces, cuál es realmente más rápido puede depender de su hardware.

Mira las estadísticas de espera de la consulta. Para el escaneo de índice agrupado es

        <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 el índice no agrupado es

        <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"/>

Pero ambos planes son muy caros, por lo que debería hacer algo al respecto. Las opciones incluyen

1. Reemplazar el índice agrupado existente por algo más útil, como agregar Fecha al primer índice y luego particionar el índice agrupado por fecha.
2. Almacenamiento de esta tabla como un almacén de columnas agrupado en lugar de un índice agrupado
3. No se está ejecutando select *y agregue las columnas incluidas seleccionadas al índice de fecha.