Um die Systemperformance eines fremden Systems bewerten zu können, ist mein erster Weg immer der Abruf von Indexstatistiken. Indexe mögen zwar für die Abfrage von Daten sinnvoll sein; es kann aber bei “zu viel des Guten” auch genau zum Gegenteil führen. Insbesondere dann, wenn ein Index vom System nicht korrekt genutzt werden kann. Mein erster Anlaufpunkt bei der Analyse von Performanceengpässen ist immer die DMV [sys].[dm_db_index_usage_stats]. Mit [sys].[dm_db_index_usage_stats] ist es möglich, die Anzahl verschiedener Arten von Indexvorgängen und den Zeitpunkt auszugeben, wann die einzelnen Vorgänge zuletzt ausgeführt wurden. Diese Informationen sind für die Bewertung teurer Indexaktionen (z. B. Keylookups] relevant und können einem DBA und Programmierer sehr schnell einen Einblick in die Performancemöglichkeiten des Systems geben. Mit dem nachfolgenden Beispiel soll eine solche Analyse eines Systems an Hand der vorliegenden Werte erklärt werden.

Technisches Umfeld

Für die nachfolgenden Beispiele soll eine einfache Relation dienen, die sowohl einen Clustered Index als auch zwei weitere non clustered Indexe besitzt. Einer der non clustered Indexe ist ein Unique Index.

CREATE TABLE dbo.tbl_Demo
(
    Id      int         NOT NULL    IDENTITY (1, 1),
    col1    char(20)    NOT NULL,
    col2    char(20)    NOT NULL,
    IUser   sysname     NOT NULL    DEFAULT (ORIGINAL_LOGIN()),
    IDate   datetime    NOT NULL    DEFAULT (getdate())
);

-- Erstellen aller Indexe für dbo.tbl_Demo
CREATE UNIQUE CLUSTERED INDEX cix_tbl_Demo_Id ON dbo.tbl_Demo (Id);
CREATE UNIQUE INDEX uix_tbl_Demo_Col1 ON dbo.tbl_Demo (col1);
CREATE INDEX ix_tbl_Demo_Col2 ON dbo.tbl_Demo (col2);
CREATE INDEX ix_tbl_Demo_IUser ON dbo.tbl_Demo (IUser);
GO

Nachdem die Relation erstellt ist, werden 1.000 Datensätze in die Relation eingetragen. Da es hier nicht um die Demonstration der Datensätze selbst geht, reichen ein paar simple Testdaten, die automatisiert eingespielt werden.

DECLARE @i int = 1;

WHILE @i <= 1000 BEGIN
    INSERT INTO dbo.tbl_Demo (col1, col2)
    SELECT  'col1 = ' + CAST(@i AS varchar(4)),
            'col2 = ' + CAST(@i % 10 AS varchar(4))

    SET    @i += 1
END
GO

Workload generieren

Nachdem die technischen Rahmenbedingungen festgelegt wurden, können – ebenfalls in einer Schleife – jeweils 100 Mal verschiedene Abfragen gegen die Relation ausgeführt werden. Dabei wird jede mögliche Indexstrategie für die spätere Bewertung generieret. Die Analysen werden zeigen, wie oft welcher Index mit welcher Aktion verwendet wurde. Das nachfolgende Script simuliert einen Workload gegen die Datenbank:

DECLARE @i int = 1;
WHILE @i <= 100
BEGIN
    -- Clustered Index scan
    SELECT * FROM dbo.tbl_Demo;

    -- Clustered Index Seek
    SELECT * FROM dbo.tbl_Demo WHERE Id = @I;

    -- Seek in uix_tbl_Demo_Col1 mit Keylookup in Clustered Index
    SELECT * FROM dbo.tbl_Demo WHERE col1 = 'col1 = ' + CAST(@i AS varchar(4));

    -- Index Seek in ix_tbl_Demo_Col2
    SELECT Id, Col2 FROM dbo.tbl_Demo WHERE col2 = 'col2 = 5';

    -- Clustered Index scan in cix_tbl_Demo_Id
    SELECT Id, Col1, Col2 FROM dbo.tbl_Demo WHERE col2 = 'col2 = 5';

    -- Und die nächste Runde
    SET    @i += 1;
END
GO

Workload analysieren

