Ermitteln ob in einer SQL Server Datenbank Indizes fehlen?
Häufig fragt man sich bei Performanceproblemen ob in der Datenbank eventuell Indizes fehlen.
Wenn der Query Optimizer eine SQL-Abfrage ausführt und er feststellt, dass ihm Indizes fehlen die er gerne gehabt hätte, so protokolliert er diese.
Jetzt fragt man sich natürlich, wie kann SQL Server feststellen, dass ihm Indizes fehlen? In diesem Fall möchte ich mal ein Beispiel aus der realen Welt bemühen.
Nehmen wir ein SQL Server Fachbuch mit 500 Buchseiten. Wenn ich Dich bitte das Buch zu nehmen und mir alles zum Thema -Indexoptimierung- herauszusuchen, würde man doch automatisch das Stichwortverzeichnis am Ende des Buches aufschlagen und in der sortierten Liste (darum handelt es sich ja in diesem Fall) den Punkt -Indexoptimierung- suchen. Wenn dieser im Stichwortverzeichnis gefunden wurde, dann braucht man ja nur noch zur angegebenen Buchseite zu springen und hat die Information(en) direkt ohne größeren Aufwand mit einem Zugriff.
Wenn Du aber das Stichwortverzeichnis aufschlagen möchtest und es ist nicht da, da es z.B. jemand herausgerissen hat, erkennst Du dies sofort. Der Index und bei einem Stichwortverzeichnis in einem Buch handelt es sich genau darum, ist nicht da und Du könntest mir das sagen bzw. eine Notiz für jemanden machen, dass hier der Index fehlt.
Du kannst natürlich trotzdem alles zu diesem Thema finden, allerdings dauert der Vorgang wesentlich länger, da Du jetzt das Ganze Buch von vorne bis hinten durchblättern musst. Du musst quasi einen kompletten Buchscan (Begriff wurde von mir erfuden :) machen. Bei SQL Server würde man von einem Tablescan sprechen, da ohne Index jetzt die gesamte Tabelle durhsucht werden muss. Hier sind dann natürlich wesentlich mehr Zugriffe notwendig. Erhöhte Lesevorgänge und damit I/O Zugriffe sind nötig und das kostet Zeit.
Genauso kann man sich den Vorgang bei SQL Server vorstellen, nur eben, dass hier die Informationen, Achtung hier fehlt ein Index, in den Dynamic Managment Views protokolliert werden. Eigentlich ganz einfach oder?
Im folgenden Youtube Video erkläre ich das noch einmal ausführlich.
In diesem Video gibt Michael R. Friebel - Spezialist für Datenanalyse und Datenbankexperte Informationen zum Thema SQL Server Indexgrundlagen.
Fehlende Indizes werden in den Management Views, die in jeder SQL Server Datenbank einsehbar sind, protokolliert. Ntürlich benötigst Du die entsprechenden Berechtigungen um darauf zuzugreifen.
Abbildung 1: Die SQL Server Dynamic Managment Views die die Informationen
über fehlende Indizes enthalten
Die Views oder auch DMV’s (Dynamic Managment Views) oder Systemviews genannt, die die Informationen über fehlende Indizes enthalten heissen:
[sys].[dm_db_missing_index_details]
[sys].[dm_db_missing_index_group_stats]
[sys].[dm_db_missing_index_group_stats_query]
[sys].[dm_db_missing_index_groups]
Generell kann man sagen, dass wenn auf dem Produktiven SQL Server in diesen Views Informationen stehen, dem Query Optimizer konkret Indizes gefehlt haben. Im allgemeinen bedeutet dies, dass hier Optimierungspotential bzgl. Indizierung besteht. Einfach gesagt, es wurden Indizes vermisst und die wurden protokolliert und die zugehörigen Informationen zu diesen fehlenden Indizes stehen in genannten Views.
Durch verjoinen der Views ist es jetzt möglich, konkretere Informationen über die fehlenden Indizes zu erhalten. Hier hat sich die SQL Community schon die Mühe gemacht und in unzähligen Artikeln die Zusammenhänge beschrieben.
In verschiedenen SQL Skripten werden Lösungen gezeigt, die sogar die fertigen Index Create Anweisungen der fehlenden Indizes auf Basis der Missing Index Views ausgeben.
Hier ein paar Links auf die genannten Artikel der SQL Community-Experten:
https://www.mssqltips.com/sqlservertip/1634/find-sql-server-missing-indexes-with-dmvs/
https://www.sqlshack.com/collecting-aggregating-analyzing-missing-sql-server-index-stats/
Wer Informationen zu den Missing Index Views an sich erhalten möchte wird unter dem folgenden Link fündig: https://learn.microsoft.com/de-de/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql?view=sql-server-ver16
Generell kann man sagen, dass die hier fehlenden Indexinfomationen nur als Indiz oder Hinweis gewertet werden sollten, da die Indexvorschläge in den Missing Index Tabellen auch Nachteile haben und eine ausführliche Indexanalyse nur bedingt ersetzen.
Die Nachteile der Informationen über fehlende Indizes aus den missing_index Managment Views sind:
- die Informationen in den Views gehen mit Neustart von SQL Server verloren / nach Neustart sollte einige Zeit vergehen um fehlende Indexinformationen einzufangen
- es werden nur rowbasierte NONCLUSTERED (nicht gruppierte) Indizes vorgeschlagen / nicht alle möglichen Indextypen werden hier vorgeschlagen und das ist nicht optimal, da z.B. ein Clustered oder gefilterter Index ggf. effektiver sein könnte.
- es können Doubletten der Vorschläge vorkommen (also mehrfach die gleiche redundante Indexempfehlung, die sich nur geringfügigst unterscheiden)
- andere bereits gemachte Index Vorschläge bzw. Indizes werden nicht berücksichtigt (bestehende Indizes werden nicht berücksichtigt)
- keine CLUSTERED (Gruppierte) Index Vorschläge / und auch keine COLUMN basierten Index Vorschläge / ebenfalls keine FilteredIndizes etc.
Eine ausführliche Indexanalyse ist z.B. mit dem Tool DTA (Database Tuning Advisor) oder im deutschen Datenbank Optimierungsratgeber möglich.
In meinem Online-Seminar -10 Punkte die SQL Server schneller machen- erkläre ich ausführlich das Thema Indexgrundlagen und Indexoptimierung und den Umgang mit dem Tool Profiler und dem Database Tuning Advisor.
SQL Server Optimierung -10 Punkte die SQL Server schneller machen-