Indexanalyse im SQL Server mit Query Store, Ausführungsplänen und DMVs

Eine fundierte Indexanalyse im SQL Server beginnt bei einer konkreten Abfrage: Wie häufig läuft sie, wie viele Ressourcen verbraucht sie und welchen Ausführungsplan verwendet sie? Erst danach lässt sich beurteilen, ob ein neuer oder geänderter Index hilft.

Eine belastbare Indexanalyse beginnt deshalb bei der Abfrage und ihrer tatsächlichen Last. Der Query Store zeigt, welche Abfragen über einen Zeitraum Ressourcen verbrauchen und ob sich ihre Pläne geändert haben. Der Ausführungsplan erklärt, wie SQL Server die Abfrage verarbeitet. Dynamische Verwaltungssichten – kurz DMVs – ergänzen Informationen zur Indexnutzung und zu möglichen Lücken.

Dieser Artikel zeigt, wie du langsame Abfragen mit Query Store, Ausführungsplänen und DMVs untersuchst. Ausgangspunkt ist die konkrete SQL-Anweisung: Wo entsteht die Last, hat sich der Ausführungsplan geändert und ist ein Index die passende Lösung? Wie du die Nutzung, Fragmentierung und Wartung bereits vorhandener Indizes bewertest, erklärt der ergänzende Beitrag Nutzung und Wartung vorhandener Indizes. Grundlagen zu Primärschlüsseln und Indizes findest du im Beitrag Primary Key und Indizes im MS SQL Server.

Indexanalyse im SQL Server: drei Werkzeuge und ihre Aufgaben

Die Reihenfolge ist wichtig: Erst eine relevante Abfrage finden, dann ihren Plan verstehen und schließlich einen Indexentwurf prüfen. Eine Liste vermeintlich „ungenutzter“ oder „fehlender“ Indizes allein ist noch keine Entscheidungsvorlage.

Voraussetzungen und Berechtigungen

Query Store ist seit SQL Server 2016 verfügbar. Bei neu angelegten Datenbanken unter SQL Server 2022 ist er standardmäßig aktiviert. Bei älteren oder migrierten Datenbanken solltest du den Zustand prüfen, statt ihn vorauszusetzen.

Für die Query-Store-Sichten benötigst du unter SQL Server 2022 und neuer in der Datenbank VIEW DATABASE PERFORMANCE STATE; bei älteren Versionen ist VIEW DATABASE STATE relevant. Für die hier verwendeten serverweiten Index-DMVs ist unter SQL Server 2022 und neuer VIEW SERVER PERFORMANCE STATE erforderlich, bei älteren Versionen VIEW SERVER STATE. Lass diese Rechte bei Bedarf gezielt durch einen Administrator vergeben.

Die folgenden Abfragen lesen Metadaten und Leistungsdaten. Das spätere Aktivieren des Query Store und das Erstellen eines Index ändern dagegen die Datenbankkonfiguration beziehungsweise Tabellenstruktur und sollten in deinen normalen Änderungsprozess eingebunden werden.

Schritt 1: Zustand des Query Store prüfen

Verbinde dich mit der betroffenen Datenbank und führe aus:

SELECT
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;

Für die laufende Erfassung sollte actual_state_desc auf READ_WRITE stehen. Steht dort READ_ONLY, werden möglicherweise keine neuen Laufzeitdaten mehr gesammelt. Eine Ursache kann sein, dass der für Query Store vorgesehene Speicherplatz ausgeschöpft ist. Prüfe dann Aufbewahrung, Bereinigungsmodus und Größenlimit, bevor du blind den Grenzwert erhöhst.

Ist Query Store für eine Datenbank deaktiviert, kann ein berechtigter Administrator ihn einschalten:

ALTER DATABASE [MeineDatenbank] SET QUERY_STORE = ON;
ALTER DATABASE [MeineDatenbank]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Ersetze MeineDatenbank durch den tatsächlichen Datenbanknamen. Nach dem Aktivieren entsteht keine rückwirkende Historie: Query Store muss zunächst einen repräsentativen Teil der Arbeitslast erfassen. Für eine Anwendung mit Wochenend- oder Monatsläufen reicht ein einzelner Vormittag als Beobachtungszeitraum nicht aus.

Schritt 2: Teure Abfragen im Query Store finden

In SQL Server Management Studio kannst du im Objekt-Explorer unter der Datenbank die Query Store-Berichte öffnen. Top Resource Consuming Queries ist ein guter Einstieg. Stelle dort einen passenden Zeitraum ein und wechsle je nach Problem zwischen gesamter CPU-Zeit, Dauer, logischen Lesevorgängen und Ausführungszahl. Regressed Queries hilft, wenn eine Abfrage nach einem Planwechsel langsamer geworden ist.

