Startseite > MS SQL Server > Nicht genutzte Indizes ermitteln

Nicht genutzte Indizes ermitteln


Um nicht genutzte Indizes einer Datenbank zu ermitteln kann über das SQL Server Managementstudio den Standard Bericht für die Indexnutzung verwendet werden. Für einen schnellen Überblick ist das ganz nett, aber man hat in diesen Bericht nicht die Möglichkeit auf nur ungenutzte Indizes zu filtern.

Damit ich dieses Ergebnis trotzdem relativ schnell ermitteln kann habe ich ein SQL-Skript geschrieben in dem ich dynamische Verwaltungssichten und Metadatenfunkionen verwende. Zusätzlich ist im Ergebnis auch der belegte Speicher des jeweiligen Indizes enthalten.

Verwendete Verwaltungssichten:
– sys.dm_db_index_usage_stats
– sys.dm_db_index_physical_stats
– sys.indexes

Verwendete Metadatenfunktionen:
– object_name()
– db_id()
– objectproperty()

Die Beschreibung dieser Sichten und Funktionen können in der Onlinedokumentation nachgelesen werden.

Das SQL-Skript:

/*Diese SQL-Skript ermittelt alle nicht genutzten nonclustered Indizes einer Datenbank inklusiver der
letzten Aktualisierung und der Größe des Indizes.
Es muss bei dieser Abfrage berücksichtigt werden das die dynamische Systemsicht sys.dm_db_index_usage_stats
bei jeden Neustart der SQL Engine auf null gesetzt werden.*/

SELECT object_name(iu.object_id) AS Tabellenname, i.name as Indexname,
       i.type_desc AS ‚Indextyp‘,
       iu.user_seeks AS ‚Suche durch Benutzerabfragen‘,
       iu.user_scans AS ‚Scans durch Benutzerabfragen‘,
       iu.user_lookups,
       iu.user_updates AS ‚Aktualisierung durch Benutzerabfragen‘,
       iu.last_user_update AS ‚Letzte Aktualisierung durch Benutzerabfragen‘,
       iph.page_count * 8192/1024 AS ‚Speicher weg in KB‘
FROM sys.dm_db_index_usage_stats iu
INNER JOIN sys.indexes i on iu.object_id = i.object_id AND iu.index_id = i.index_id
INNER JOIN sys.dm_db_index_physical_stats (db_id(),null,null,null,null) iph ON iph.object_id = i.object_id AND iph.index_id = i.index_id
WHERE iu.database_id = db_id()–Datenbankname als Wert für den Vorlageparameter angeben
       AND OBJECTPROPERTY(iu.object_id, ‚IsUserTable‘) = 1
       AND iu.index_id > 1
       AND iu.user_seeks = 0
       AND iu.user_scans = 0
       AND iu.user_lookups = 0
ORDER BY Tabellenname;
GO

Bei der Nutzung des dargestellten SQL-Skriptes werden alle nichtgenutzten und nichtgruppierte Indizes im Ergebnis ermittelt. Wenn auch die gruppierten Indizes mit angezeigt werden sollen, müssen in der Where-Klausel nur der Index_ID Wert von 1 auf 0 gesetzt werden.
Beim Start des Skriptes wird die DB_ID wird immer im Kontext mit der verbundenen Datenbank, auf die diese Abfrage ausgeführt wird ermittelt.
 
Je nach dem wie groß eure DB ist und wie viele Indizes vorhanden sind, kann die Ausführung des Skriptes auch etwas länger dauern. Ich habe zur Ermittlung der Größe die Anzahl der belegten Seiten aus der sys.db_dm_physical_stats verwendet, man kann auch die Sicht sys.sysindex mit der Spalte ‘dpages’ verwenden. Da diese Sicht aber nur noch aus Kompatibilitätsgründen vorhanden ist verwende ich sie nicht.

Gruß

Mario

Kategorien:MS SQL Server
  1. Es gibt noch keine Kommentare.
  1. No trackbacks yet.

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: