Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Nicht verwendete Indizes (DMV)

17. Juni 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Eine der Performanz-Faktoren beim Datenbank-Design sind die richtige Verwendung von Indizes: So viele wie nötig, so wenig wie möglich. Überflüssige Indizes verringern unnötig die Schreibgeschwindigkeit, da sie immer mit aktualisiert werden müssen, wenn INSERTs oder UPDATEs auf die Index-Felder erfolgen.

Über die DMV sys.dm_db_index_usage_stats kann man ermitteln, welche Indizes wie häufig und wann zuletzt verwendete wurden, entsprechend kann man hier über auch die Indizes selektieren, die nicht (mehr) verwendet werden, um diese ggf. zu entfernen. 

Untenstehend ein Script zum Ermitteln von nicht/selten und/oder seit langem nicht mehr verwendeten Indizes. Es filtert dabei schon

-          Indizes zu leeren Tabellen (welche entsprechend auch nicht verwendet worden sein können)

-          PrimaryKey, Unique und Clustered (habe eine Logikfunktion und werden deshalb benötigt)

-          Desaktivierte Indizes

heraus. Aber auch hier gilt wie immer, nicht einfach unbesehen alle selektierten Indizes rauswerfen, sondern zunächst jeden detaillierter prüfen.

 -- Ermittelt alle nicht oder seit langem nicht 
 -- & kaum verwendete Indizes  
 DECLARE @MinLastUsed datetime; DECLARE @MinUsage int; 
 SET @MinLastUsed = '20090101'; 
 SET @MinUsage = 20 
   
 SELECT SCH.name AS SchemaName, OBJ.name AS TableName, OBJ.type_desc AS TableType,  
  IDX.name AS IndexName, IDX.type_desc AS IndexType, 
  IUS.last_user_update AS LastUserUpdate, 
  IUS.last_system_update AS LastSystemUpdate, 
  (IUS.user_seeks + IUS.user_scans + IUS.user_lookups 
  + IUS.system_seeks + IUS.system_scans + IUS.system_lookups) AS UsageCount, 
  ISNULL(IUS.last_user_seek, IUS.last_user_scan) AS LastUserUsage, 
  STA.row_count AS DataRowCount 
 FROM sys.dm_db_index_usage_stats AS IUS 
  INNER JOIN sys.indexes AS IDX 
  ON IUS.object_id = IDX.object_id AND IUS.index_id = IDX.index_id 
  INNER JOIN sys.objects AS OBJ 
  ON IUS.object_id = OBJ.object_id 
  INNER JOIN sys.schemas AS SCH 
  ON OBJ.schema_id = SCH.schema_id 
  INNER JOIN sys.dm_db_partition_stats AS STA 
  ON STA.object_id = IDX.object_id 
  AND STA.index_id = IDX.index_id 
 WHERE IUS.database_id = DB_ID() -- Nur für die aktuelle DB 
  AND STA.row_count > 0 -- Keine Daten = keine Idx-Verwendung 
  AND IDX.type > 1 -- keine Heaps & CLUSTERED 
  AND IDX.is_unique = 0 -- keine Unique Indizes, weil benötigt für Logik 
  AND IDX.is_unique_constraint = 0 
  AND IDX.is_primary_key = 0 -- keine PKs, weil benötigt für Logik 
  AND IDX.is_disabled = 0 -- Deaktivierte werde eh nicht verwendet 
  AND OBJ.type <> 'S ' -- keine Systemtabellen 
  -- entweder "kaum" verwendet 
  AND (ISNULL(IUS.user_seeks + IUS.user_scans + IUS.user_lookups 
   + IUS.system_seeks + IUS.system_scans + IUS.system_lookups, 0)  
  < @MinUsage 
  OR -- oder das letzte Mal ist "lange" her 
  (ISNULL(IUS.last_user_seek, @MinLastUsed - 1) < @MinLastUsed 
  AND ISNULL(IUS.last_user_scan, @MinLastUsed - 1) < @MinLastUsed 
  AND ISNULL(IUS.last_user_lookup, @MinLastUsed - 1) < @MinLastUsed 
  )) 
 ORDER BY (IUS.user_seeks + IUS.user_scans + IUS.user_lookups 
  + IUS.system_seeks + IUS.system_scans + IUS.system_lookups), 
  IUS.last_user_update, IUS.last_system_update,
SCH.name, OBJ.Name, IDX.name

Diesen Post teilen

Repost 0
Um über die neuesten Artikel informiert zu werden, abonnieren:

Kommentiere diesen Post