Die folgende T-SQL-Abfrage zeigt die Pläne mit der höchsten aufsummierten CPU-Zeit in vollständig begonnenen Query-Store-Intervallen der letzten 24 Stunden:

WITH workload AS
(
    SELECT
        p.query_id,
        p.plan_id,
        SUM(rs.count_executions) AS executions,
        SUM(rs.avg_cpu_time *
            CONVERT(float, rs.count_executions)) / 1000.0
            AS total_cpu_ms,
        SUM(rs.avg_duration *
            CONVERT(float, rs.count_executions)) / 1000.0
            AS total_duration_ms
    FROM sys.query_store_plan AS p
    JOIN sys.query_store_runtime_stats AS rs
        ON rs.plan_id = p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id =
           rs.runtime_stats_interval_id
    WHERE rsi.start_time >=
          DATEADD(hour, -24, SYSDATETIMEOFFSET())
      AND rs.execution_type = 0
    GROUP BY
        p.query_id,
        p.plan_id
)
SELECT TOP (20)
    w.query_id,
    w.plan_id,
    w.executions,
    CONVERT(decimal(18, 1), w.total_cpu_ms) AS total_cpu_ms,
    CONVERT(decimal(18, 1), w.total_duration_ms)
        AS total_duration_ms,
    LEFT(qt.query_sql_text, 400) AS query_text
FROM workload AS w
JOIN sys.query_store_query AS q
    ON q.query_id = w.query_id
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
ORDER BY w.total_cpu_ms DESC;

avg_cpu_time und avg_duration werden in Mikrosekunden gespeichert. Die Abfrage multipliziert die jeweiligen Durchschnittswerte mit der Ausführungszahl und rechnet das Ergebnis in Millisekunden um. Da Query Store Werte pro Zeitintervall zusammenfasst, ist die 24-Stunden-Grenze hier intervallbasiert und nicht sekundengenau. Außerdem berücksichtigt das Beispiel nur erfolgreich abgeschlossene Ausführungen (execution_type = 0).

Bewerte sowohl Gesamtkosten als auch Kosten pro Ausführung: Eine mäßig teure Abfrage kann bei zehntausenden Aufrufen die größte Gesamtlast erzeugen. Eine seltene Abfrage kann dagegen für einen einzelnen Benutzer besonders störend sein, obwohl sie in der CPU-Rangliste weit unten steht.

Schritt 3: Planänderungen erkennen

Notiere die query_id einer auffälligen Abfrage und prüfe, ob mehrere plan_id-Werte vorliegen. Vergleiche dabei möglichst denselben Zeitraum und eine ähnliche Arbeitslast. Ein neuer Plan ist nicht automatisch schlechter; entscheidend sind die zugehörigen Laufzeitwerte und die betroffenen Parameter.

Den im Query Store gespeicherten Plan einer bekannten plan_id kannst du auslesen:

DECLARE @plan_id bigint = 123; -- Anpassen

SELECT
    p.query_id,
    p.plan_id,
    p.is_forced_plan,
    TRY_CONVERT(xml, p.query_plan) AS query_plan_xml
FROM sys.query_store_plan AS p
WHERE p.plan_id = @plan_id;

Der gespeicherte Query-Store-Plan beschreibt den kompilierten Plan. Für Fragen wie „Wie viele Zeilen hat dieser Operator bei der problematischen Ausführung tatsächlich verarbeitet?“ brauchst du zusätzlich einen tatsächlichen Ausführungsplan einer passenden Ausführung. Führe problematische Schreibabfragen oder sehr teure Berichte dafür nicht unüberlegt auf dem Produktivsystem erneut aus.

Schritt 4: Indexanalyse im SQL Server: Den tatsächlichen Ausführungsplan lesen

In SQL Server Management Studio aktivierst du vor einem geeigneten Testlauf unter Abfrage → Tatsächlichen Ausführungsplan einschließen den tatsächlichen Plan. Der Standard-Shortcut ist Strg+M. Im Gegensatz zum geschätzten Plan wird die Abfrage dabei wirklich ausgeführt.

Konzentriere dich bei der Indexanalyse auf diese Fragen:

