Mit dem nachfolgenden Thema möchte ich aus dem "Daily Business" berichten, in dem ich gebeten wurde, die Performance von Abfragen zu optimieren. Als Beispiel soll die folgende Datenstruktur (inklusive Indexes) verwendet werden. Dieses Beispiel werde ich für diesen wie auch die folgenden Themenkomplexe verwenden, um zu zeigen, wie man professionell an die Analyse und die Optimierung von Relationen und Indexes geht.

Für die Demonstration der Optimierung habe ich die Struktur einer Relation aus einer Kundenanwendung etwas vereinfacht - jedoch sind die Datentypen 1 zu 1 aus dem Datenmodell des Herstellers übernommen!

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

CREATE TABLE dbo.tbl_Companies
(
     Id          uniqueidentifier NOT NULL,
     Name       nvarchar(max) NOT NULL,
     TaxNo      nvarchar(max) NOT NULL,
     CostCenter nvarchar(max) NOT NULL,
     UpdateBy   nvarchar(max) NOT NULL,

     CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id)
);

Auf die obige Relation werden Abfragen der folgenden Art ausgeführt:

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'Kostenstelle'
SELECT Name, TaxNo, CostCenter FROM dbo.tbl_Companies WHERE Name = 'Kundenname'
usw.

Optimierungsphase 1: Datentypen in der Relation

Wahl der optimalen Datentypen

Zunächst fällt auf, dass die Relation einen Clustered Index (was ja zunächst mal nicht schlecht ist) besitzt. Dieser Clustered Index wird durch das Attribut [Id] definiert. Da aktuell nur der Clustered Index festgelegt ist, mag man meinen, dass man jederzeit weitere Indexes erzeugen kann; das ist aber - bei obiger Struktur - ein böser Trugschluss. Leider ist es bei SQL Server nicht möglich, einen "MAX-Datentypen" zu indizieren. Dies bedeutet in der Konsequenz, dass Abfragen auf [Name], [TaxNo], [CostCenter] und [UpdateBy] nicht in der Lage sind, als Schlüsselattribut in einem Index verwendet zu können. (siehe dazu: http://msdn.microsoft.com/de-de/library/ms188783.aspx). MAX-Datentypen können jedoch Bestandteil eines Indexes sein!

Aus den obigen Gründen sind die ausgewählten Datentypen nicht ideal für einen indexierten Zugriff. Die ideale Auswahl des geeigneten Datentypen richtet sich nach den Anforderungen. Aus der obigen Datenstruktur erschließt sich mir nicht der Grund für die Verwendung des gewählten Datentypen. Wir haben also die maximale Datenlänge ermittelt und dabei folgende Erkenntnisse gewonnen:

Das Attribut [Name] beinhaltete Einträge, deren Länge zwischen 10 und 80 Zeichen variierte

• Das Attribut [CostCenter] hatte eine feste Länge von 7 Zeichen
• Das Attribut [TaxNo] variierte in der Länge zwischen 10 und 24 Zeichen

Auf Basis dieser Erkenntnis haben wir im ersten Schritt die Attribute der Relation [dbo].[tbl_Company] wie folgt angepasst:

CREATE TABLE dbo.tbl_Companies
( 
    Id uniqueidentifier NOT NULL,
    Name nvarchar(128) NOT NULL,
    TaxNo varchar(24) NOT NULL,
    CostCenter char(7) NOT NULL,
    UpdateBy varchar(20) NOT NULL,

    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id)
);

Für das Attribut [Name] haben wir eine Begrenzung auf 128 Zeichen festgelegt um Spielraum für längere Unternehmensnamen zu gewährleisten. Den Datentypen nvarchar haben wir für die Attribute weitestgehend belassen, da auch Sonderzeichen berücksichtigt werden müssen. Die Steuernummern [TaxNo] kann nur Buchstaben und numerische Werte beinhalten. Aus diesem Grund haben wir den Datentypen nvarchar durch varchar ersetzt und die Zeichenlänge ebenfalls begrenzt. Das Attribut [CostCenter] besitzt eine feste Größe von 7 Zeichen. Weiterhin werden für Kostenstellen keine Sonderzeichen verwendet. Somit konnte auch hier der Datentype von nvarchar zu char geändert werden. Die Entscheidungsgrundlage für [UpdateBy] orientierte sich eng an den Überlegungen für [Name].

Durch diese - zunächst marginalen - Änderungen wurde die Länge eines Datensatzes limitiert und die Attribute können später für Indexe verwendet werden! Ein weiterer Optimierungsbedarf lies sich bei der Wahl des Datentypen für den Primärschlüssel finden. Der Hersteller konnte nicht schlüssig argumentieren, warum er diesen Datentypen verwendet. Ein GUID hat eine Größe von 16 Bytes. Das hört sich zunächst einmal nicht sehr groß an aber man sollte bei der Auswahl von Datentypen beachten, dass ihre Datengröße auch in JEDEM Index mit gespeichert werden muss, in dem das Attribut verwendet wird.

Ein einfaches Rechenbeispiel soll das verdeutlichen:

Eine Relation mit 10.000.000 Datensätzen besitzt einen UNIQUE INDEX (ux_Relation) auf einem Attribut vom Datentypen GUID.
Ein weiterer Index verwendet ebenfalls dieses Attribut in Verbindung mit weiteren Attributen (ix_Relation)

Der Index (ux_Relation) hat dann bei dieser Datenmenge ein Datenvolumen von ~150 MB (!!!)
Der zweite Index (ix_Relation) hat ein Datenvolumen von ~150 MB + (10.000.000 * Datengröße anderer indizierter Attribute)

Je größer ein Index ist, um so mehr Reads (logical und physical) werden erzeugt, da ja weniger Informationen auf einer Page gespeichert werden können. Handelt es sich - wie im obigen Beispiel - auch noch um den Clustered Index, werden diese 152 MB zu JEDEM Index automatisch hinzugefügt, da der Key des Clustered Index Bestandteil des angelegten Index ist! Man sollte sich im Vorfeld gut überlegen, welche Datentypen verwendet werden sollen/müssen.

Einen GUID als Clustered Key zu verwenden hat einen weiteren Nachteil, wenn man berücksichtigt, dass der Clustered Index immer die Relation selbst repräsentiert; und zwar physikalisch nach dem Schlüssel sortiert (!!!). Zunächst einmal mag man meinen, dass dieser Umstand doch nicht so schlecht ist. Damit kann man ja sehr schnell einen Wert finden! So gesehen hat die Sortierung auf jeden Fall einen Vorteil. Aber wo Sonne ist, da ist auch Schatten. Die Sortierung bedeutet nämlich im Umkehrschluss, dass neue Datensätze auch entsprechend einsortiert werden müssen.

Berücksichtigt man diesen Umstand, wird schnell klar, wo Engpässe auftreten. Handelt es sich um ein OLTP-System, ist dieser Datentyp denkbar ungeeignet. In DWH-Systemen "könnte" man sie noch dulden. Eine GUID wird eher zufällig generiert und das bedeutet z. B. bei dem obigen Beispiel mit 10.000.000 Datensätzen einen erheblichen Aufwand, wenn plötzlich an der 1.000 Position ein neuer Datensatz eingetragen werden muss. Intern müssen alle nachfolgenden Datensätze komplett neu organisiert werden - das kann dauern.

Hinweis: Man hat die Möglichkeit, in SQL-Server für den DEFAULT-Value neben NEWID() (generiert eine zufällige GUID auch die Funktion NEWSEQUENTIALID() zu verwenden; nur sollte man nicht unbedingt darauf bauen, dass auch wirklich eine neue "höhere" GUID generiert wird.

Nachdem wir mit dem Kunden und dem Hersteller gemeinsam das maximale Datenvolumen besprochen haben (>= 3.000.000), haben wir gemeinsam beschlossen, den Datentypen GUID in den Datentypen INT zu wechseln. Die Definition der Relation wurde wie folgt angepasst:

CREATE TABLE dbo.tbl_Companies
(
    Id int NOT NULL IDENTITY,
    Name nvarchar(128) NOT NULL, 
    TaxNo varchar(24) NOT NULL, 
    CostCenter char(7) NOT NULL, 
    UpdateBy varchar(20) NOT NULL,

    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id)
);

Da das Attribut [Id] keine beschreibende Funktion hat (Surrogatschlüssel), wurde neben einem neuen Datentypen auch die Einschränkung IDENTITY hinzugefügt. Dadurch werden neue Datensätze automatisch am Ende der Relation eingefügt. Der Aufwand für den Clustered Index ist minimal, da neue Datensätze IMMER am Ende eingefügt werden, da ja die nächsthöhere Nummer verwendet wird.

Durch die obige Anpassung (und dabei wurden noch keine Index optimiert!) wurde der I/O signifikant reduziert. Die Messwerte vorher / nachher zeigen die Unterschiede auf (450.000 Datensätze!)

Ursprüngliche Datenstruktur

SELECT * FROM dbo.tbl_Customer

(450000 row(s) affected)
tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 11913, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Neue Datenstruktur

SELECT * FROM dbo.tbl_Customer

(450000 row(s) affected)
tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 7529, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Fazit

Die Auswahl geeigneter Datentypen ist ein erster - wichtiger - Schritt zu einer optimierten Anwendung. Durch die richtige Auswahl von Datentyp und Datengröße kann ein erster Erfolg erzielt werden, ohne dabei auch nur einen Index zu berücksichtigen. Zu den behandelten Schwerpunkten finden Sie nachfolgend weiterführende Links zu Webseiten, um das Thema zu vertiefen.

Herzlichen Dank für's Lesen.
PS: Die Demo-Datenbank werde ich nach Abschluss dieser "Serie" zum Download bereitstellen!