Mit dem nachfolgenden Artikel 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. Zur 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(N'dbo.tbl_Companies' N'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,
    CostCentre  NVARCHAR(MAX)    NOT NULL,
    UpdateBy    NVARCHAR(MAX)    NOT NULL,

    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id)
);
GO

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

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'Kostenstelle';
GO

SELECT Name, TaxNo, CostCenter FROM dbo.tbl_Companies WHERE Name = 'Kundenname';
GO

Wahl der optimalen Datentypen

Der 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. Microsoft SQL Server erlaubt es nicht, 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).

Aus diesen Gründen sind die ausgewählten Datentypen unbrauchbar für einen indexierten Zugriff. Die 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 Ergebnisse 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)
);
GO

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 wurde der Datentypen nvarchar durch varchar ersetzt und die Zeichenlänge auf eine feste Größe von 7 Zeichen begrenzt. Das Attribut [Kostenstellen] verwendet keine Sonderzeichen. Somit konnte der Datentyp 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!

Datentyp des gruppierten Index

Ein weiterer Optimierungsbedarf war die Wahl des Datentypen für den gruppierten Index. Da der gruppierte Index als uniqueidentifier definiert wurde, konsumiert der 16 Bytes. Ebenfalls problematisch im vorliegenden Fall ist, dass der Wert zufällig ermittelt wird und somit der Index schnell fragmentiert wird.

Hier muss eine Abwägung durch die Entwickler gemacht werden. Wird das System primär zum Speichern neuer Daten benötigt durch mehrere Clients benötigt werden, kann der gruppierte Index sogar positiv wirken, da keine “Hotspots” am Ende des Index gebildet werden. Wird jedoch ein weniger konkurrierendes System programmiert, sollte man den Datentypen zu Gunsten von Platz und Wartbarkeit ändern.

Nachdem wir mit dem Kunden und dem Hersteller gemeinsam das maximale Datenvolumen besprochen haben (>= 3.000.000 Datensätze), 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)
);
GO

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 gruppierten 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 bei eine Volumen von 450.000 Datensätzen:

Ursprüngliche Datenstruktur

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

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

Zusammenfassung

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.

Thema	Link
NEWID()	http://msdn.microsoft.com/de-de/library/ms190348.aspx
NEWSEQUENTIALID()	http://msdn.microsoft.com/de-de/library/ms189786.aspx
SQL Server Datentypen	http://msdn.microsoft.com/de-de/library/ms187752.aspx
SQL Server Storage Engine	SQL Server Storage Engine

Herzlichen Dank fürs Lesen!