• Wie viele Zeilen wurden geschätzt und wie viele tatsächlich verarbeitet? Große Abweichungen können auf ungeeignete Statistiken, unterschiedliche Parameterwerte oder eine schwierige Schätzung hinweisen. Ein zusätzlicher Index behebt nicht jede Fehlschätzung.
• Welche Tabellen werden gelesen? Ein Scan ist bei kleinen Tabellen oder bei Abfragen über einen großen Teil der Daten oft völlig angemessen. Das Wort „Scan“ allein ist kein Fehlernachweis.
• Wie viele Zeilen passieren Key Lookups? Ein Lookup für wenige Zeilen kann günstig sein. Tausende wiederholte Lookups können teuer werden; dann lohnt ein Blick auf die benötigten Ausgabespalten und vorhandene Indexabdeckung.
• Wo entstehen Sortierungen, Hash-Operationen oder Warnungen? Ein Index mit passender Schlüsselreihenfolge kann eine Sortierung vermeiden. Warnungen und hohe Kosten können aber auch andere Ursachen haben.
• Welcher vorhandene Index wird tatsächlich verwendet? Vergleiche Schlüsselspalten, Reihenfolge, enthaltene Spalten und Filter mit den Bedingungen der Abfrage.

Für einen reproduzierbaren Test kannst du ergänzend die von der Abfrage verursachten Lesevorgänge und Laufzeiten ausgeben lassen:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Hier die zu untersuchende Abfrage mit geeigneten
-- Testparametern ausführen.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Die Ausgabe zu STATISTICS IO zeigt unter anderem logische Lesevorgänge. Diese sind häufig aussagekräftiger als eine einzelne gemessene Dauer, die auch von gleichzeitiger Last, Wartezeiten und Caches beeinflusst wird. Teste vor und nach einer Änderung mit vergleichbaren Parametern und Datenständen.

Schritt 5: Indexanalyse im SQL Server: Vorhandene Indizes mit DMVs prüfen

sys.dm_db_index_usage_stats zählt unter anderem Seeks, Scans, Lookups und durch Schreibvorgänge verursachte Indexaktualisierungen. Die folgende Abfrage zeigt die Werte für Benutzertabellen der aktuellen Datenbank:

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    i.is_primary_key,
    i.is_unique,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates,
    us.last_user_seek,
    us.last_user_scan
FROM sys.indexes AS i
JOIN sys.objects AS o
    ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE o.type = 'U'
  AND i.index_id > 0
  AND i.is_hypothetical = 0
ORDER BY
    schema_name,
    table_name,
    index_name;

Ein Index mit vielen user_updates und wenigen Lesezugriffen ist ein Prüfkandidat, kein automatischer Löschkandidat. user_updates zählt betroffene Schreiboperationen, nicht die Zahl geänderter Zeilen. Primärschlüssel und eindeutige Indizes können außerdem Datenregeln absichern, selbst wenn in diesem Zählerfenster kaum Lesezugriffe erscheinen.

Die DMV-Zähler beginnen nach einem Neustart der SQL-Server-Instanz neu. Auch bestimmte Datenbankzustände können Einträge entfernen. Ein Index, der nur beim Monatsabschluss gebraucht wird, kann nach wenigen Beobachtungstagen fälschlich „ungenutzt“ wirken. Das Startdatum der Instanz hilft bei der Einordnung:

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

Schritt 6: Hinweise auf fehlende Indizes prüfen

SQL Server sammelt während der Optimierung Vorschläge für möglicherweise hilfreiche Indizes. Diese Hinweise findest du im Ausführungsplan und in den Missing-Index-DMVs. Für die aktuelle Datenbank liefert diese Abfrage eine Übersicht:

SELECT TOP (50)
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
WHERE mid.database_id = DB_ID()
ORDER BY
    migs.user_seeks + migs.user_scans DESC;

equality_columns stammen aus Gleichheitsbedingungen, inequality_columns beispielsweise aus Bereichsvergleichen. included_columns sind zusätzliche Spalten, die zur Abdeckung einer Abfrage vorgeschlagen werden. avg_user_impact ist eine Schätzung des Optimizers, keine gemessene Beschleunigung nach dem Erstellen eines Index.

Übernimm solche Vorschläge nicht unverändert. Der Optimizer bewertet dabei zunächst eine einzelne Abfrage. Er schlägt beispielsweise keine eindeutigen oder gefilterten Indizes vor, bestimmt nicht die optimale Reihenfolge mehrerer Schlüsselspalten und berücksichtigt die Wartungskosten großer Included-Column-Listen nicht vollständig. Ähnliche Vorschläge für dieselbe Tabelle solltest du mit den bereits vorhandenen Indizes abgleichen. Auch diese DMV-Daten sind nicht dauerhaft gespeichert.

Ein Indexentwurf in der Praxis

Angenommen, eine häufig aufgerufene Abfrage filtert Bestellungen nach Kunde und einem Datumsbereich und sortiert die neuesten Bestellungen zuerst:

