Donnerstag, 29. November 2012

Idealer Datentyp für Clustered Index – GUID vs. INT

Am heutigen Tag war ich bei einem Kunden und sollte mir ein System anschauen, bei dem es Streitigkeiten zwischen Entwicklern und Systemadministratoren gab. Bei der Auswahl von Attributen für einen Surrogatschlüssel als Primary Key sollte dargelegt werden, welcher Datentyp und welche Verwendung ideal für einen optimierten Zugriff ist.

Problemstellung

Die Entwickler im Unternehmen haben sich darüber beschwert, dass Abfragen auf die Objekte der Datenbank sehr langsam laufen. Dieses Phänomen trat besonders auf, nachdem durch nächtliche Uploads von Daten aus einem Drittsystem die Berichte für den nächsten Tag aufbereitet und ausgeführt wurden. Insgesamt hat man eine Verschlechterung der Performance nach einem Import von 10% – 30% bemerkt. Meine Aufgabe bestand darin, herauszufinden, warum die Performance so massiv eingebrochen war.

Die nachfolgende Beschreibung des Problems beschränkt sich aus Gründer der Vereinfachung auf eine Relation. Diese Relation soll die Probleme im Allgemeinen verdeutlichen, die sich im System ergeben haben!

Überprüfung der Abfrage für den Bericht

Zunächst habe ich mir den Bericht angeschaut und das zugrundeliegende DataSet. Hierbei wurde die Abfrage (stark vereinfacht!) wie folgt ausgeführt:

SELECT * FROM dbo.tbl_ReportData WHERE Col4 BETWEEN 100 AND 200;

Bei der Untersuchung des Abfrageplans für die Abfrage habe ich festgestellt, dass ein “Clustered Index Scan” durchgeführt wird. Das lies für mich darauf schließen, dass das Attribut [Id] entweder nicht indiziert war oder aber die Kardinalität (Selektivität) der Daten den Optimierer dazu verleitet hat, einen Index Scan einem Index Seek vorzuziehen.

Überprüfung der Datenstruktur der Relation

Also habe ich im nächsten Schritt die Datenstruktur begutachtet und habe den “Fehler” relativ schnell ausfindig machen können. Hinweis: Die Datenstruktur ist für den Artikel stark vereinfacht worden!

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tbl_ReportData', 'U'))
    DROP TABLE dbo.tbl_ReportData
    GO

CREATE TABLE dbo.tbl_ReportData
(
    Id      uniqueidentifier    NOT NULL    DEFAULT (newid()),
    Col1    char(119)           NOT NULL    DEFAULT ('just simple text for col1'),
    Col2    char(90)            NOT NULL    DEFAULT ('just simple text for col2'),
    Col3    char(200)           NOT NULL    DEFAULT ('just simple text for col3'),
    Col4    int                 NOT NULL    IDENTITY,

    CONSTRAINT pk_tbl_ReportData PRIMARY KEY CLUSTERED (Id)
);
GO

CREATE INDEX ix_tbl_ReportData_Col4 ON dbo.tbl_ReportData (Col4);

Ein erster Blick auf die Struktur schien zunächst einmal – oberflächlich – o.k, jedoch fielen sofort zwei Punkte ins Auge, die mich alarmierten:

  • Der Index ix_tbl_ReportData_Col4 berücksichtigt zwar das Attribut [Col4] jedoch werden die anderen Attribute nicht in den Index einbezogen. Somit wird entweder ein Clustered Index Scan oder aber ein Clustered Index Seek mit einem Key Lookup durchgeführt. Dieser Punkt war jedoch nicht wesentlich für meine Bedenken.
  • Vielmehr ist aufgefallen, dass für den Clustered Index ein höchst ungünstiger Datentyp “uniqueidentifier” verwendet wird.
  • Zu allem Unglück ist der DEFAULT dieses Attributs ein Wert, der eher zufällig durch die Funktion newid() generiert wird

Problemlösung

Von den obigen Auffälligkeiten möchte ich den falsch definierten Index zunächst außen vorlassen. Vielmehr sollte zunächst das markanteste Problem der Wahl des Attributs für den Clustered Index Key unter die Lupe genommen werden. Zu einigen Nachteilen dieser Wahl habe ich bereits im Artikel “Optimierung von Datenbankmodellen – Richtige Wahl von Datentypen und Indexen (Teil 1)” Beispiele genannt.

