SQL Server verwendet keinen nicht gruppierten Index
Ich habe eine Tabelle, die ungefähr 470 Millionen Zeilen enthält. Ich möchte Daten basierend auf dem Datum auswählen. Ich habe zwei Indizes für diese Tabelle erstellt. Einer ist geclustert, der andere ist nicht in der Datumsspalte geclustert (Datum wird als INT gespeichert). Ich habe eine einfache select-Anweisung:
select *
from big_table
where [date] BETWEEN 20200820 AND 20200828
Das Problem ist, dass der Abfrageplan einen Clustered-Index-Scan anstelle eines nicht-Clustered-Suchvorgangs + Keylookup verwendet. Planen Sie wie folgt:
Die im Abfrageplan generierten Schätzungen sind in Ordnung, die Statistiken sind aktuell. Dieser Datumsbereich sollte ungefähr 5 Millionen Zeilen liefern. Wenn ich einen Indexhinweis gebe, ist diese Auswahl in wenigen Sekunden abgeschlossen - ohne Hinweis dauert es einige Minuten, bis der Vorgang abgeschlossen ist.
Dies ist SQL Server 2019, und im Allgemeinen habe ich festgestellt, dass db Clustered-Index-Scans bevorzugt, bei denen nicht geclusterte + Keylookups auch in größeren Tabellen verwendet werden.
Ich würde lieber keinen Hinweis verwenden, weil:
- Manchmal wähle ich größere Bereiche aus, in denen ein Clustered-Index-Scan wünschenswert sein sollte
- Die Tabelle wird in der Ansicht verwendet und ich kann der Ansicht keinen Indexhinweis geben
Gibt es eine Erklärung, warum db in diesem Fall keinen NC-Index verwendet?
Links zu Abfrageplänen:
- https://www.brentozar.com/pastetheplan/?id=SkaQFqLmD
- https://www.brentozar.com/pastetheplan/?id=Hkh7q587P
Antworten
Es sieht so aus, als würde SQL Server diesen Index standardmäßig nicht verwenden, weil:
- Es ist ein gefilterter Index, und
- Ihre Abfrage ist parametrisiert
Sie können diese Warnung im XML-Ausführungsplan sehen:
<UnmatchedIndexes>
<Parameterization>
<Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
</Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />
SQL Server kennt die Parameterwerte nicht (da sie in Variablen enthalten sind), daher kann der gefilterte Index nicht sicher verwendet werden.
Eine Lösung besteht darin, Indexhinweise zu verwenden (wie Sie bereits erwähnt haben, ist dies nicht ideal).
Eine andere Möglichkeit, dies zu umgehen, ist die Verwendung von dynamischem SQL, wie von Jeremiah Peschka hier beschrieben:
Gefilterte Indizes und dynamisches SQL
Ich weiß nicht, wie der gefilterte Index ... gefiltert wird. Möglicherweise können Sie das Literal nur in einen der beiden Werte einbetten, um das Aufblähen des Plan-Cache zu begrenzen.
Gibt es eine Erklärung, warum db in diesem Fall keinen NC-Index verwendet?
Die geschätzten Kosten dieses Plans sind niedriger. Der Clustered-Index-Scan verwendet mehr sequentielle E / A und der nicht-Clustered-Index-Scan + Lesezeichen-Lookup verwendet mehr zufällige E / A. Welches also tatsächlich schneller ist, hängt möglicherweise von Ihrer Hardware ab.
Schauen Sie sich die Wartestatistiken für Abfragen an. Für den Clustered Index Scan ist 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"/>
Für den nicht gruppierten Index ist 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"/>
Aber beide Pläne sind sehr teuer, deshalb sollten Sie etwas dagegen tun. Zu den Optionen gehören
- Ersetzen des vorhandenen Clustered-Index durch etwas Nützlicheres, z. B. Hinzufügen von Datum zum ersten Index und anschließende Partitionierung des Clustered-Index nach Datum.
- Speichern dieser Tabelle als Clustered Columnstore anstelle eines Clustered Index
- Wird nicht ausgeführt
select *und fügt ausgewählte eingeschlossene Spalten zum Datumsindex hinzu.