Gefilterte SQL Server-Indizes für Warteschlangen

Eine Auftragstabelle enthält Millionen erledigte Datensätze, während Hintergrundprozesse immer wieder wenige offene Aufträge suchen. Ein vollständiger Index kann diese Suche beschleunigen, umfasst aber weiterhin die gesamte Historie. Ein gefilterter Index bildet stattdessen nur die fachlich relevante Teilmenge ab.

Maßgeblich ist die aktive Datenmenge. Eine Tabelle mit 50 Millionen Zeilen und 200 offenen Aufträgen verhält sich anders als dieselbe Tabelle mit 20 Millionen offenen Aufträgen nach einer Störung. Prüfen Sie beide Zustände. Ein bei leerer Warteschlange überzeugender Index kann unter Rückstau andere Pläne und Kosten zeigen.

Das fachliche Prädikat exakt abbilden

Im Beispiel steht null für offen und eins für erledigt. Gemeint ist der numerische Wert 0, nicht SQL NULL. Der Index sortiert ausschließlich offene Aufträge nach Erstellungszeit und ID. CustomerId wird eingeschlossen, weil die Ausgabe den Wert benötigt, ohne danach zu suchen oder zu sortieren. Erwartet werden die Aufträge 2 und 3; die kleine Datenmenge demonstriert Funktion, keine belastbare Performance.

CREATE TABLE #Work
(
    WorkId bigint NOT NULL PRIMARY KEY,
    Status tinyint NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    CustomerId int NOT NULL
);
INSERT #Work VALUES
(1,1,'2024-01-01',10),(2,0,'2024-01-02',20),
(3,0,'2024-01-03',10),(4,1,'2024-01-04',30);

CREATE INDEX IX_Work_Pending
ON #Work(CreatedAt, WorkId)
INCLUDE(CustomerId)
WHERE Status = 0;

SELECT TOP (20) WorkId, CreatedAt, CustomerId
FROM #Work
WHERE Status = 0
ORDER BY CreatedAt, WorkId;

SELECT i.name, p.row_count, p.used_page_count
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.dm_db_partition_stats AS p
  ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID('tempdb..#Work');

DROP TABLE #Work;

Vergleichen Sie bei echten Daten Indexseiten, logische Zugriffe und gelesene Zeilen für normalen und maximalen Rückstau. Ein Index nur auf dem Status kann weiterhin eine Sortierung oder zahlreiche Tabellenzugriffe erfordern. Ordnen Sie Such- und Sortierspalten bewusst an. Große JSON-Nutzdaten lassen sich häufig besser nach der begrenzten Auswahl der Auftrags-IDs nachladen.

Gefilterte Indizes setzen passende Verbindungseinstellungen voraus. Dazu gehören aktivierte Optionen ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL und ARITHABORT sowie deaktiviertes NUMERIC_ROUNDABORT. Wenn ein Administrator erfolgreich testet, die Anwendung aber beim Schreiben scheitert, vergleichen Sie die SET-Optionen der tatsächlichen Verbindung.

Weshalb SQL Server den Index nicht immer verwendet

Der Optimierer muss beweisen können, dass der Index sämtliche möglichen Treffer enthält. Ein wiederverwendbarer Plan mit Status = @Status darf sich gewöhnlich nicht auf einen Index beschränken, der nur Status = 0 enthält. Derselbe Plan könnte später erledigte Aufträge lesen sollen. Ein einmaliger Test mit Parameterwert null beseitigt dieses Problem nicht.

Eine eigene Abfrage für offene Aufträge mit konstantem Prädikat ist oft die verständlichste Lösung. Alternativ kann eine Neukompilierung der einzelnen Anweisung sinnvoll sein, wenn ihr Aufwand vertretbar ist. Erzwingen Sie den Index nicht vorschnell. Ein unzulässiger Hinweis auf einen gefilterten Index kann die Planerstellung verhindern. Testen Sie die echte Parametrisierung, einschließlich einer gegebenenfalls erzwungenen Parametrisierung.

Die gefilterte Statistik beschreibt die aktive Teilmenge. Das kann Schätzungen verbessern, ersetzt aber keine Prüfung bei schnell wechselndem Inhalt. Vergleichen Sie geschätzte und tatsächliche Zeilen und berücksichtigen Sie Statuswechsel. Eine Statistik der Gesamttabelle beschreibt offene Aufträge nicht automatisch ausreichend. Bei einem IS-NULL-Filter sollte außerdem geprüft werden, ob die Filterspalte eingeschlossen werden muss.

Schnelle Suche ist noch keine sichere Auftragsvergabe

Der Wechsel von offen zu erledigt entfernt einen Indexeintrag; erneutes Öffnen fügt ihn hinzu. Die Historie belastet diesen Index weniger, Statuswechsel bleiben jedoch Schreiboperationen. Beobachten Sie deren Latenz und mögliche Konkurrenz um denselben vorderen Bereich der Warteschlange.

Die gezeigte SELECT-Abfrage reserviert keinen Auftrag. Zwei Prozesse können dieselben IDs lesen, bevor einer den Status ändert. Eine produktive Warteschlange benötigt eine transaktionale Reservierung, Besitzer- oder Lease-Daten, Wiederholungen und eine Regel für abgestürzte Bearbeiter. READPAST und Update-Sperren hängen von der gewählten Isolation ab und sollten nicht ohne Prüfung ergänzt werden.

Untersuchen Sie insbesondere den Rückstau nach Ausfällen und dauerhaft fehlerhafte Aufträge am Anfang der Warteschlange. Entscheidend ist, ob sich der Rückstand abbauen lässt, ohne normale Datenbankzugriffe zu verdrängen. Der Index soll offene Arbeit kostengünstig auffindbar machen; Reservierung, Wiederaufnahme und Aufbewahrung bleiben eigenständige fachliche Anforderungen.

Technische Referenzen: Microsoft Learn: Filtered indexes · Microsoft Learn: Index design.