Neben der “verschwendeten” 16 Bytes pro Schlüsselwert kommt – wie dieses Beispiel deutlich zeigen wird – ein weiteres Problem hinzu, das leider viel zu sehr vernachlässigt wird – die Fragmentierung des Indexes.

Basierend auf der obigen Datenstruktur werden nun 100.000 Datensätze mit ihren Standardwerten in die Tabelle eingetragen. Der Hinweis sei erlaubt, dass zu Demonstrationszwecken ein Attribut [Col4] hinzugefügt wurde, das automatisch um jeweils 1 hochgezählt wird, wenn ein neuer Datensatz in die Relation eingetragen wird.

SET NOCOUNT ON;
GO

INSERT INTO dbo.tbl_ReportData DEFAULT VALUES;
GO 100000

Führt man das obige Script aus, werden 100.000 Datensätze in die zuvor erstellte Relation eingetragen. Ein Blick auf die Daten lässt schnell vermuten, was hier passiert.

UniqueIdentifier - SortOrder_1

Man kann sehr deutlich am Attribut [Col4] erkennen, dass der erste Datensatz erst an Position 86.925 eingetragen wurde. Das ist natürlich für einen Clustered Index ein absolutes K.O-Kriterium, da in diesem Fall der Index vollständig neu organisiert werden muss (Clustered Index = Relation!).

Schaut man auf die Statistiken dieser Abfrage, erschreckt der hohe IO-Wert für die Ausführung

(100000 row(s) affected)
tbl_ReportData-Tabelle. Scananzahl 1, logische Lesevorgänge 8143, . . .

Würde der Index sequentiell gefüllt werden, so hätten eigentlich nicht 8.143 Seiten benötigt werden müssen. Da ein Datensatz eine Gesamtgröße von 430 Bytes hat, wären ungefähr 5.500 Seiten nötig, um die Datenmenge zu speichern.

8.000 Bytes / 430 Bytes = 18 Datensätze / Seite
100.000 Datensätze / 18 Datensätze = 5.555 Datenseiten

Wie kommt also dieser hohe Wert für die Lesevorgänge zu Stande. Insgesamt sind fast 46% mehr Lesevorgänge erforderlich um die gesamte Relation zu lesen. Dieser Wert ist erschreckend hoch und ein Blick auf die physikalischen Werte des Indexes brachten das Ergebnis schnell zu Tage. Die nachfolgende Abfrage zeigt die physikalische Beschaffenheit des Clustered Index:

SELECT  db_name(database_id)    AS    Database_Name,
        OBJECT_NAME(object_id)  AS    Table_Name,
        Index_Id,
        Index_type_desc,
        Index_Level,
        avg_fragmentation_in_percent,
        avg_fragment_size_in_pages,
        page_count,
        avg_page_space_used_in_percent,
        record_count,
        avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_ReportData', 'U'), 1, NULL, 'DETAILED');

image

Wie der Abbildung zu entnehmen ist, hat der Index insbesondere im Leaf-Level einen sehr hohen Grad der Fragmentierung von nahezu 99%. Gleichwohl werden nur ca. 66% einer Datenseite effektiv benutzt. Der Grund für den hohen Fragmentierungsgrad sowie der ungünstigen Verwendung der Seiten liegt in der Struktur der Relation und dessen Schlüsselattribut für de Clustered Index.

Grundsätzlich ist ein Clustered Index die Relation in einer nach dem Schlüsselattribut geordneten Form. In dem obigen Beispiel wird die Relation nach dem Schlüsselattribut [Id] sortiert. Problematisch ist hierbei jedoch, dass dieses Schlüsselattribut zufällig durch die Funktion newid() generiert wird. Das Ergebnis dieser Funktion ist immer zufällig und ungeordnet. Wenn nun ein neuer Datensatz in die Relation eingetragen wird, muss für diesen Datensatz im Leaf-Level Platz vorhanden sein. Ist dieser Platz nicht vorhanden, wird die Datenseite geteilt (Page Split) und ca. 50% der Daten auf die neue Seite verschoben, um anschließend den Datensatz auf eine der beiden Seiten einzutragen. Das Attribut [Col4] habe ich zu Demonstrationszwecken der Relation hinzugefügt, um zu zeigen, bei welcher Einfügen-Operation der Datensatz in die Relation eingetragen wurde. In der obigen Darstellung bedeutet dies, dass der erste Datensatz im Clustered Index erst mit der 86.925 Operation in die Relation eingetragen worden ist. Wie viel Prozent einer Seite tatsächlich verwendet wird, zeigt der Wert für das Attribut [avg_page_space_used_in_percent] der Systemfunktion sys.dm_db_index_physical_stats.

