Eine Indexanalyse im MS SQL Server beantwortet mehr als die Frage, wie stark ein Index fragmentiert ist. Du willst wissen, welche Indizes vorhanden sind, ob Abfragen sie tatsächlich nutzen und welche Kosten sie bei Änderungen verursachen. Erst danach lässt sich entscheiden, ob ein Index bleiben, angepasst oder gewartet werden sollte. Dieser Leitfaden zeigt dafür drei lesende T-SQL-Abfragen und erklärt, wie du die Ergebnisse einordnest.
Was gehört zu einer Indexanalyse im MS SQL Server?
Indizes beschleunigen passende Such-, Join- und Sortieroperationen. Gleichzeitig benötigen sie Speicherplatz und werden bei INSERT, UPDATE und DELETE gepflegt. Eine brauchbare Analyse betrachtet deshalb Bestand, Nutzung, Größe und physischen Zustand zusammen. Ein einzelner DMV-Wert liefert noch keine Empfehlung zum Löschen oder Neuerstellen.
Die Beispiele gelten für klassische zeilenorientierte Indizes (Rowstore) in SQL Server. Columnstore- und speicheroptimierte Indizes benötigen teils andere Kennzahlen und DMVs. Falls du zunächst Clustered Index, Nonclustered Index und Primärschlüssel einordnen möchtest, lies Primary Key und Indizes im MS SQL Server.
1. Vorhandene Indizes und ihre Größe erfassen
Führe die Abfrage in der Datenbank aus, die du untersuchen möchtest. Sie zeigt Benutzer-Tabellen, Indexart, Eindeutigkeit, Einschränkungen und belegten Speicher. Die Größe ist ein erster Hinweis auf die Kosten eines Indexes, aber noch kein Beweis für mangelnden Nutzen.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
i.type_desc,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.is_disabled,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18, 2)) AS used_mb
FROM sys.indexes AS i
JOIN sys.tables AS t
ON t.object_id = i.object_id
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_partition_stats AS ps
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE i.index_id > 0
AND i.is_hypothetical = 0
GROUP BY
s.name, t.name, i.name, i.type_desc,
i.is_unique, i.is_primary_key,
i.is_unique_constraint, i.is_disabled
ORDER BY used_mb DESC, s.name, t.name, i.name;So liest du das Ergebnis: used_mb summiert die belegten Seiten über die Partitionen eines Indexes. is_primary_key und is_unique_constraint zeigen Indizes, die eine Datenregel absichern. Solche Indizes darfst du nicht allein aufgrund geringer Lesezugriffe entfernen. Für die Abfrage können je nach SQL-Server-Version und Berechtigungsmodell zusätzliche Rechte auf die DMV nötig sein.
2. Indexnutzung mit einer DMV auswerten
Für die Indexanalyse im MS SQL Server zählt sys.dm_db_index_usage_stats unter anderem Suchzugriffe (user_seeks), Scans, Lookups und Änderungen (user_updates). Ein LEFT JOIN lässt auch Indizes ohne aktuellen DMV-Eintrag im Ergebnis stehen. Die Abfrage ist lesend und ändert keine Indizes.
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
i.type_desc,
i.is_primary_key,
i.is_unique_constraint,
COALESCE(u.user_seeks, 0) AS user_seeks,
COALESCE(u.user_scans, 0) AS user_scans,
COALESCE(u.user_lookups, 0) AS user_lookups,
COALESCE(u.user_updates, 0) AS user_updates,
u.last_user_seek,
u.last_user_scan,
u.last_user_lookup,
u.last_user_update
FROM sys.indexes AS i
JOIN sys.tables AS t
ON t.object_id = i.object_id
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS u
ON u.database_id = DB_ID()
AND u.object_id = i.object_id
AND u.index_id = i.index_id
WHERE i.index_id > 0
AND i.is_hypothetical = 0
ORDER BY
COALESCE(u.user_updates, 0) DESC,
s.name, t.name, i.name;Wichtig: Die Zähler sind nicht dauerhaft. Nach einem Neustart der Datenbank-Engine beginnen sie erneut; auch bestimmte Datenbankzustände können Einträge entfernen. Eine Null bedeutet daher nur: Im beobachteten Zeitraum wurde keine entsprechende Nutzung erfasst. Prüfe den Beobachtungszeitraum mit SELECT sqlserver_start_time FROM sys.dm_os_sys_info; und berücksichtige Monatsabschlüsse, seltene Berichte und Wartungsjobs.
user_updates zählt Änderungsvorgänge, nicht geänderte Zeilen. Ein großer Wert gegenüber wenigen Lesezugriffen macht einen Index zu einem Prüfkandidaten, nicht automatisch zu einem Löschkandidaten. Auch ein Scan kann bei großen Ergebnismengen die richtige Wahl des Optimierers sein. Ein nur selten genutzter Index kann für eine wichtige Abfrage oder eine Eindeutigkeitsregel entscheidend sein. Laut Microsoft-Dokumentation der DMV benötigt die Abfrage auf SQL Server 2022 und neuer VIEW SERVER PERFORMANCE STATE; bei älteren SQL-Server-Versionen ist VIEW SERVER STATE erforderlich.
3. Fragmentierung und Seitendichte prüfen
Die Funktion sys.dm_db_index_physical_stats zeigt unter anderem page_count, Fragmentierung und Seitendichte. Untersuche zunächst eine konkret ausgewählte Tabelle. Ersetze dbo.DeineTabelle durch einen tatsächlich vorhandenen Namen und führe die Abfrage im Kontext der richtigen Datenbank aus.
DECLARE @object_id int = OBJECT_ID(N'dbo.DeineTabelle', N'U');
IF @object_id IS NULL
THROW 50000, 'Tabelle in der aktuellen Datenbank nicht gefunden.', 1;
SELECT
i.name AS index_name,
p.partition_number,
p.page_count,
CAST(p.avg_fragmentation_in_percent AS decimal(5, 2)) AS fragmentation_percent,
CAST(p.avg_page_space_used_in_percent AS decimal(5, 2)) AS page_density_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), @object_id, NULL, NULL, 'SAMPLED') AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
WHERE p.index_id > 0
AND p.index_level = 0
AND p.alloc_unit_type_desc = 'IN_ROW_DATA'
ORDER BY p.page_count DESC, i.name, p.partition_number;Der Prüfmodus SAMPLED liefert bei großen Objekten Näherungswerte. Bei weniger als 10.000 Seiten verwendet SQL Server intern den detaillierten Modus. Für eine schnelle erste Größen- und Fragmentierungsprüfung kann LIMITED günstiger sein; dabei bleibt die Seitendichte jedoch NULL. Auch diese DMV benötigt passende Rechte. Starte umfassende Scans großer Produktionsdatenbanken nicht unüberlegt, besonders nicht auf einer lesbaren Sekundärreplik einer Availability Group.
Fragmentierung beschreibt bei Rowstore-Indizes die logische Reihenfolge der Seiten. Eine geringe Seitendichte bedeutet, dass weniger Daten auf eine Seite passen und Abfragen möglicherweise mehr Seiten lesen müssen. Kleine Indizes können hohe Fragmentierungsprozente zeigen, ohne ein messbares Problem zu verursachen. Beziehe bei der Indexanalyse im MS SQL Server daher immer auch page_count, Zugriffsmuster und die Leistung der betroffenen Abfragen ein. Die Dokumentation zu sys.dm_db_index_physical_stats erklärt die Messwerte und Scanmodi.
Wann ist Indexwartung sinnvoll?
Starre Regeln wie „ab 10 % reorganisieren, ab 30 % neu aufbauen“ sind keine zuverlässige allgemeine Entscheidungshilfe. Microsoft empfiehlt, Wartung am tatsächlichen Nutzen für die Arbeitslast auszurichten. Eine Reorganisation bearbeitet bei Rowstore-Indizes die Blattebene und benötigt meist weniger Ressourcen. Ein Neuaufbau erstellt den Index neu, kann aber mehr CPU, I/O, Protokoll- und Speicherplatz beanspruchen und je nach Edition, Version und Optionen den Zugriff stärker beeinträchtigen.
Ein Neuaufbau aktualisiert außerdem die Indexstatistik. Wenn eine Abfrage danach schneller läuft, kann die frischere Statistik statt der geringeren Fragmentierung der Grund sein. Ein gezieltes UPDATE STATISTICS ist dann möglicherweise günstiger. Eine Reorganisation aktualisiert die Statistik nicht. Für einen ausgewählten Index kann eine geplante Reorganisation so aussehen:
-- Beispielnamen ersetzen und nur nach Prüfung im Wartungsfenster ausführen:
ALTER INDEX [IX_DeineTabelle_Suchspalte]
ON [dbo].[DeineTabelle]
REORGANIZE;Prüfe vor einer Änderung die betroffenen Abfragen, Ressourcen, Laufzeit und einen aktuellen Wiederherstellungsweg. Verwende ALTER INDEX ... REBUILD nicht pauschal für alle Tabellen. Die Microsoft-Anleitung zur Indexwartung beschreibt die Auswirkungen und empfiehlt Messungen vor und nach einem Eingriff.
Indexanalyse im MS SQL Server: eine praktische Reihenfolge
- Arbeitslast festlegen: Welche Anwendungen, Berichte und Wartungsfenster sind wichtig? Sammle Daten über einen repräsentativen Zeitraum.
- Bestand und Nutzung erfassen: Führe die ersten beiden Leseabfragen aus. Markiere große Indizes mit vielen Änderungen und wenigen erfassten Lesezugriffen als Kandidaten.
- Ausnahmen prüfen: Schütze Primärschlüssel, Unique Constraints und Indizes für seltene, aber geschäftskritische Abfragen. Suche nach ähnlichen oder überlappenden Indizes, bevor du etwas entfernst.
- Physischen Zustand gezielt messen: Betrachte bei auffälligen Tabellen Seitendichte, Fragmentierung und Seitenzahl. Eine hohe Prozentzahl allein genügt nicht.
- Wirkung messen: Dokumentiere die Ausgangswerte, ändere jeweils nur gezielt etwas und vergleiche Lese- und Schreiblast danach. Behalte einen Rückweg für die Änderung.
Wenn das eigentliche Problem eine langsame Abfrage ist, beginne bei deren Laufzeit, Ausführungsplan und Query Store. Genau darum geht es im ergänzenden Beitrag Query Store, Ausführungspläne und DMVs zur Abfragediagnose. Dieser Artikel behandelt dagegen den Bestand, die Nutzung und die Pflege vorhandener Indizes. Beide Perspektiven gehören zusammen, beantworten aber unterschiedliche Fragen.
Häufige Fragen zur Indexanalyse
Kann ich einen Index mit null Seeks löschen?
Nein, nicht allein deshalb. Der DMV-Zähler kann nach einem Neustart leer sein. Der Index könnte Scans unterstützen, eine seltene Abfrage beschleunigen oder eine Datenregel absichern. Prüfe seine Funktion über einen ausreichenden Zeitraum und teste die Auswirkung einer Änderung.
Ist ein Index Scan immer schlecht?
Nein. Wenn eine Abfrage viele Zeilen benötigt, kann ein Scan günstiger sein als viele einzelne Suchzugriffe. Bewerte den tatsächlichen Ausführungsplan und die gelesenen Datenmengen.
Muss ich fragmentierte Indizes regelmäßig neu aufbauen?
Nein. Wartung lohnt sich nur, wenn sie bei deiner Arbeitslast einen messbaren Vorteil bringt. Kleine Indizes und moderne Speichersysteme können hohe Fragmentierungswerte ohne spürbaren Nachteil zeigen. Prüfe auch die Seitendichte und die Aktualität der Statistiken.
Fazit
Eine gute Indexanalyse im MS SQL Server verbindet Katalogdaten, Nutzungszähler und physische Kennzahlen mit dem Verhalten echter Abfragen. Die drei Leseabfragen liefern einen belastbaren Startpunkt, ersetzen aber keine Beobachtung über einen repräsentativen Zeitraum. Pflege oder entferne Indizes erst, wenn du ihren Nutzen und die Kosten der Änderung nachvollziehen kannst.