Verwendung von Indizes

Ich brauche nicht nur einen schnellen Überblick (siehe Benutzerdefinierte Berichte und Indizes) über die Indizes, sondern würde auch noch gerne wissen, wie sie verwendet werden.

Verwendung der Indizes

Was nützen einem die schönsten Indizes, wenn sie niemand verwendet, oder wenn sie überwiegend im Weg sind, da eine Vielzahl von Updates nur einer kleinen Zahl von Einsatzmöglichkeiten gegenüber steht? Zum Glück können wir bei den Indizes, die seit dem letzten Neustart des SQL Servers verwendet oder aktualisiert wurden einige Informationen vom System bekommen. Man beachte aber, dass sich diese Informationen wirklich auf den Zeitraum der aktuellen Nutzung beziehen. Sollte ein Index noch nicht aktualisiert worden sein und auch noch nicht verwendet worden sein, so fehlt er in dieser Liste. Das bedeutet aber nicht, dass man ihn einfach löschen könnte. Für das Design und die Beurteilung der Indizes sollte man schon seine Anwendung kennen. Wir können uns hier immer nur den aktuellen Zustand anschauen, erst in der Summe der Beobachtungen und dem Wissen um Constraints kann man wirklich entscheiden, welche Indizes zu viel sind.

Hier also ein Statement, welches uns diesen Überblick verschaffen soll. Ich habe es wieder auf meine Beispiel-Tabelle dbo.InsideSQL ausgerichtet, es lässt sich aber beliebig anpassen und spätestens im benutzerdefinierten Bericht wird das ganze sehr einfach auf alle möglichen Tabellen anzuwenden.

DECLARE @ObjectName SYSNAME = '[dbo].[InsideSQL]';

SELECT CASE WHEN a.user_updates > a.user_seeks + a.user_scans + a.user_lookups AND b.is_unique <>1 THEN 'Overhead' ELSE '' END AS Overhead,
CAST(CASE WHEN a.user_seeks + a.user_scans + a.user_lookups > 0 THEN  ROUND(1.0 *a.user_updates / (a.user_seeks + a.user_scans + a.user_lookups) * 100.0 , 2)
ELSE 100.0
END AS DECIMAL(10,2)) AS Prozent_Updates,
OBJECT_SCHEMA_NAME(a.OBJECT_ID) AS Tabellenschema,
OBJECT_NAME(a.OBJECT_ID) AS Tabelle, b.name AS Indexname,  
a.index_id, a.user_seeks, a.user_scans, a.user_lookups, a.user_updates, a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update
FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b ON a.index_id = b.index_id
WHERE a.database_id = DB_ID()
AND
a.OBJECT_ID = b.OBJECT_ID
AND QUOTENAME(OBJECT_SCHEMA_NAME(a.OBJECT_ID)) + '.' + QUOTENAME(OBJECT_NAME(a.OBJECT_ID)) = @ObjectName
ORDER BY Tabelle, Indexname
;

Zum besseren Verständnis sei gesagt, dass in diese Tabelle insgesamt 10 Sätze eingefügt wurden und gegen diese Daten 3 SELECT Statements ausgeführt wurden.

Hier das (gekürzte) Ergebnis:

OverheadProz. UpdatesIndexnameseeksscansupdateslast_user_seeklast_user_scanlast_user_update
1000.00XAK1InsideSQL10102017-03-29 12:11:04.787NULL2017-03-29 12:09:37.140
Overhead1000.00XIE1InsideSQL10102017-03-29 12:11:04.787NULL2017-03-29 12:09:37.140
Overhead100.00XIE2InsideSQL0010NULLNULL2017-03-29 12:09:37.140
200.00XPKInsideSQL12102017-03-29 12:11:04.7872017-03-29 11:59:18.1202017-03-29 12:09:37.140

In der Spalte Overhead erscheint eine Warnung, wenn die Summe der Seeks, Scans und Lookups kleiner ist als die Anzahl der Updates. Dieser Hinweis erscheint aber nicht bei Indizes, die UNIQUE sind, da diese ihre Daseinsberechtigung evtl. nur diesem Umstand zu verdanken haben. Droppen sollte man diese UNIQUE-Indizes also nicht, bloß weil sie selten verwendet werden! In der Spalte Proz. Updates erfolgt die Angabe, wieviel Prozent die Updates im Verhältnis zu Seek + Scan + Lookup sind. Damit bekommt man schon mal einen Eindruck, wie die Verwendung des Index aussieht. In den letzten Spalten sehen wir genau zu welchem Zeitpunkt die verschiedenen Verwendungen des Index stattgefunden haben. Das ist z. B. dann ganz hilfreich, wenn man einen neuen Index eingeführt hat und überprüfen möchte, ob die Anwendung auch wirklich Gebrauch davon macht.

Der Report

Was eben noch eine deklarierte Variable war, ist jetzt ein Parameter für den Report: ObjectName. Zusätzlich verwende ich noch ServerName und DatabaseName um die Aussage des Berichts etwas sprechender zu machen.

Diese Werte gebe ich einfach per Ausdruck im Report aus und anschließend eine Tabelle mit dem Ergebnis des (jetzt parametrisierten) SQL Statements.
Das Ergebnis sieht dann so aus, wenn man den Bericht aus dem Objekt Explorer von der Tabelle dbo.InsideSQL her aufruft:

  insidesql_indizes_verwendung.rdl

Diese Datei über "Ziel speichern unter" als RDL-Datei abspeichern und im Management Studio verwenden!

Noch kein Feedback
Einen Kommentar hinterlassen

Sie müssen eingeloggt sein, um einen Kommentar zu hinterlassen. Jetzt einloggen!

Sie haben noch kein Konto. Sie können sich jetzt registrieren...
(Es dauert nur ein paar Sekunden!)