Mit dem Kunden wurden drei Möglichkeiten besprochen, um diesen hohen Grad der Fragmentierung zu verhindern. Eine Defragmentierung als Auftrag, der jede Nacht ausgeführt wird, wurde als erste Option schnell verworfen, da ein REBUILD eines Indexes zwangsläufig den Plancache für die betroffene Relation löscht, da sowohl Index als auch Statistiken gegebenenfalls nicht mehr für den Plan gültig sind.

Lösung 1: Verwendung einer sequentiellen GUID

Als zweite Option wurde vorgeschlagen, den DEFAULT für die Relation nicht durch eine zufällige GUID befüllen zu lassen, sondern durch eine sequentielle GUID. Hierzu gibt es seit SQL Server 2005 die Funktion newsequentialid(). Diese Funktion stellt sicher, dass eine GUID nicht mehr willkürlich erstellt wird sondern sequentiell (ähnlich einer fortlaufenden Nummer). Das nachfolgende Beispiel belegt, dass diese Option eine deutliche Verbesserung gegenüber dem aktuellen Modell bringt.

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tbl_ReportData_SeqGUID', 'U'))
    DROP TABLE dbo.tbl_ReportData_SeqGUID
    GO

CREATE TABLE dbo.tbl_ReportData_SeqGUID
(
    Id      uniqueidentifier    NOT NULL    DEFAULT (newsequentialid()),
    Col1    char(119)           NOT NULL    DEFAULT ('just simple text for col1'),
    Col2    char(90)           NOT NULL    DEFAULT ('just simple text for col2'),
    Col3    char(200)          NOT NULL    DEFAULT ('just simple text for col3'),
    Col4    int                 NOT NULL    IDENTITY,

    CONSTRAINT pk_tbl_ReportData_SeqGUID PRIMARY KEY CLUSTERED (Id)
);
GO

CREATE INDEX ix_tbl_ReportData_SeqGUID_Col4 ON dbo.tbl_ReportData_SeqGUID (Col4);

SET NOCOUNT ON;
GO

INSERT INTO dbo.tbl_ReportData_SeqGUID DEFAULT VALUES;
GO
100000

Mit diesem Script wird eine neue Relation mit der identischen Struktur wie die Originalrelation erstellt. Der einzige – aber wesentliche – Unterschied zur Originalrelation besteht darin, wie der DEFAULT für das Attribut [Id] definiert wird. Statt einer zufälligen GUID wird nun eine fortlaufende GUID generiert und eingetragen. Anschließend werden erneut 100.000 Datensätze eingetragen. Bereits das Ergebnis der Abfrage zeigt einen markanten Unterschied zum Original

UniqueIdentifier - SortOrder_2

Wie in der Abbildung zu sehen ist, werden die Datensätze nun tatsächlich fortlaufend eingetragen. Das Attribut [Col4] besitzt eine fortlaufende Numerierung. Auch die Indexstatistiken für diese Relation (Clustered Index) sehen deutlich besser aus. Dies zeigt sich unter anderem im IO-Verhalten der obigen Abfrage.

(100000 row(s) affected)
tbl_ReportData-Tabelle. Scananzahl 1, logische Lesevorgänge 5595, . . .

Durch die fortlaufende Sortierung muss der Index nicht permanent umorganisiert werden und Pagesplits werden verhindert. Der IO ist durch diese Aktion um ca. 30% reduziert worden. Eine Fragmentierung des Indexes konnte verhindert werden.

UniqueIdentifier - Fragmentation_2

Leider hat die Verwendung von newsequentialid() jedoch einen kleinen – aber entscheidenden – Nachteil; sobald der Server neu gestartet wird, generiert die Funktion zwar weiterhin fortlaufende GUIDS jedoch können die im Wertebereich über, zwischen oder – wenn es gut läuft – unter dem bisherigen Wertebereich liegen. Schlimmstenfalls ist der neue Bereich so unglücklich gewählt, dass er bei weiteren Eintragungen erneut zu Pagesplits führt, da die neuen Werte zwischen den bisherigen Werten liegen.

Lösung 2: fortlaufende Numerierung durch einen Zahlenwert

Wir haben die Vor- und Nachteile dieser Variante mit dem Kunden abgesprochen und kamen zu dem Entschluss, grundsätzlich auf die Verwendung von GUID zu verzichten. Statt eines 16 Byte großen Schlüsselattributs würde bei der zu erwartenden Anzahl von Datensätzen auch ein Datentyp INT (4 Byte) ausreichend sein.

Die oben genannte Struktur wurde dahingehend geändert, dass für das Attribut [Id] der Datentyp “uniqueidentifier” durch den Datentyp INT sowie den Standardwert IDENTITY ersetzt wird. Anschließend wurden erneut 100.000 Datensätze in die neue Tabelle eingetragen.

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tbl_ReportData_Int', 'U'))
    DROP TABLE dbo.tbl_ReportData_Int
    GO

CREATE TABLE dbo.tbl_ReportData_Int
(
    Id      int            NOT NULL    IDENTITY,
    Col1    char(119) …

Die Ausführung dieser Abfrage zeigte die folgende IO-Statistik:

(100000 row(s) affected)
tbl_ReportData-Tabelle. Scananzahl 1, logische Lesevorgänge 5285, . . .

Der IO ist zwar nicht signifikant reduziert worden, jedoch ist der Index nun deutlich besser organisiert und neue Datensätze werden fortlaufend in die Relation eingetragen, da das Attribut [Id] durch IDENTITY den jeweils nächsten höheren Wert verwendet. Damit werden die Datensätze für den Clustered Index immer am Ende eingetragen und Pagesplits werden verhindert.

UniqueIdentifier - Fragmentation_3

Bei der Überprüfung der physikalischen Indexbedingungen fallen zwei Dinge sofort ins Auge:

  • Der Grad der Fragmentierung ist noch einmal deutlich um 50% reduziert worden
  • Die Leaf-Seiten sind nahezu vollständig gefüllt und werden optimal genutzt

Fazit

Insgesamt bleibt mir als Empfehlung für Strukturen von Relationen der Hinweis, nach Möglichkeit auf GUID zu verzichten. GUID mögen ihre Berechtigung in manchen Situationen haben – sie sind aber für einen Clustered Index vollkommen ungeeignet und führen insbesondere beim Einfügen / Löschen von Datensätzen zu einem sehr hohen Grad der Fragmentierung. Meistens müssen Indexreorganisationen in sehr kurzen Intervallen durchgeführt werden, um die Fragmentierung in den Griff zu bekommen.

Ein weiterer – nicht zu unterschätzender – Nachtteil von GUID ist die Größe des Datentypen. Insgesamt ist eine GUID als Schlüsselattribut 8 Bytes größer als ein wesentlich performanterer Schlüssel vom Datentypen INT.

Wird dieses Schlüsselattribut auch als Foreign Key Einschränkung in abhängigen Relationen verwendet, wird unnötig Speicherplatz für Indexe verwendet.

Bitte auch immer daran denken, dass das Schlüsselattribut eines Clustered Index IMMER Bestandteil eines jeden weiteren angelegten Indexes ist. Storage mag heute zwar preiswert sein – aber dieser Punkt sollte nicht die treibende Kraft bei der Auswahl des geeigneten Datentypen sein – vielmehr sollte berücksichtigt werden, wie viele Datensätze auf eine Datenseite passen. Je weniger Datensätze auf einer Datenseite gespeichert werden können, um so mehr Datenseiten müssen für die Speicherung verwendet werden – und das erzeugt teuren IO!

Herzlichen Dank für’s Lesen

newid() http://msdn.microsoft.com/de-de/library/ms190348.aspx
newsequentialid() http://msdn.microsoft.com/de-de/library/ms189786.aspx
dm_db_physical_index_stats http://msdn.microsoft.com/de-de/library/ms188917.aspx

1 Kommentar :

  1. ... die Größe des Datentypen. Insgesamt ist eine GUID als Schlüsselattribut 8 Bytes größer als...

    Hier ist mir ein Fehler unterlaufen - es sind natürlich 12 Bytes.
    GUID = 16 Bytes
    INT = 4 Bytes

    DIFF = 12 Bytes

    AntwortenLöschen