Das obige Script führt in einer Schleife 100 Mal die sechs Abfragen mit unterschiedlichen Ausführungsstrategien aus. Bevor es an die Analyse der Ausführungspläne geht, schauen wir uns die Informationen an, die SQL Server nach der Ausführung im Bezug auf die Verwendung der implementierten Indexe gespeichert hat. Die nachfolgende Abfrage zeigt für jeden verwendeten Index die Häufigkeit seiner Verwendung in unterschiedlichen Ausführungsstrategien.

SELECT  OBJECT_NAME(i.object_id) AS table_name,
        i.index_id,
        i.name,
        i.type_desc,
        us.user_seeks,
        us.user_scans,
        us.user_lookups,
        us.user_updates
FROM    sys.Indexes i LEFT JOIN sys.dm_db_index_usage_stats us
        ON    (
                i.object_id = us.object_id AND
                i.index_id = us.index_id
            )
WHERE    i.object_id = OBJECT_ID('dbo.tbl_Demo', 'U')
ORDER BY
        i.index_id;

Die Abfrage auf [sys].[dm_db_index_usage_stats] liefert das nachfolgende Ergebnis:

Die Abfrage zeigt für jeden Index, der mindestens ein Mal verwendet wurde, die statistischen Hintergrundinformationen.

Mit Hilfe von [sys].[db_db_index_usage_stats] lässt sich sehr schnell ein erster Eindruck über die Performance verschaffen. Ich schaue IMMER zunächst auf die – teuren – Keylookups. Es mag Situationen geben, die einen Keylookup rechtfertigen; in diesem Beispiel sind sie jedoch echte Performancekiller. Entscheidend ist bei einer solchen Analyse immer die Anzahl dieser teuren Indexaktionen. Jedoch sollten die Ergebnisse von [sys].[dm_db_index_usage_stats] stets mit Vorsicht bewertet werden. Für Benutzungsstatistiken gilt es die nachfolgenden Hinweise zu kontrollieren:

• [sys].[dm_db_index_usage_stats] wird bei einem Neustart des SQL Server Dienstes immer vollständig geleert! Achten Sie also immer darauf, wann der Server das letzte Mal neu gestartet worden ist
• [sys].[dm_db_index_usage_stats] löscht statistische Werte für einen Index, wenn dieser Index mit REBUILD neu erstellt worden ist Fragen Sie also nach Möglichkeit bereits VOR der Analyse, ob tägliche Indexroutinen (Maintenancepläne) durchgeführt werden
• Indexe, die mit REORGANIZE reorganisiert werden, behalten ihre Statistiken und werden weiterhin mit den bisherigen statistischen Daten in [sys].[dm_db_index_usage_stats] gelistet
• [sys].[dm_db_index_usage_stats] führt keine Indexe auf, die NIE verwendet worden sind Es ist immer sinnvoll, die DMV mit einem LEFT JOIN mit sys.indexes abzufragen, um alle vorhandenen Indexe zu listen!
• Wird im laufenden Betrieb ein Index neu implementiert, so führt [sys].[dm_db_index_usage_stats] auch erst ab diesem Moment die Statistiken für den Index

Schaut man sich das Ergebnis des zuvor durchgeführten Workloads an und vergleicht es mit den durchgeführten Aktionen, sind die 1.000 Einfügevorgänge durch das Attribut [user_updates] repräsentiert. Hierbei ist es wichtig, zu wissen, dass JEDE DML-Aktion (INSERT / UPDATE / DELETE) durch diesen Wert repräsentiert wird.

Wird z. B. der folgende Befehl ausgeführt…

UPDATE dbo.tbl_Demo SET Col2 = 'This is mycol' WHERE Id = 10; 

sieht das Ergebnis der Statistiken wie folgt aus:

Im Ergebnis haben sich drei Werte geändert. Der Grund ist einfach zu erklären. Zunächst einmal musste für das Update nach dem Datensatz [Id] = 10 gesucht werden. Da die Id der eindeutige Schlüssel ist, konnte mit einem effizienten [user_seek] unmittelbar auf den gewünschten Datensatz zugegriffen werden. Weiterhin wurde das Attribut [col2] aktualisiert. Da der Clustered Index die Relation selbst ist, muss – der Logik folgend – dieser Index aktualisiert werden. Somit ist ein [user_update] für den Clustered Index die Konsequenz. Das Attribut [Col2] ist aber auch das Schlüsselattribut des Indexes ix_tbl_Demo_col2; somit ist auch für diesen Index ein [user_update] aufgezeichnet worden.

Sehr wichtig bei der Analyse dieser Werte ist, dass diese Statistiken nicht “pro Datensatz” erfolgen sondern IMMER pro “Benutzung”. Das folgende Beispiel verdeutlicht diesen Sachverhalt noch einmal:

UPDATE dbo.tbl_Demo SET Col2 = 'This is mycol' WHERE Id BETWEEN 10 AND 20;

Es werden zwar 11 Datensätze aktualisiert jedoch werden die dazu benötigten Indexoperationen ([user_seek] und [user_update]) nur ein Mal protokolliert.

Analyse des Ergebnisses von sys.dm_db_index_usage_stats

Um die einzelnen Indexoperationen zu verstehen, sollen die nachfolgenden Abbildungen der Ausführungspläne als Referenz dienen:

Abbildung 1: SELECT * FROM dbo.tbl_Demo;

Abbildung 2: SELECT * FROM dbo.tbl_Demo WHERE Id = @I;

Abbildung 3: SELECT * FROM dbo.tbl_Demo WHERE col1 = ‘col1 =  ‘ + CAST(@I AS varchar(4));

Abbildung 4: SELECT Id, Col2 FROM dbo.tbl_Demo WHERE col2 = ‘col2 =  5‘;

Abbildung 5: SELECT Id, Col1, Col2 FROM dbo.tbl_Demo WHERE col2 = ‘col2 =  5‘;

Abbildung 5: SELECT Id, Col2 FROM dbo.tbl_Demo WHERE col2 LIKE ‘col2 =  %‘;

Insgesamt werden – jeweils 100 Mal – sechs Abfragen mit unterschiedlichen Ausführungsplänen ausgeführt. Bei der Analyse der Ausführungspläne  können folgende Strategien festgestellt werden:

Zunächst einmal ist abzugrenzen, dass ein [user_seek] optimal ist und keiner weiteren Optimierung bedarf. Während Abfrage 2 ausschließlich einen [user_seek] verwendet, sieht die Analyse von Abfrage 3 kritisch aus. Es wird zwar mittels [user_seek] der Index [uix_tbl_Demo_col1] für gezielte Suchen verwendet ABER… – da nicht alle auszugebenen Attribute durch den Index selbst abgedeckt werden, muss Microsoft SQL Server die fehlenden Informationen aus dem Clustered Index (ist ja die Relation selbst!) ermitteln. Exakt diese Ausführungsstrategie zeigt auch der Ausführungsplan – indem ein “teurer” KeyLookup im Index [cix_tbl_Demo_Id] durchgeführt werden muss.

Ein [user_scan] ist “grundsätzlich” keine schlechte Ausführungsstrategie. Vielmehr kommt es immer auf die Datenmenge selbst an. Schaut man sich den Ausführungsplan von Abfrage 1 an, stellt man fest, dass der [user_scan] sinnvoll ist, da ja die vollständige Relation gelesen werden soll. Es gibt keine Predicates, die die Ergebnismenge einschränken.

Interessant ist jedoch die Analyse von Abfrage 5. Hier ist klar erkennbar, warum ein [user_scan] durchgeführt wird, obwohl ein Predicate verwendet wird; es ist die Datenausgabe, die in diesem Fall die Ausführungsstrategie beeinflusst. Wie man sehen kann, soll [Id], [col1] und [col2] ausgegeben werden. Der Index [ix_tbl_Demo_col2] deckt nur die Attribute [Id] und [col2] ab. Da aber [col1] ebenfalls ausgegeben werden muss, ist – basierend auf der Kardinalität des Predicates – ein [user_scan] über den clustered Index performanter als die Verwendung des implementierten Index [ix_tbl_Demo_col2] in Verbindung mit einem teuren KeyLookup (siehe nachfolgende Ausführungspläne).

Um die teuren Keylookups zu vermeiden, muss der Index uix_tbl_demo_col1 angepasst werden.

-- der clustered index wird nur zum Rücksetzen der statistischen Werte neu aufgebaut!
ALTER INDEX cix_tbl_demo_id ON dbo.tbl_Demo REBUILD;

