Missing Index Einträge in SQL Server

Wenn man sich fragt, was eine Datenbank eigentlich ausmacht, wird sehr wahrscheinlich die Antwort auf Indexe fallen. Diese sind eine sehr mächtige Komponente. Ein sinnvoll eingerichteter Index kann unter Umständen dafür sorgen, dass auch sehr anspruchsvolle Abfragen in einer sehr guten Zeit ausgeführt werden können. Hier sind in bestimmten Fällen Performanceverbesserungen von über 90% möglich.

Im Gegenzug können schlechte oder insbesondere zu viele Indexe auch zu großen Performanceproblemen führen. Hier muss man sich vor Augen halten, dass jeder Index bei Datenänderungen (insert, update, delete) zusätzlichen Pflegeaufwand bedeutet.

Man sollte sich jedoch nicht davor scheuen, Indexe gar nicht oder nur sehr sporadisch zu verwenden. In diesem Fall lohnt es sich die Missing Index Vorschläge anzuschauen. Was das ist und wie man dieses Feature am besten nutzt, schauen wir uns in diesem Artikel genauer an.

Missing Indexes

Wenn man eine Abfrage in SQL Server ausführt, werden ein paar Schritte vor der tatsächlichen Durchführung unternommen. Dazu zählt das Aufstellen eines Ausführungsplans. Hierbei ermittelt das Herzstück einer Datenbank, der sogenannte Query Optimizer, wie am besten auf die benötigten Daten zugegriffen werden kann. Wesentliche Aspekte sind hierbei:

der Umfang der Daten der an der Abfrage beteiligten Tabellen
der geschätzte Umfang der Ergebnisdaten. Ausgangspunkt sind hier Statistiken zu den Tabellen- und Spalteninhalten
inwieweit ein existierender Index genutzt werden kann um die benötigten Daten schneller zu adressieren

Ziel ist hierbei, die Kosten, eine DB-interne Metrik des Optimizers, für eine Abfrage zu minimieren.

Ausgehend von den oben genannten, aber auch weiteren Kriterien, kann der Optimizer auf Grund von Statistiken und Kostenschätzungen Vermutungen anstellen, dass die Einführung eines oder mehrerer Indexe die Performance der Datenbank erhöhen könnte. Diese Information wird an den Benutzer als Missing Index Vorschlag weitergegeben.

Die entsprechenden Informationen werden in den Tabellen wie sys.dm_db_missing_index_group_stats und sys.dm_db_missing_index_details gespeichert. Innerhalb der Einträge wird unter anderem festgehalten, wie dieser Index aussehen kann, auf welche Tabelle er angewendet werden soll und wie viel Kosten und Performance eingespart werden kann. Diese äußern sich in Form von avg_total_user_cost und avg_user_impact. Es ist wichtig anzumerken, dass die vorgeschlagenen Indexe nur für korrespondierende Abfragen optimal sind. Sie müssen also nicht für alle ähnlichen Abfragen einen Vorteil bringen. Es ist daher nicht empfehlenswert alle Missing Index Vorschläge ungeprüft zu übernehmen, da sonst die Tabellen mit Indexen überschwemmt werden könnten und allein das Aktualisieren aller Indexe im Zusammenhang mit Datenänderungen mehr Performance kosten könnte, als die Indexe bei Abfragen einsparen.

Erkennen von Missing Indexes

Wie erkennt man nun einen Missing Index, den man umsetzen könnte? Es empfiehlt sich die Vorschläge umzusetzen, die eine hohe Anzahl an Aufrufen haben und einen hohen Wert an Kosten sparen. Leider sind Worte wie ‘hoch’ sehr schwammig. Wie in allen Optimierungsfragen, gibt es keine richtige Antwort. Jedes System hat eine andere Anforderung und Belastung anhand derer ein gutes Maß zum Abwägen gewählt werden sollte. Sehr nützliche Hinweise zu Indexproblemen erhalten Sie aus unserem SQL Server Assessment

In den folgenden Zeilen haben wir zudem ein Skript bereitgestellt, mit dem nach den besten Missing Index Vorschlägen gesucht werden kann.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

Ein Spezialfall

Die Missing Index Vorschläge stellen ein sehr nützliches Tool dar, eine Datenbank noch effizienter zu gestalten. Es gibt jedoch einen Fall, in dem der SQL Server auch lahmgelegt werden kann. Hat man einen Server nun lange am Stück laufen, sammeln sich in sys.dm_db_missing_index_group_stats_query eine große Anzahl an Einträgen (wenn entsprechend viele Queries laufen). Will man nun einen neuen Index auf eine Tabelle erstellen oder rebuilden, die einen solchen Missing Index hat, kann es zu einem sehr langen Blocking und damit einer langen Wartezeit kommen. Das liegt daran, dass jeder Eintrag ebenfalls verarbeitet wird. Dabei handelt es sich nicht um die Einträge zu dieser Tabelle, sondern um alle Tabellen in allen Datenbanken, die einen Missing Index Eintrag haben.

Der Grund für die vielen Einträge scheint ein Bug zu sein. In der MS Dokumentation zu der Tabelle sys.dm_db_missing_index_group_stats_query steht geschrieben, dass diese nur 600 Einträge sammelt. Schaut man sich dagegen Berichte aus dem Internet an, wird diese Zahl um ein Vielfaches überschritten. Leider wissen wir zu diesem Zeitpunkt nicht, ob Microsoft den Bug behoben hat, oder ob dieser noch existiert.

Wenn es zu diesem Problem kommen sollte, sind die Lösungsmöglichkeiten hierbei sehr begrenzt. Eine Lösung kann sein, den SQL Server neu zu starten. Denn somit werden alle Missing Index Einträge gelöscht. Jedoch wollen wir dringend von einem Neustart abraten. Eine weitere Möglichkeit ist, einen passenden Index hinzuzufügen, oder die verantwortliche Tabelle zu rebuilden. Um herauszufinden, welche Objekte hinter diesen Problemen stecken, können Sie dieses Skript ausführen.

SELECT TOP 10 mid.database_id, mid.object_id, COUNT(*) c
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats_query migs
ON migs.group_handle = mig.index_group_handle
GROUP BY mid.database_id,
         mid.object_id
ORDER BY c desc

Teilweise erhält man die Information zu Missing Indexes auch durch Betrachten des Ausführungsplans oder aus dem seit MSSQL 2016 angebotenen QueryStore.

Nehmen wir beispielsweise die im oberen Teil der folgenden Abbildung dargestellte Abfrage aus der AdventureWorks2019 Datenbank, so zeigt bereits der geschätzte (nicht erst der tatsächliche) Ausführungsplan u.a. den in grün dargestellten Hinweis auf eine Optimierung durch Verwendung eines Index auf der Spalte LineTotal. Laut Schätzung führt dieser zu einer Verbesserung um 99,59 %. Die geschätzten Kosten betragen hier 1,13626. Nach Implementierung des Index konnten diese auf 0,0039295 reduziert werden.

Geschätzter Ausführungsplan im Originalzustand der Datenbank

Geschätzter Ausführungsplan nach Implementierung des Indexvorschlags

Fazit

Wir hoffen, wir konnten mit diesem Artikel erklären, was ein Missing Index Eintrag ist und wie man mit entsprechenden Vorschlägen die Performance Ihres SQL Servers verbessern kann. Sollten Sie Fragen zu diesem Thema haben, können Sie sich gerne an unsere Experten wenden und einen unverbindlichen Termin über unser Kontaktformular vereinbaren.