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:

Overhead	Proz. Updates	Indexname	seeks	scans	updates	last_user_seek	last_user_scan	last_user_update
	1000.00	XAK1InsideSQL	1	0	10	2017-03-29 12:11:04.787	NULL	2017-03-29 12:09:37.140
Overhead	1000.00	XIE1InsideSQL	1	0	10	2017-03-29 12:11:04.787	NULL	2017-03-29 12:09:37.140
Overhead	100.00	XIE2InsideSQL	0	0	10	NULL	NULL	2017-03-29 12:09:37.140
	200.00	XPKInsideSQL	1	2	10	2017-03-29 12:11:04.787	2017-03-29 11:59:18.120	2017-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: