In einem Projekt, zu dem ich erst vor kurzem hinzu gezogen wurde, ging es um die Verwendung von Indexen sowie die richtige Indexstrategie in einem Herstellerprodukt, das regelmäßig gegen Mittag manche Abfragen in der Ausführungszeit verdoppelte. Meine Aufgabe bestand darin, die Ursache zu finden und geeignete Strategien für den Kunden zu erarbeiten. Bei dieser Aufgabe wurden eine Vielzahl von Problemen offenkundig, von denen insbesondere der Sachverhalt dieses Artikels einen direkten Zusammenhang zwischen Statistiken und der Verwendung von Indexen zeigt.

Ein ganz wichtiger Hinweis gleich zu Beginn: Zu Statistiken in Microsoft SQL Server gibt es tausende von sehr guten Artikeln im WWW. Statistiken selbst werden in dem nachfolgenden Artikel NICHT näher beschrieben! Sie sind aber für das Verständnis von Microsoft SQL Server und der Ermittlung von Ausführungsplänen DAS Werkzeug für gute Ausführungspläne. Sofern es notwendig wird, gehe ich auf einzelne Ergebnisse gezielt ein; für eine Vertiefung in das Thema Statistiken möchte ich das wirklich SEHR GUTE Buch “SQL Server Statistics” von Holger Schmeling empfehlen! In diesem Zusammenhang möchte ich auch die Aufzeichnung "Demystifying Database Statistisc” von Erin Stellato empfehlen, den sie auf dem PASS Summit 2012 gehalten hat!

Problemstellung

Eine Relation mit mehreren Attributen wird jeden Morgen mit Daten gefüllt, die während des Tages von Mitarbeitern aktualisiert werden müssen. Nachdem die Daten in die Relation übertragen wurden, wurden die Indexe der Relation neu aufgebaut. Über den Tag werden Daten eines Attributs kontinuierlich geändert. Die Relation hat ein Volumen von ca. 100.000 Datensätzen. In der Besprechung habe ich darauf hingewiesen, dass ein Index in der Relation nicht ideal implementiert ist, da er nur das abzufragende Attribut abdeckt und weitere Daten mittels teurer KeyLookups aus dem Clustered Index gelesen werden müssen. Die Argumentation des Herstellers lief darauf hinaus, dass ein schlechter Index immer noch besser sei als kein Index. Diese Aussage ist nicht nur falsch; sie kann ein System jegliche Performance kosten, wenn verschiedene Konstellationen zusammentreffen. Für die Demonstration des Sachverhalts reicht die folgende – anonymisierte – Struktur:

CREATE TABLE dbo.demo
(
    Id    int       IDENTITY (1, 1),
    col1  char(10),
    col2  char(10),
    col3  char(10),
    col4  char(10),
    col5  date,
    col6  money,
    col7  float,
    col8  char(200),
    col9  varchar(200),

    CONSTRAINT pk_demo_Id PRIMARY KEY CLUSTERED (Id)
);

-- Eintragen von 100000 Datensätzen
SET NOCOUNT ON
GO

DECLARE @i int = 1
WHILE @i <= 100000
BEGIN
    INSERT INTO dbo.demo
    (col1, col2, col3, col4, col5, col6, col7, col8, col9)
    SELECT 'col1: ' + CAST(@i % 10 AS varchar(10)),
           'col2: ' + CAST(@i % 20 AS varchar(10)),
           'col3: ' + CAST(@i % 30 AS varchar(10)),
           'col4: ' + CAST(@i % 40 AS varchar(10)),
           DATEADD(dd, RAND() * 7 + 1, getdate()),
           RAND() * 10000 + 1000,
           RAND() * 100000 + 1000,
           'col8: ' + CAST(@i % 80 AS varchar(10)),
           'col9: ' + CAST(@i % 90 AS varchar(10))

    SET @i += 1
END
GO

Zunächst wird die Relation dbo.demo mit einem Clustered Index auf [Id] erstellt. Anschließend werden 100.000 Datensätze eingetragen. Abschließend wird der problematische und hier zu diskutierende Index auf dem Datumsattribut erstellt:

CREATE INDEX ix_demo_col5 ON dbo.demo (col5);

Um die Problematik des nun folgenden Sachverhalts etwas besser zu verstehen, gilt die besondere Beachtung dem Attribut  [col5]. Hierbei handelt es sich um ein Datumsattribut; in diesem Attribut sind maximal 7 verschiedene Datumswerte gespeichert. Die Werte in diesem Attribut werden aber im Laufe des Tages auf das aktuelle Tagesdatum gesetzt, wenn die Bearbeitung des Datensatzes abgeschlossen wurde. Bevor es in die Demonstration geht, ganz wenig Theorie, die für das Verständnis der gleich folgenden Ergebnisse notwendig sind.

Statistiken in Microsoft SQL Server

Bei Statistiken handelt es sich - einfach ausgedrückt - um die Häufigkeitsverteilung (Histogramm) der Schlüsselattribute des betroffenen Index. Sobald ein Index erstellt oder mit der Option REBUILD neu aufgebaut wird, wird regelmäßig die dazugehörige Statistik erstellt oder neu berechnet. Für die Aktualisierung einer Statistik gilt folgende Berechnung: 20% der Datenmenge + 500 Datensätze müssen geändert werden, um die Statistiken neu berechnen zu lassen. Für das obige Beispiel bedeutet dies, dass 20.000 Datensätze + 500 Datensätze = 20.500 Datensätze geändert werden müssen, damit die Statistik neu erstellt wird.

Schauen wir uns zunächst ein mal die Statistiken (hier jedoch nur das Histogramm) für das Attribut [col5] an. Man kann sehr gut die gleichmäßige Verteilung der Werte erkennen:

DBCC SHOW_STATISTICS ('dbo.demo', 'ix_demo_col5') WITH HISTOGRAM;

Workflow

In der Anwendung wird regelmäßig eine Abfrage auf alle Datensätze des aktuellen Datums ausgeführt. Diese Informationen werden in einem Listenfeld angezeigt und zeigen in einem Dashboard die Datensätze, die bereits von den Mitarbeitern erledigt wurden. Diese Abfrage sieht wie folgt aus:

SELECT Col1, col2, col3, col4, col5, col6, col7, col8, col9
FROM   dbo.demo
WHERE  col5 = CONVERT(char(8), getdate(), 112);

Zunächst wird ermittelt, wie viel I/O ein Clustered Index Scan auf alle Daten erzeugt:

SET STATISTICS IO ON;
GO

SELECT * FROM dbo.demo;

Das Ergebnis zeigt einen I/O von ca. 28 MB für 100.000 Datensätze:

(100000 Zeile(n) betroffen)
demo-Tabelle. Scananzahl 1, logische Lesevorgänge 3586, physische Lesevorgänge 0,

Am Morgen wird die Abfrage nach den aktuell erledigten Datensätzen zum ersten Mal ausgeführt. Damit ist das “Urteil” für die katastrophalen Ergebnisse bereits gesprochen, da der Ausführungsplan gecached wird!

Besondere Beachtung gilt der geschätzten Anzahl von Zeilen; sie ist 1. Dieser Wert wurde von Microsoft SQL Server “vermutet”, da es zum aktuellen Tagesdatum in der Statistik keine Daten gibt. Grundsätzlich geht Microsoft SQL Server IMMER von einem Wert 1 aus, wenn keine verlässlichen Daten im Histogramm gefunden werden können. Da keine Datensätze für den heutigen Tag  gefunden werden können, ist der der I/O vernachlässigbar. Nun läuft das Tagesgeschäft an und die Mitarbeiter bearbeiten die Datensätze.

510 Datensätze wurden geändert

Die folgende Abfrage schreibt für insgesamt 510 Datensätze das aktuelle Tagesdatum in das Attribut [col5] und die Abfrage nach erledigten Datensätzen wird erneut ausgeführt.

UPDATE dbo.demo
SET    col5 = getdate()
WHERE  Id % 10000 <= 50 AND
       col5 <> CONVERT(char(8), getdate(), 112);

Schaut man sich den Ausführungsplan zu den Daten des aktuellen Tages an, wird man feststellen, dass sich NICHTS an der Ausführungsstrategie geändert hat.

Obwohl nun 510 Datensätze aktualisiert wurden, kann Microsoft SQL Server die aktuellen Datensätze nicht für die Ermittlung eines idealen Ausführungsplans verwenden. Microsoft SQL Server geht immer noch von einer Datenmenge von 1 Datensatz aus. Entsprechend “vernichtend” fällt die Abfragestrategie aus und es müssen für 510 Datensätze insgesamt 12 MB Daten gelesen werden, da auch weiterhin von der “optimalen” Ausführungsstrategie in Verbindung mit einem KeyLookup ausgegangen wird:

(510 Zeile(n) betroffen)
demo-Tabelle. Scananzahl 1, logische Lesevorgänge 1533, …

Der Tag schreitet voran und gegen 11:00 Uhr sind bereits weitere 500 Datensätze bearbeitet worden. Das wird durch die folgende Abfrage simuliert:

UPDATE dbo.demo
SET    col5 = getdate()
WHERE  Id % 10000 <= 100 AND
       col5 <> CONVERT(char(8), getdate(), 112);

Der Ausführungsplan hat sich nicht geändert, da erst 1,01 % aller Datensätze geändert wurden. Entsprechend sieht das I/O aus:

(1010 Zeile(n) betroffen)
demo-Tabelle. Scananzahl 1, logische Lesevorgänge 3035, …

Der I/O für gerade mal 1.000 Datensätze reicht – bedingt durch die schlechte Abfragestrategie – fast schon an den I/O für einen Clustered Index Scan über alle Daten heran; aber der Tag ist ja noch nicht zu Ende. Bis 12:00 wurden weitere 500 Datensätze geändert…

UPDATE dbo.demo
SET    col5 = getdate()
WHERE  Id % 10000 <= 150 AND
       col5 <> CONVERT(char(8), getdate(), 112);

Wird nun die Abfrage nach den aktualisierten Datensätzen ausgeführt, kippt die Abfragestrategie und die Abfrage wird unverhältnismäßig teuer:

(1510 Zeile(n) betroffen)
demo-Tabelle. Scananzahl 1, logische Lesevorgänge 4537, …

Müssen für einen Clustered Index Scan über ALLE Datensätze 28.00 MB gelesen werden, sind es – trotz deutlich weniger Datensätze – für die teuren KeyLookups bei 1.500 Datensätzen bereits 35,50 MB!

Analyse

Microsoft SQL Server kann keinen besseren Ausführungsplan erstellen, da keine verlässlichen Statistiken vorhanden sind, die eine neue Ausführungsstrategie rechtfertigen. Microsoft SQL Server geht bei der Berechnung des Ausführungsplans IMMER von einem Datensatz aus, der die Ergebnismenge widerspiegelt. Ein Blick auf das Histogramm zeigt, dass trotz 1.500 geänderten Datensätzen die Statistiken nicht geändert wurden. Gemäß der oben bereits beschriebenen Formel für die Berechnung der “Schwelle” müssen erst 20.500 Datensätze geändert werden, bevor die Statistiken aktualisiert werden. Microsoft SQL Server weiß also noch nichts über die aktuelle Verteilung der Daten:

DBCC SHOW_STATISTICS ('dbo.demo', 'ix_demo_col5') WITH HISTOGRAM;

Werden die Statistiken aktualisiert, kann Microsoft SQL Server basierend auf den neuen Verteilungsdaten einen neuen Ausführungsplan erstellen. WICHTIG: Sobald Statistiken neu erstellt werden, werden die vormals verwendeten Ausführungspläne aus dem Ausführungscache gelöscht, da sie “wertlos” sind.

-- Aktualisierung der Statistiken für Index ix_demo_col5
UPDATE STATISTICS dbo.demo ix_demo_col5 WITH FULLSCAN;

-- Anzeige der geänderten Statistiken
DBCC SHOW_STATISTICS ('dbo.demo', 'ix_demo_col5') WITH HISTOGRAM;

Wie man erkennen kann, sind nun die Daten für den aktuellen Tag ebenfalls in den Statistiken vorhanden und die Ausführung der Abfrage nach den Daten des aktuellen Tages wird von einem teuren Keylookup zu einem Clustered Index Scan.

Nun, da die benötigten Statistikdaten vorhanden sind, erkennt Microsoft SQL Server, dass bei 1.510 Datensätzen der I/O durch die Verwendung von Keylookups den I/O bei der Verwendung eines Clustered Index übersteigt und ändert die Strategie. Zielführend sind in diesem Fall die ausreichenden und aktualisierten Statistiken für die betroffene Relation. Wenn nun noch der betroffene Index ix_demo_col5 so angepasst wird, dass alle benötigten Informationen vollständig durch den Index selbst abgedeckt werden, muss auch kein teurer Clustered Index Scan mehr durchgeführt werden und das I/O kann deutlich nach unten korrigiert werden:

-- Löschen des schlechten Index
DROP INDEX ix_demo_col5 ON dbo.demo;

-- Erstellen des neuen (korrekten) Index
CREATE INDEX ix_demo_col5 ON dbo.demo (col5)
INCLUDE (col1, col2, col3, col4, col6, col7, col8, col9);

Auf die I/O-Statistiken können sich nun sehen lassen. Aus 4.537 gelesenen Datenseiten werden so nun  69 Datenseiten!

(1510 Zeile(n) betroffen)
demo-Tabelle. Scananzahl 1, logische Lesevorgänge 69, …

Fazit

Das obige Beispiel zeigt einmal mehr, dass für optimale Datenbankperformance nicht ausreichend ist, nur die “Grundzüge” von Indexierung zu verstehen. Als ich die Aussage des Mitarbeiters der Herstellerfirma gehört habe, bin ich innerlich “explodiert”. Da wird dem Kunden (er war während der Telefonkonferenz ebenfalls im Gespräch) gegenüber mit solchen Aussagen suggeriert, dass die gewählte Indexstrategie professionell und optimal für das Produkt gewählt wurde und statt dessen ist es dieser “Strategie” zu verdanken, dass man ein System eher noch unperformanter “tuned” als es ohne diese Strategie wäre.

Die Datenvolumen haben sich in den letzten Jahren rapide verändert und wachsen unaufhörlich; um so wichtiger ist gerade dann das grundlegende Verständnis für die Optimierung von Datenbanken. Würde sich der Entwickler nur ein wenig mehr mit den Zusammenhängen von Indexen, physikalischen Ausführungsoperationen und Statistiken auseinander setzen, würde er sich solche Aussagen wie die obige “verkneifen”. Mit jedem Projekt, zu dem ich hinzu gezogen werde, wird mir immer mehr klar, dass in den Entwicklungshäusern ein “Umdenken” stattfinden muss. Es gibt sie nicht, die “universellen” Programmierer, die in JEDEM Bereich der Anwendungsentwicklung perfekt sind; vielmehr muss ein wirklich professioneller Entwickler ausschließlich für den Bereich “Datenbanken” abgestellt werden. Nur dann ist es meines Erachtens möglich, gute und schnell funktionierenden Datenbankanwendungen zu programmieren.

Herzlichen Dank fürs Lesen!