SQL Server non utilizza un indice non cluster

Question

Ho una tabella che contiene circa 470 mln di righe. Vorrei selezionare i dati in base alla data. Ho due indici creati su questa tabella. Uno è raggruppato e l'altro non è raggruppato nella colonna della data (la data è memorizzata come INT). Ho una semplice dichiarazione di selezione:

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

Il problema è che il piano di query utilizza la scansione dell'indice cluster invece di seek non cluster + keylookup. Pianifica come segue:

Le stime generate nel piano di query vanno bene, le statistiche sono aggiornate. Questo intervallo di date dovrebbe fornire circa 5 mln di righe. Quando fornisco un suggerimento per l'indice, questa selezione si completa in un paio di secondi - senza suggerimento ci vogliono un paio di minuti per finire.

Questo è SQL Server 2019 e in generale ho notato che db preferisce le scansioni degli indici cluster che utilizzano le keylookup non cluster + anche su tabelle più grandi.

Preferisco non usare il suggerimento perché:

• a volte seleziono intervalli più ampi in cui la scansione dell'indice cluster dovrebbe essere desiderabile
• table viene utilizzata nella vista e non posso fornire un suggerimento per l'indice alla vista

C'è qualche spiegazione per cui db non utilizza l'indice NC in questo caso?

Collegamenti ai piani di query:

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

Answer 1

Sembra che SQL Server non utilizzi quell'indice per impostazione predefinita perché:

• è un indice filtrato e
• la tua query è parametrizzata

È possibile visualizzare questo avviso nell'XML del piano di esecuzione:

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

SQL Server non sa quali siano i valori dei parametri (perché sono in variabili), quindi non può utilizzare in modo sicuro l'indice filtrato.

Una soluzione è usare i suggerimenti per l'indice (come hai detto, questo non è l'ideale).

Un altro modo per aggirare questo problema è usare l'SQL dinamico, come descritto da Jeremiah Peschka qui:

Indici filtrati e SQL dinamico

Non so come sia ... filtrato l'indice filtrato. Potresti riuscire a farla franca incorporando il letterale solo su uno dei due valori, per limitare il blocco della cache del piano.

Answer 2

C'è qualche spiegazione per cui db non utilizza l'indice NC in questo caso?

Il costo stimato di quel piano è inferiore. La scansione dell'indice cluster utilizza più I / O sequenziale e la scansione dell'indice non cluster + la ricerca dei segnalibri utilizza più I / O casuale. Quindi quale è effettivamente più veloce può dipendere dal tuo hardware.

Guarda le statistiche di attesa della query. Per la scansione dell'indice cluster è

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

Per l'indice non cluster è

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

Ma entrambi i piani sono molto costosi, quindi dovresti fare qualcosa al riguardo. Le opzioni includono

1. Sostituzione dell'indice cluster esistente con qualcosa di più utile, come l'aggiunta di Date al primo indice e quindi il partizionamento dell'indice cluster per data.
2. Archiviazione di questa tabella come Columnstore in cluster anziché come indice in cluster
3. Non in esecuzione select *e aggiungi le colonne incluse selezionate all'indice delle date.