Kronester

In einer Datenbank können sich über die Zeit sehr viele Tabellen, und damit verbunden auch sehr viele Indizes ansammeln. Möchte man nun wissen welche dieser Indizes noch in Gebrauch sind, kann man dies mittels der Abfrage , wie sie im Beispiel unten gezeigt wird, ermitteln.

In der Dynamic-Management-View sys.dm_db_index_usage_stats sammelt SQL Server Statistikdaten über die Verwendung eines jeden Indexes der auf der SQL Server Instanz angesprochen wird.

Dies ist dann auch der Grund warum man die Datenbank im Beispiel (s.u.) explizit im CTE-Block der Abfrage angeben muss (@Database).

Die Statistik-Daten werden mit jedem Neustart des MSSQL-Service zurückgesetzt und zeigen daher nur die seit dem letzten Start aufgelaufenen Statistiken an. Vorsicht also mit voreiligen Schlüssen was das Löschen von diesen “unnützen” Indizes angeht!

Grundsätzlich kann man sich jedoch mal eine Liste ausgeben lassen und die enthaltenen Indizes einer genaueren Prüfung unterziehen.

Selektivität oder warum wird mein Index in einer Abfrage nicht verwendet?

Unter Selektivität eines Index versteht man die Anzahl von Datensätzen die von einem Index-Eintrag angesprochen werden. Je mehr Datensätze über einen Indexschlüssel selektiert würden, je schlechter ist die Selektivität. Der Optimierer von SQL Server bewertet, neben einigen anderen Kriterien, auch die Selektivität eines Index bei der Entscheidung ob dieser Index für eine Abfrage in Betracht gezogen werden muss. Hintergrund ist die triviale Tatsache dass ein Index im Verhältnis zum Scan der gesamten Tabelle ggf. mehr I/O-Aufkommen produzieren würde als der direkte Zugriff auf die Tabelle. Wenn dies der Fall ist, ist ein Table-Scan oft schneller (weil nicht andauernd zwischen Index und Tabelle hin und her gesprungen werden muss um selektierte Daten zu erhalten) als der Zugriff über einen Index.

Grundsätzlich kann man sich die Faustregel, wie im Beispiel dargestellt, zu Herzen nehmen. Da die Selektivität nicht das einzige Kriterium bei der Optimierung ist, kann es durchaus sein das ein Index verwendet wird obwohl die Selektivität schlecht ist.

z.B. wenn der Index die Abfrage komplett abdeckt ist dies meist am schnellsten (schmales Dataset) über den Scan des gesamten Index zu machen.

Also wie immer: den Einzelfall prüfen!

 T-SQL | 
 copy code |
?
 
01
USE AdventureWorks
02
go
03
 
04
DECLARE @DATABASE sysname
05
 
06
SET @DATABASE = 'AdventureWorks'
07
 
08
-- ----------------------------------------------------------------
09
-- unbenutzte Indizes feststellen
10
-- ----------------------------------------------------------------
11
 
12
;with NotUsed AS
13
(
14
    SELECT OBJECT_ID,index_id
15
    FROM sys.indexes
16
    EXCEPT
17
    SELECT s.OBJECT_ID,s.index_id
18
    FROM sys.dm_db_index_usage_stats s
19
    WHERE database_id=DB_ID(@DATABASE)
20
)
21
SELECT s.name AS SchemaName
22
     , o.name AS TabellenName
23
     , i.name AS IndexName
24
FROM sys.objects o
25
    join sys.schemas s ON  s.schema_id = o.schema_id
26
    join NotUsed n     ON  o.OBJECT_ID = n.OBJECT_ID
27
    join sys.indexes i ON  i.index_id  = n.index_id
28
                       and i.OBJECT_ID = n.OBJECT_ID
29
WHERE o.type = 'U'
30
 
31
-- ----------------------------------------------------------------
32
-- Faustregel
33
-- ----------------------------------------------------------------
34
 
35
-- Selektivität eines Index feststellen (excl. Covering-Index)
36
 
37
-- 0 bis 0.05     = Hohe Selektivität 
38
--                  (Indexverwendung wahrscheinlich)
39
 
40
-- 0.05 bis 0.10  = Mittlere Selektivität 
41
--                  (IndexVerwendung fraglich)
42
 
43
-- 0,10 und höher = Schlechte Selektivität 
44
--                  (Indexverwendung unwahrscheinlich)
45
 
46
 
47
-- ----------------------------------------------------------------
48
-- Beispiel: Schlechter selektiver Index
49
-- ----------------------------------------------------------------
50
 
51
-- select EmailPromotion,count(*) 
52
-- from Person.Contact 
53
-- group by EmailPromotion 
54
-- order by count(*)
55
 
56
CREATE INDEX IX_Non_Selective ON Person.Contact (EmailPromotion)
57
 
58
DBCC SHOW_STATISTICS ('Person.Contact','IX_Non_Selective')
59
     WITH NO_INFOMSGS , DENSITY_VECTOR 
60
 
61
SELECT * FROM Person.Contact WHERE EmailPromotion = 2
62
 
63
DROP INDEX IX_Non_Selective ON Person.Contact 
64
 
65
-- ----------------------------------------------------------------

have fun