-- Neue Definition von ix_tbl_Demo_Col2
DROP INDEX uix_tbl_Demo_Col1 ON dbo.tbl_Demo;
CREATE INDEX uix_tbl_Demo_Col1 ON dbo.tbl_Demo (col1) INCLUDE (col2, IUser, IDate);

Eine erneute Abfrage von [sys].[dm_db_index_usage_stats] zeigt das nachfolgende Ergebnis

Wie bereits weiter oben ausgeführt werden die Statistiken für Indexe, die neu erstellt werden, zurückgesetzt. Interessant ist hierbei aber der Unterschied zwischen den “Werten” für [cix_tbl_Demo_Id] und [uix_tbl_Demo_col1]! Während für den Clustered Index alle Werte auf “0” zurück gesetzt wurden, werden für den non clustered Index NULL-Werte für die Indexstrategien angezeigt. Das hängt damit zusammen, dass der Clustered Index während der Erstellung des Indexes [uix_tbl_Demo_col1] benötigt wurde, um die clustered Keys in den Index zu übertragen! Ansonsten wären für den clustered Index keine statistischen Informationen vorhanden und die Statistiken würden NULL-Werte anzeigen!

Nun wird das Script für den Workload erneut 100 Mal ausgeführt und die Statistiken für die Verwendung der Indexe sieht nun wie folgt aus:

PERFEKT – wie man sehr schön erkennen kann, werden keine kostenintensive Keylookups mehr auf den clustered Index ausgeführt. Dennoch – schaut man genau hin- hat sich nicht nur der Wert für die [user_lookups] geändert! Durch die Indexoptimierung konnte im Workload ein Anstieg der [user_seeks] für den Index [ix_tbl_Demo_col2] und [user_scans].

Ein Blick auf [sys].[dm_db_index_usage_stats] gibt aber noch weitere Informationen preis. Wenn man sich das obige Ergebnis anschaut, stellt man fest, dass der Index [ix_tbl_Demo_IUser] weder für [user_seeks], [user_scans] noch [user_lookups] verwendet wurde. Eigentlich wurde dieser Index NIE benutzt; jedoch kostet er Performance! Obwohl der Index nicht verwendet wird, muss er dennoch regelmäßig aktualisiert werden, wenn sich Daten in Attributen ändern, die durch den Index abgedeckt werden. Auch, wenn neue Datensätze in die Relation eingetragen werden, muss dieser Index mit aktualisiert werden. Das Löschen einen solchen Indexes kann man guten Gewissens empfehlen, wenn man mit den Entwicklern spricht oder aber verlässlich geklärt ist, dass:

• der Service des Microsoft SQL Servers nicht vor kurzem gebootet wurde
• der Index nicht erst nachträglich implementiert wurde

Fazit

Bei der Analyse von Performanceengpässen ist – wenn die Hardware und Systemkonfiguration ausgeschlossen werden kann – so gut wie immer eine falsche Indexstrategie die Ursache. Letztendlich basiert ein effizientes Datenbanksystem immer darauf, die angeforderten Daten so schnell wie möglich zu finden und an den Anforderer zu senden. Um diesen Prozess zu beschleunigen, ist die Reduktion von I/O die oberste Prämisse (siehe dazu der Artikel “Indexoptimierung = Reduktion von I/O”). Wenn auffällig hohe [user_lookup]-Werte in der Analyse ermittelt werden, sollte dies der erste “Angriffspunkt sein. Bisher war es in den von mir untersuchten Systemen immer so, dass zu den hohen Keylookup-Werte ein non clustered Index eine fast identische Zahl von [user_seeks]  aufwies – damit war sehr schnell der “Übeltäter” gefunden und es musste nur noch herausgefunden werden, welches Attribut aus dem Clustered Index im betroffenen Index fehlt. Um das herauszufinden, müssen die Ausführungspläne untersucht werden.

Des Weiteren ist [sys].[dm_db_index_usage_stats] ein sehr gutes Analysewerkzeug, um “nutzlose” Indexe zu finden und sie gegebenenfalls zu konsolidieren. Wie man Indexe konsolidiert, habe ich im Artikel “Konsolidierung von Indexen” detailliert beschrieben.

Herzlichen Dank fürs Lesen!