SELECT
    OrderID,
    OrderDate,
    Status
FROM dbo.Orders
WHERE CustomerID = @CustomerID
  AND OrderDate >= @FromDate
ORDER BY OrderDate DESC;

Wenn Query Store eine relevante Last zeigt, der tatsächliche Plan viele Seiten liest und kein vorhandener Index die Bedingungen gut unterstützt, wäre dieser Index ein Entwurf für einen Test:

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate DESC)
INCLUDE (Status);

Die Gleichheitsbedingung auf CustomerID steht am Anfang. OrderDate unterstützt den Datumsbereich und möglicherweise die verlangte Sortierung. Status ist für die Ausgabe enthalten, ohne selbst Teil der Suchreihenfolge zu sein. Ob OrderID zusätzlich enthalten sein muss, hängt unter anderem vom vorhandenen Tabellen- und Indexaufbau ab.

Das Beispiel ist bewusst kein allgemeines Rezept. Prüfe zuerst, ob ein ähnlicher Index bereits existiert. Miss danach die betreffende Abfrage mit repräsentativen Parametern und beobachte auch Schreiblast, Speicherbedarf und andere wichtige Abfragen. Ein Index, der einen Bericht verbessert, kann Inserts und Updates derselben Tabelle verteuern.

Änderungen mit Query Store nachmessen

Halte vor einer Änderung mindestens folgende Werte fest: query_id, plan_id, Ausführungszahl, CPU-Zeit, Dauer und logische Lesevorgänge im relevanten Zeitraum. Dokumentiere außerdem die verwendeten Parameter und den tatsächlichen Ausführungsplan eines passenden Tests.

Prüfe nach der Änderung dieselbe Abfrage erneut. Ein neuer Index ist erfolgreich, wenn sich die Gesamtwirkung auf die Arbeitslast verbessert – nicht bloß ein einzelner Testlauf. Vergleiche dafür ähnlich lange und ähnlich ausgelastete Zeitfenster. Achte darauf, ob SQL Server einen neuen Plan gewählt hat und ob andere Abfragen oder Schreibvorgänge unter dem zusätzlichen Index leiden.

Bei einem deutlichen Leistungsabfall nach einem Planwechsel kann ein früherer Plan im Query Store als kurzfristige Maßnahme erzwungen werden. Das ist kein Ersatz für die Ursachenanalyse: Datenverteilung, Statistiken, Parameter und Indexstruktur können sich weiter ändern. Überwache einen erzwungenen Plan und lege fest, wann die Maßnahme erneut geprüft wird.

Häufige Fehler bei der SQL-Server-Indexanalyse

• Jeden Scan als Problem behandeln: Für kleine Tabellen oder große Ergebnismengen kann ein Scan die richtige Wahl sein.
• Jeden Missing-Index-Hinweis ausführen: Vorschläge können sich überschneiden und berücksichtigen nicht die gesamte Schreib- und Speicherlast.
• „Keine Seeks“ mit „ungenutzt“ gleichsetzen: Ein Index kann gescannt werden, seltene wichtige Abfragen bedienen oder eine Eindeutigkeitsregel sichern.
• DMV-Zähler ohne Zeitraum bewerten: Nach einem Neustart fehlen ältere Nutzungsdaten.
• Nur die Dauer eines einzelnen Testlaufs vergleichen: Cache-Zustand, gleichzeitige Last und Parameter können das Ergebnis stark beeinflussen.
• Geschätzte und tatsächliche Pläne verwechseln: Der tatsächliche Plan einer ausgeführten Abfrage liefert zusätzliche Laufzeitinformationen und tatsächliche Zeilenzahlen.

Fazit: Die Abfrage entscheidet über den Index

Eine verlässliche Indexanalyse im SQL Server verbindet Query Store, tatsächliche Ausführungspläne und DMV-Daten. Erst zusammen zeigen sie, ob ein Indexentwurf zur Arbeitslast passt.

Erst aus diesen Informationen entsteht ein sinnvoller Indexentwurf. Teste ihn mit repräsentativen Parametern, vergleiche die Arbeitslast vor und nach der Änderung und berücksichtige die Kosten für Schreibvorgänge. So wird aus einer vermeintlich schnellen Indexempfehlung eine nachvollziehbare Verbesserung.

Weiterführende Informationen

• Grundlagen zur Bewertung vorhandener Indizes
• Primary Key und Indizes im MS SQL Server – Aufbau und Unterschiede
• Microsoft: Leistung mit Query Store überwachen
• Microsoft: Grenzen von Missing-Index-Vorschlägen