Frank Kalis

Einer der effizientesten Wege, um die JOIN Performance zu erhöhen ist die Anzahl der Zeilen die geJOINt werden, zu beschränken. Dies gilt insbesondere für die äußere(n) Tabelle(n) eines JOINs. Es sollten nur die Zeilen zurückgegeben werden, die auch für den JOIN verwendet werden.

Ganze Geschichte »

Ein Zusammengesetzter Index ist ein Index, der aus mehr als einer Spalte besteht. In manchen Fällen ist ein Zusammengesetzter Index auch ein Covering Index. Quick Tips zu Covering Indizes finden sich hier. Verallgemeinert gesprochen sollten Zusammengesetzte Indexes (mit Ausnahme von Covering Indexes) vermieden werden, da solche Indexes typischerweise sehr breit sind und damit mehr I/O Operationen verursachen, was zu Lasten der Performance geht.

Ganze Geschichte »

JOIN Hints können in einer Abfrage verwendet werden, um den JOIN Typ festzulegen, den der Query Optimiser für den Ausführungsplan verwenden soll: Folgende JOIN Optionen stehen zur Verfügung:
- Loop
- Merge
- Hash

Die Syntax für einen JOIN Hint ist (am Beispiel eines INNER JOINs):

FROM tabelle_1 INNER (LOOP | MERGE | HASH) JOIN tabelle_2

Hier ist ein Beispiel:

FORM header_tabelle INNER LOOP JOIN detail_tabelle

Wie man sehen kann, wird der JOIN Hint zwischen der Angabe des JOIN Typs (hier der INNER) und dem JOIN Schlüsselwort. Nur ein JOIN Hint kann zu einer Zeit pro JOIN in einer Abfrage verwendet werden. Ferner können JOIN Hints nur verwenden werden zusammen mit der ANSI JOIN Syntax, nicht mit der älteren Microsoft JOIN Syntax.

Die obige Syntax ist nicht die einzige Möglichkeit, um einen JOIN Hint in einer Abfrage zu verwenden. Man kann auch die OPTION Klausel verwenden. Diese bewirkt, daß der Hint während der gesamten Abfrage verwendet wird. Es können mehrere Hints in der OPTION Klausel auftauchen, jeder Hint jedoch nur genau 1x. Es gibt nur eine OPTION Klausel pro Abfrage.

Hier ist ein Beispiel der Verwendung der OPTION Klausel:
OPTION (INNER) oder OPTION (MERGE) oder OPTION (HASH)

Welche Methode SQL Server verwendet, um Tabellen zu joinen, hängt von zahlreichen Einzelfaktoren ab. In der Mehrzahl der Fälle wird man aber einen Nested Loop Join beobachten. Bevor man nun aber hingeht, und versucht, den JOIN durch Einsatz eines Hints zu beeinflußen, sollte man immer erst die zugrundeliegende Abfrage und die Indexes auf den zugrundeliegenden Tabellen betrachten und versuchen, diese zu optimieren, bevor man SQL Server durch den Hint in seinem Spielraum einengt und die Verarbeitungsmethode starr vorgibt. Unter Umständen kann man durch den Einsatz des Hints die Performance in den Keller ziehen, wenn z.B. unter geänderten Rahmenbedingungen der Hint nicht mehr angebracht ist; SQL Server jedoch gezwungen ist, diesen zu beachten. Von daher sollte man stets vorher unter realistischen Bedingungen testen, ob der Hint das bringt, was man erwartet.

*****

Tips und Tricks, die die Performance von Trigger erhöhen können:

Die Zeit, die ein Trigger zur Ausführung benötigt, ist eine Funktion der Anzahl der innerhalb des Triggers referenzierten Tabellen und der Anzahl der vom Trigger Code betroffenen Zeilen. Daher sollte man stets bestrebt sein, die Anzahl der im Trigger involvierten Tabellen und die Anzahl der betroffenen Zeilen zu minimieren.

Zusätzlich sollte der Code eines Triggers auf ein Minimum reduziert werden, um Overhead zu vermeiden. Dies ist wichtig, da Trigger typischerweise durch INSERT's, UPDATE's und DELETE's ausgelöst werden, die in OLTP Applikation häufig vorkommen. Je mehr Code in einem Trigger ausgeführt werden muß, umso langsamer ist jedes INSERT, UPDATE und DELETE, das stattfindet.

*****

Falls der Trigger eine WHERE Klausel beinhaltet, sollte man nicht vergessen, einen geeigneten Index zu erstellen, der verwendet werden kann. WHERE Klauseln, die sich in Triggern verstecken, werden leicht übersehen, aber wie jedes andere SELECT Statement können auch sie signifikant durch einen geeigneten Index beschleunigt werden.
Ein Weg um dies zu testen ist, den Trigger Code im Query Analyzer laufen zu lassen und den daraus resultierenden Ausführungsplan zu untersuchen. Dadurch kann man sehr schnell feststellen, ob man einen geeigneten Index hinzufügen muß oder nicht.

*****

Hat man ein INSERT, UPDATE oder DELETE Statement daß länger zu benötigen scheint als angemessen, sollte man überprüfen, ob ein Trigger auf der beteiligten Tabelle definiert ist. Das Performance Problem, das man beobachtet, könnte sehr wohl auf den Trigger zurückzuführen zu sein, nicht auf das DML Statement selber.
Man sollte nicht vergessen, Trigger Code genauso zu optimieren wie man es mit jedem anderen Code auch macht. Aufgrund der Tatsache, daß Trigger Code im Hintergrund läuft, scheinen viele Leute zu vergessen, daß der Code existiert, und potentiell für Performance Probleme verantwortlich sein kann.
Man kann Profiler und Query Analyzer verwenden, um herauszufinden, wie die Trigger in einer Datenbank arbeiten.

*****

Verwenden Sie keinen Trigger, um referenzielle Integrität sicherzustellen, wenn Sie die Option haben, auf die in SQL Server eingebauten Funktionen zur Sicherstellung referenzieller Integrität zurückzugreifen. Die Verwendung der hoch optimierten eingebauten referenziellen Integrität ist deutlich schneller als die Verwendung eines Triggers und interpretiertem Code, um die gleiche Aufgabe zu erfüllen.

*****

Wenn Sie die Wahl haben zwischen einem Trigger und einer CHECK Constraint, um Regeln oder Standards in der Datenbank sicherzustellen, sollte man sich generell für die CHECK Constraint entscheiden, da sie schneller sind als Trigger, wenn sie die gleiche Aufgabe erfüllen sollen

*****

Man sollte es vermeiden, einen Trigger zurückrollen zu müssen aufgrund des verursachten Overheads. Anstelle den Trigger ein Problem finden zu lassen und evtl. eine Transaktion zurückrollen zu müssen, sollte man den Fehler vorher versuchen abzufangen, falls das aufgrund des involvierten Codes möglich ist. Einen Fehler abzufangen (bevor der Trigger ausgelöst wird), verbraucht wesentlich weniger Server Resourcen als den Trigger zurückrollen zu müssen.

*****

Manchmal erscheint es aus Performancegrunden angebracht, denormalisierten Daten zu halten. Beispielsweise mag man aggregierte ( so z.B. kumulierte Daten) in einer Tabelle halten, da es einfach zu zeitaufwendig ist, sie on-the-fly innerhalb des SELECT Statements zu generieren. Ein Weg, diese denormalisierten Daten zu pflegen, ist die Verwendung von Triggern. Beispielsweise könnte ein Trigger immer dann ausgelöst werden, wenn ein neuer Verkauf zur Verkaufstabelle hinzugefügt wird und den Gesamtwert dieses Verkaufs zu einer Gesamtverkaufstabelle hinzufügen.

*****

Der Code, der in einem UPDATE Trigger enthalten ist, wird jedesmal ausgeführt, wenn seine zugrundeliegende Tabelle upgedatet wird. In den meisten UPDATE Triggern, betrifft der Code des Triggers nur einige wenige Spalten, nicht alle. Darum ist es nicht sinnvoll (und eine Verschwendung von SQL Server Resourcen) den gesamte Code des Triggers auszuführen, auch wenn die Spalten, an denen man ursprünglich interessiert war, überhaupt nicht aktualisiert worden sind. Mit anderen Worten, auch wenn eine Spalte aktualisiert wird, an der man nicht interessiert ist, wird der UPDATE Trigger ausgelöst und der Code ausgeführt.
Um nun die unnötige Ausführung von Code in einem UPDATE Trigger zu vermeiden, kann man eine der beiden folgenden Funktionen einsetzen: UPDATE() (verfügbar in SQL Server 2000) und COLUMNS_UPDATED() (verfügbar in SQL Server 7.0 und 2000).
Jede dieser beiden Funktionen kann verwenden werden, um zu testen, ob eine bestimmte Spalte sich verändert hat oder nicht. Daher kann man nun Code in seinem Trigger schreiben, der genau dies überprüft und falls sich diese Spalte nicht verändert hat, der Code halt nicht ausgeführt wird. Dies reduziert die Arbeit, die der Trigger ausführen muß und verbessert die allgemeine Performance der Datenbank.
Die UPDATE() Funktion wird verwendet, um die Veränderung einer einzelnen Spalte zu einer Zeit zu überprüfen. Die COLUMNS_UPDATED Funktion kann verwendet werden, um gleichzeitig mehrere Spalten zu überprüfen.

*****

Falls Sie kaskadierende referentielle Integrität (zum Beispiel kaskadierende DELETEs) in SQL Server 2000 Datenbanken, verwenden Sie kaskadierende referentielle Integritäts Constraints anstelle von Triggern, die die kaskadierenden DELETEs ausführen, da Constraints wesentlich effizienter sind und dadurch die Performance verbessern können. Falls man ältere (7.0 oder früher) Applikationen hat, die man auf SQL Server 2000 portiert hat und die Trigger benutzen, um kaskadierende DELETEs, sollte man in Erwägung ziehen, die Trigger, falls möglich zu entfernen und kaskadierende referentielle Integrität zu verwenden.

*****

Während INSTEAD OF trigger technisch identisch mit AFTER Triggern sind, liegt der Hauptgrund für die Verwendung von INSEAD OF Trigger darin, daß man mit ihnen bestimmte View Typen aktualisieren kann. Was bedeutet dies nun im Hinblick auf Performance? Unter der Annahme, daß die meisten, der von Ihnen geschriebenen Trigger, nur selten Transaktionen zurückrollen, kann man getrost weiterhin AFTER Trigger verwenden. Der Overhead eines INSTEAD OF Triggers ist größer als der eines AFTER Trigers. Ist es hingegen an der Regel, daß ROLLBACKs durchgeführt werden (mehr als in 50% aller Fälle), dann kann ein INSTEAD OF Trigger die bessere Alternative sein, da das ROLLBACK eines INSTEAD OF Triggers weniger Overhead verursacht als das eines AFTER Triggers. Also sollte man die meiste Zeit konventionelle AFTER Trigger verwenden, und sich die INSTEAD OF Trigger sparen, um damit Views upzudaten.

*****

SQL Server 2000 ermöglicht in gewissem Maße, Kontrolle über die Reihenfolge in der Trigger auszuüben. In gewissem Maße heißt, daß man angeben kann, welcher Trigger als Erster und welcher als Letzter ausgeführt wird. hat man jedoch mehr als zwei Trigger für eine Tabelle definiert, hat man keine Kontrolle über die Reihenfolge, in der die restlichen ausgeführt wird.
So, wie nun kann die Selektion der Reihenfolge, in der die Trigger ausgeführt werden, dabei helfen, die Performance einer Applikation zu verbessern? Um Trigger Performance zu optimieren, sollte man denjenigen Trigger, bei dem ein ROLLBACK am wahrscheinlichsten (aus welchem Grund auch immer), als ersten auszuführenden Trigger spezifizieren. Auf diese Weise ist nur dieser eine Trigger betroffen, wenn ein ROLLBACK durchgeführt werden muß.
Mal angenommen, man hat drei Trigger auf einer Tabelle definiert, aber anstelle dem am wahrscheinlichsten als ersten zu definieren, sei dieser als letzter definiert. Wird in diesem Szenario nun ein ROLLBACK ausgelöst, dann müssen alle drei Trigger. Wäre dieser Trigger aber als Erster definiert worden, hätte nur dieser Trigger zurückgerollt werden müssen. Verringert man die Anzahl der Trigger, die zurückgerollt werden müssen, reduziert man SQL Server's Overhead und verbessert die Performance.

Deadlocking tritt dann auf, wenn zwei Benutzerprozesse einen Lock auf ein Datenbankobjekt halten und versuchen, inkompatible Locks auf das jeweils andere Objekt zu erhalten. In dies der Fall, beendet SQL Server automatisch einen der beiden Prozesse, beendet damit den Deadlock und erlaubt dem anderen Prozeß fortzufahren. Die beendete Transaktion wird zurückgerollt und eine entsprechende Fehlermeldung wird an den Client gesendet. Im allgemeine wird derjenige Prozeß abgebrochen, dessen Zurückrollen den geringsten Overhead verursacht.

Wie man sich unschwer vorstellen kann, verschwenden Deadlock unnötig Resourcen, speziell CPU.

Die meisten gutdurchdachten Applikationen werden versuchen, die Transaktion erneut durchzuführen, nachdem sie die Deadlock Meldung erhalten haben. Diese neue Transaktion wird nun sehr wahrscheinlich erfolgreich durchlaufen. Kommt dieses Procedere häufig auf einem Server vor, kann es die Performance in den Keller ziehen. Kann die Applikation nicht mit der Deadlockmeldung umgehen, versucht also nicht automatisch die Transaktion durchzuführen, kann dies durchaus zu Verwirrung des Anwenders führen, wenn dieser die Deadlock Fehlermeldung am Bildschirm erhält.

Hier sind einige Tips, wie man Deadlocking im SQL Server vermeiden kann:

• Sicherstellen, das die Datenbank normalisiert ist.
• Sicherstellen, das die Applikation jedesmal auf die Serverobjekte in der gleichen Reihenfolge zurückgreift.
• Während einer Transaktion keine Benutzereingaben erlauben. Also, alle notwendigen Informationen vorher einsammeln.
• Cursor vermeiden.
• Die Transaktionen so kurz als möglich zu halten. Ein Weg dazu ist, die Anzahl von Roundtrips zwischen der Applikation und dem SQL Server durch den Einsatz von Gespeicherten Prozeduren zu minimieren bzw. die Transaktion in einem einzelnen Batch zu halten. Ein weiterer Weg zur Reduzierung der Dauer einer Transaktion ist es, sicherzustellen, das man nicht ständig die gleichen Daten lesen muß. Muß man Daten mehr als einmal lesen, sollte man diese versuchen durch den Einsatz von Variablen zu cachen und anschließend die Variablen auszulesen.
• Sperrzeiten minimieren. Versuchen, die Applikation so zu entwickeln, daß Sperren erst so spät wie möglich gesetzt werden und so früh wie möglich wieder aufgehoben werden.
• Falls angebracht, Sperren durch Einsatz von ROWLOCK or PAGLOCK vermindern.
• Den NOLOCK hint in Betracht ziehen, falls die Daten, die gelockt werden, nicht häufig modifiziert werden.
• Den niedrigsten Isolation Level in Betracht ziehen, mit dem man sein Ziel erreichen kann, d.h. die Transaktion erfolgreich beenden kann.
• Den Einsatz von "Bound Connections" in Betracht ziehen.

 *****

Tritt ein Deadlock auf wählt SQL Server standardmäßig den Prozeß als Victim aus, dessen Rollback den geringsten Overhead bedeutet und gibt den die Fehlermeldung 1205 zurück.

Was aber nun wenn man dieses Standardverhalten nicht mag? Kann man dies ändern? Ja, man kann. Indem man folgenden Befehl verwendet:

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

wobei:

LOW dem SQL Server mitteilt, daß die aktuelle Session das bevorzugte Deadlock Victim sein soll und nicht die Session deren Rollback den geringsten Overhead bedeutet. Die Standardmeldung 1205 wird zurückgegeben.

NORMAL teil dem SQL Server mit, daß das Standardverhalten angewendet werden soll.

@deadlock_var ist eine Zeichenfolgenvariable, die angibt, welche Deadlock Methode angewendet werden soll. "3" bedeutet LOW, "6" bedeutet NORMAL.

Dieser Befehl wird zur Laufzeit für jeweils eine Connection angewendet.

 *****

Um Deadlock Probleme zu lösen, sollte man den Einsatz eines Serverbasierten Trace in Betracht ziehen. Der Overhead für den Trace ist minimal.

DBCC TRACEON (3605,1204,-1)

Nachdem man dies ausgeführt hat, werden alle Aktivitäten, die mit dem Deadlock zusammenhängen, in das SQL Server Error Log geschrieben. Die -1 als letzter Parameter ist optional. Läßt man sie weg, gilt das Trace Flag nur für die aktuelle Connection. Setzt man hingegen die -1 wird das Flag für alle Connections gesetzt.

 *****

Um Tabellen oder Gespeicherten Prozeduren, die Deadlock Probleme verursachen, auf die Spur zu kommen, kann man die Trace Flags 1204 oder 1205 verwenden. 1204 gibt grundlegende Tracing Daten zurück, 1205 gibt detailiertere Tracing Daten zurück. Man sollte sicherstellen, daß man die Trace Flag wieder abschalten, nachdem man sein Problem gelöst hat. Obwohl Traces einen geringen Overhead verursachen, tragen sie auf der anderen Seite auch nicht zur Verbesserung der Performance des Servers bei, da sie unnötigerweise Resourcen binden, nachdem das Deadlocking Problem beseitigt ist.

 *****

Idealerweise sollten Deadloch in der Applikation ausgeschlossen werden. Kann man jedoch aus diesem oder jenem Grund nicht all Deadlock aus der Applikation ausschließen, sollte man sicherstellen, daß die Applikation anwenderfreundlich für den Umgang mit Deadlocks gerüstet ist.

Angenommen man hat zwei Transaktionen, die sich deadlocken und SQL Server beendet eine der beiden Transaktionen. In diesem Fall sendet SQL Server eine Fehlermeldung, auf die die Applikation reagieren muß. Meistens möchte man in einem solchen Zeit, eine bestimmte (zufällig gewählte) Zeit warten, bevor man die beendete Transaktion erneut an den Server sendet.

Daß eine zufällig gewählte Zeit gewartet wird, ist deshalb wichtig, da es möglich sein kann, daß eine weitere konkurrierende Transaktion ebenfalls wartet und man schließlich nicht möchte das diese beiden Transaktionen nun die gleicht Zeit warten, bevor sie erneut versuchen, die Transaktion durchzuführen und einen erneuten Deadlock verursachen.

Ist die Fehlerbehandlung ausgefeilt, kriegt der Benutzer im Idealfall nicht mit, daß überhaupt ein Deadlock Problem existiert hat.

 *****

Ein Weg, um Deadlocks zu verhindern, ist der Einsatz des UPDLOCK Query Hints. Dieser Hinweis zwingt SQL Server einen Update Lock anstelle eines Shared Locks zu verwenden.

Ein Shared Lock tritt auf, wenn eine Abfrage Daten lesen will, diese aber nicht verändern will. Shared Lock können friedlich koexistieren mit anderen Shared Locks. Das bedeutet, daß mehrere Shared Locks für eine Zeile, Seite oder Tabelle vorhanden sein können. Ein Shared Lock verhindert, daß die gesperrte Resource einen exklusiven Lock erhält. Falls also ein weiterer Benutzer eine Abfrage ausführt, die eine Zeile updatet, kann dieses UPDATE erst dann durchgeführt werden, nachdem all Shared Locks aufgelöst worden sind. Shared Locks werden unverzüglich aufgelöst, sobald sie nicht mehr benötigt werden.

Ein Update Lock ist ein Zwischending zwischen einem Shared Lock und einem Exklusive Lock. Update Lock werden benötigt, wenn eine Abfrage eine oder mehrere Zeilen in einer Tabelle aktualisieren soll. Solange aber die WHERE Klausel des UPDATE Statements nicht komplett abgearbeitet wurde, steht noch nicht fest, welche Zeilen von der Aktion betroffen sind. Anstelle nun eine Exklusive Locks auf alle Zeilen zu setzen (was Concurrency und Performance der Applikation beeinträchtigen würde), wird ein Shared Lock gesetzt und erst dann, wenn die WHERE Klausel abgearbeitet wurde, wird ein Exklusive Lock auf die betroffenen Zeilen gesetzt. Die Shared Locks, die von einem Update Lock erzeugt wurden, werden gehalten bis die WHERE Klausel abgearbeitet wurden, werden aber im Gegensatz zu "normalen" Shared Locks nicht unverzüglich wieder aufgelöst.

Wie kann nun ein Update Lock bei der Vermeidung von Deadlock helfen? Erzwingt man den Einsatz eines Update Locks anstelle eines Shared Locks während die Tabellen gelesen werden, hält der Update Lock die Lock bis das Statement oder die Transaktion beendet wird. Dies ermöglicht es, Daten zu lesen, ohne andere Benutzer zu blockieren und die Daten zu modifizieren, in der Gewißheit, daß sich diese nicht verändert haben, seit man sie zuletzt eingelesen hat. Dies verhindert Deadlocks, da andere Benutzer sich nicht einschmuggeln können und eine Zeile sperren können, die man benötigt bevor man sie benötigt.

 *****

Wenn man Tabellen erstellt, Variablen deklariert oder andere Sachen macht, bei denen man mit Datentypen in Berührung kommt, sollte man stets präzise den Untertypen angegeben, den man verwenden möchte. Hier bringt es nichts ein Lazycoder zu sein, der ein paar Tastaturanschläge sparen will.

Beispiel 1: Wenn man eine VARCHAR Variable der Länge 10 deklarieren möchte, gibt man an

DECLARE @MyVar VARCHAR(10)

und nicht

DECLARE @MyVar VARCHAR

SQL Server nimmt standardmässig eine Länge von 1 für Zeichenfolgen, sofern man nicht explizit die Länge vorgibt. SQL Server schneidet ferner die Daten ab, ohne eine Warnung auszugeben:

DECLARE @MyVar VARCHAR
SET @MyVar = 'AB' 
SELECT @MyVar AS Only_one_character

Only_one_character 
------------------ 
A

(1 row(s) affected)

Beispiel 2: Man möchte eine Variable vom Typ DECIMAL mit einer Precision von 8 und Scale von 2 deklarieren.

DECLARE MyVar DECIMAL(8,2)

Schreibt man hingegen

DECLARE @MyVar DECIMAL

unterstellt SQL Server hier den Datentyp DECIMAL(18,0).

DECLARE @MyVar DECIMAL
SET @MyVar = 123456789012345678
SELECT @MyVar
                     
-------------------- 
123456789012345678

(1 row(s) affected)

Im Unterschied jedoch zu Zeichenfolgen, generiert SQL Server eine Warnung, falls der Wert zu groß ist, um in einen DECIMAL(18,0) zu passen

DECLARE @MyVar DECIMAL
SET @MyVar = 1234567890123456789
SELECT @MyVar

Server: Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
                     
-------------------- 
NULL

(1 row(s) affected)

*****

Wenn man einen Clustered Index erstellt, sollte dieser nach Möglichkeit als UNIQUE erstellt werden. Man muß UNIQUE explizit vorgeben, da SQL Server dies nicht standardmäßig annimmt. Der Grund dafür liegt in den Interna des Servers. Unter der Haube ist jeder Clustered Index UNIQUE. Wurde der Index nicht so erstellt, greift SQL Server automatisch ein, wenn - und auch nur dann - Duplikate in den oder die Index Schlüssel eingefügt werden sollen und fügt einen Integer Wert hinzu, der die Einmaligkeit der Schlüssel garantiert. Dieser 4 Byte Integer Wert wird auch "Uniquifier" genannt. Er verbreitert den Index entsprechend und führt dazu, daß mehr IO Operationen notwendig sind, um die relevanten Daten abzurufen. Die Performance ist negativ betroffen.

Es existiert allerdings noch ein weiterer guter Grund, warum idealerweise der Clustered Index UNIQUE sein sollte. Dazu muß man sich vor Augen führen, daß die Schlüssel des Clustered Index in der Blattebene der Nonclustered Indexes gespeichert werden. Erstellt man nun den Clustered Index neu (oder reorganisiert ihn), müssen ALLE Nonclustered Indexes der Tabelle ebenfalls neu erstellt werden, wenn der Clustered Index nicht UNIQUE erstellt wurde. Während diese Vorgehensweise die übliche war vor SQL Server 2000 Service Pack 2, hat sich das Verfahren mit der Einführung von SP 2 geändert. Jetzt werden die Nonclustered Indexes nur unter der Voraussetzung neu erstellt, daß der Clustered Index nicht als UNIQUE kreiiert wurde. Der Grund dafür liegt darin, daß während der Neuerstellung oder Reorganisation des Clustered Index dieser "Uniquifier" neu generiert wird. Bei einem nicht UNIQUE Clustered Index muß sich zwangsläufig diese Veränderung auch in den Nonclustered Indexes widerspiegeln, d.h. sie müssen ebenfalls neu erstellt werden. Wurde hingegen der Index als UNIQUE erstellt, gibt es keinen Uniquifier und in der Blattebene der Nonclustered Indexes finden sich nur die Schlüssel des Clustered Index. Da diese sich bei Neuerstellung nicht ändern, braucht auch nicht an den Nonclustered Indexes geändert zu werden.

*****

Nach Möglichkeit sollte es vermieden werden, einen Clustered Index auf einer GUID Spalte (Uniqueidentifier Datentyp) zu erstellen. Hier sind einige Gründe:

1. GUID belegen 16 Bytes Speicherplatz. Dies ist, zum Vergleich, 4x so viel wie eine INTEGER Spalte. GUIDs machen den Index deutlich breiter, was dazu führt, daß weniger Daten auf einer Datenseite gespeichert werden können. SQL Server muß deshalb mehr IO Operationen ausführen, um die relevanten Daten abzurufen. Dies dürfte sich negativ auf die Performance auswirken.
2. Die Schlüssel eines Clustered Index werden zusätzlich zu den eigentlichen Index Schlüsseln in der Blattebene jedes Nonclustered Index einer Tabelle gespeichert. Dies führt ebenfalls zu einer Verbreiterung der Nonclustered Indexes, die Einfluß auf die Performance hat.
3. GUIDs sind von Natur aus "zufällig". Das heißt, man kann nicht voraussagen, ob der "nächste" Wert vor oder hinter dem letzten eingefügt werden muß. So muß man entweder entsprechend viel Platz auf einer Datenseite lassen (und damit Speicherplatz wenig effektiv nutzen) oder sich ggfs. mit Page Splittings auseinandersetzen beim einem ausgelasteten System.
4. GUIDs sind wenig intuitiv. Aus diesen Grund darf bezweifelt werden, daß die wichtigsten und kritischsten Abfragen an ein System sich an einer GUID Spalte ausrichten oder daß man häufig GUIDs sortiert oder gruppiert. Da ein geschickt ausgewählter Clustered Index eine deutlich verbesserte Performance bewirken kann, sollte man ihn sich für eine besser geeignete Kombination von Spalte(n) aufheben.
5. Ist man durch externe Einflüße gezwungen, GUIDs zu verwenden (z. B. in Replikationen Szenarien), und man entscheidet sich, die GUID Spalte auch zum PRIMARY KEY der Tabelle zu machen, sollte man darauf achten, daß SQL Server diesen PRIMARY KEY nicht auch automatisch zum Clustered Index macht (sofern bisher noch kein Clustered Index vorhanden ist auf dieser Tabelle). Dies kann dadurch erreicht werden, indem man entweder
   1. Explizit das NONCLUSTERED Schlüsselwort bei der Erstellung des Index via Skript angibt.
   2. Den Haken aus der entsprechende Option entfernt, falls man den Index über das graphische Management Interface erstellt.
6. Zu guter Letzt, und vielleicht auch weniger wichtig. GUID sind nicht ANSI komform und daher nicht unbedingt portabel.

*****

 Als Faustregel gilt: Jede Tabelle sollte einen Clustered Index besitzen. Im allgemeinen, jedoch nicht immer stur anwendend, sollte der Clustered Index auf einer Spalte mit stetig steigenden eindeutigen Werten liegen. In vielen Fällen ist der Primärschlüssel einer Tabelle auch ein guter Kandidat für den Clustered Index.

Falls man Erfahrung mit Performance Tuning im SQL Server 6.5 hat, hat man vielleicht gehört, daß es keine gute Idee ist, den Clustered Index auf eine Spalte zu legen, deren Werte stetig steigen, da dies "Hotspots" auf der Festplatte verursachen kann, die zu Performance Problemen führen können. Dies gilt für SQL Server 6.5.

In SQL Server 7.0 und 2000 sind "Hotspots" generell kein Problem. Man müßte über 1.000 Transaktionen pro Sekunde haben, bevor so ein "Hotspot" negativ die Performance beeinflussen könnte. Tatsächlich kann so ein "Hotspot" vorteilhaft unter diesen Umständen sein, da er Page Splits eliminieren kann.

Warum? Angenommen man fügt neue Zeilen zu einer Tabelle hinzu, deren Primärschlüssel auch der Clustered Index ist. Die Werte in dieser Spalte steigen monoton steigend an. Dies bedeutet, daß jedes INSERT physikalisch hinter dem vorherigen auf der Festplatte eingefügt wird. Dadurch können keine Page Split während der INSERTs vorkommen, was für sich genommen den Overhead hilft zu verringern. Grund dafür ist SQL Servers Fähigkeit festzustellen, ob die hinzuzufügenden Daten bei einer monoton steigenden Folge auf eine Seite passen oder nicht. Pasen sie nicht mehr auf die Seite, wird eine neue allokiert und Page Splits kommen erst gar nicht vor.

Fügt man eine Menge Daten in eine Heap Tabelle (also eine Tabelle ohne Clustered Index) ein, werden die Daten in keiner speziellen Reihenfolge eingefügt. Egal, ob sie monoton steigend sind oder nicht, die Daten werden dort eingefügt, wo SQL Server feststellt, daß genügend Platz vorhanden ist. Üblicherweise führt dies dazu, daß SQL Server aber auc mehr Arbeit verrichten muß, wenn die Daten von der Festplatte angefordert werden. Fügt man nun einen Clustered Index zu so einer Tabelle hinzu, werden die Daten sequentiell in die Datenseiten geschrieben und im allgemeinen benötigt man weniger I/O Disk Operationen, um die Daten von der Festplatte abzurufen.

Falls Daten in einem eher zufälligen Muster in einen Clustered Index eingefügt werden, werden die Daten oftmals eher zufällig physikalisch in die Datenseiten eingefügt, was vergleichbar ist mit dem Problem des Einfügens in eine Heap Tabelle.

Nochmals, die beste allgemeingültige Empfehlung besteht darin, den Clustered Index auf eine Spalte zu legen, deren Werte monoton steigend sind (falls es eine solche Spalte in der Tabelle gibt). Besonders gilt dies für eine Tabelle, in die häufig INSERTs, UPDATEs und DELETEs stattfinden. Sind hingegen die Daten in der Tabelle eher statisch und verändern sich kaum, werden aber häufig über SELECT Statements abgefragt, ist dieser Rat weniger hilfreich, und andere Optionen für den Clustered Index sollten in Betracht gezogen werden. Im weiteren Verlauf finden sich weitere Tips für solche Situationen, in denen man den Clustered Index auf andere Spalten legen sollte.

*****

Hier sind einige gute Gründe, warum jede Tabelle einen Clustered Index besitzen sollte.

Man sollte bedenken, daß ein Clustered Index die physikalische Sortierung der Daten einer Tabelle gemäß den Schlüsselwerten bestimmt. Eine Heap Tabelle unterliegt nicht solchen Mechanismen. Ihre Daten werden in keiner bestimmten physikalischen Sortierung gespeichert.

Immer wenn man die Spalte(n) abfragt, die für den Clustered Index verwendet werden, hat SQL Server die Möglichkeit, die Daten sequentiell aus dem Clustered Index in Extent Blöcken (= 8 Datenseiten oder 64 kb) auf einmal zu lesen. Dies macht es dem Disk Subsystem sehr einfach, die Daten sehr schnell zu lesen, besonders wenn viele Daten abgerufen werden.

Liegt aber hingegen eine Heap Tabelle vor, muß SQL Server die Daten eher zufällig von Platte lesen (auch wenn ein angemessener Nonclustered (noncovering) Index vorhanden ist). Dies bedeutet eine Menge Extraarbeit, die das Disksubsystem verrichten muß, um die gleichen Daten zurückzugeben. Dies geht zu Lasten der Performance.

Ein weiterer Nachteil einer Heap Tabelle zeigt sich, wenn man Indexes neu erstellt, um Fragmentierung zu reduzieren. Heaps können nicht defragmentiert werden, da sie keine Indizes sind. Dies bedeutet, daß über die Zeit, die Daten tendenziell eher mehr und mehr fragmentiert werden, was weiter die Performance verschlechtert. Durch das Hinzufügen eines Clustered Index kann man sicherstellen, daß eine Tabelle defragmentiert wird, wenn die Indizes neu erstellt werden.

*****

Da es nur einen Clustered Index pro Tabelle geben kann, sollte man sich besonders viel Zeit nehmen, um herauszufinden, wie dieser aussehen sollte. Dabei sollte man die Abfragen an diese Tabelle in Betracht ziehen und eine fundierte Meinung dazu haben, welche dieser Abfragen, die kritischste (und unter Umständen am häufigsten ausgeführte) ist und ob gerade diese Abfrage von dem gewählten Clustered Index profitieren würde.

*****

Clustered Indexes sind nützlich für Abfragen, die folgenden Kriterien erfüllen:

* Für Abfragen, die einen breiten Bereich abfragen oder wenn man ein sortiertes Ergebnis benötigt. Die Daten sind bereits durch den Index vorsortiert. Beispiele hierfür sind zum Beispiel BETWEEN, <, >, GROUP BY, ORDER BY und Aggregate wie MAX, MIN, and COUNT in Abfragen.

* Für Abfrage, in denen man nach einem einmaligen Wert (wie zum Beispiel eine Mitarbeiter-Nummer) sucht und alle oder fast alle Daten dieser zeile benötigt. Grund hierfür ist, daß die Abfrage durch den Index gecovered ist. Mit anderen Worten, sind die Daten, die man benötigt, der Index selber und SQL Server muß keine weiteren Daten lesen.

* Für Abfragen, die auf Spalten zugreifen, die über eine limitiert Anzahl unterschiedlicher Werte verfügen. so wie zum Beispiel Länder Daten. Hat aber die Spalte kaum unterschiedliche Daten, so wie Spalte mit "Ja" und "Nein" oder "Männlich" und "Weiblich", bedeutet es eine Verschwendung, dies Spalten für den Clustered Index zu verwenden.

* Für Abfragen, die in JOIN oder GROUP BY Klauseln verwendet werden.

* Für Abfragem, die eine Menge Daten zurückgeben sollen, nicht nur einige wenige. Dies liegt wieder daran, daß die Daten der Index selber sind und SQL Server nicht noch irgendwo anders nachschauen muß.

*****

Falls man vor einer Situation steht, in der man einen einzelnen, breiten Index (ein zusammengesetzter Index von 3 oder mehr Spalten) in einer Tabelle zu haben, während die restlichen Indizes dieser Tabelle (sofern vorhanden) nur über eine Spalte gehen, sollte man darüber nachdenken, den breiten Index zum Clustered Index zu machen, während die anderen Nonclustered erstellt werden sollten.

Warum? Ist der breite Index der Clustered Index bedeutet dies, daß die gesamte Tabelle der Index ist und kein großer zusätzlicher Plattenspeicher benötigt wird, um den Index zu erstellen. Ist hingegen der breite Index ein Nonclustered Index, muß SQL Server hierfür einen "relativ großen" neuen Index erstellen, der Speicherplatz in Anspruch nimmt.

*****

Man sollte vermeiden, den Clustered Index auf Spalten zu legen, die bereits durch Nonclustered Indizes abgedeckt werden. Dies führt zu redundanten Indizes. Man sollte geeignetere Spalten für den Clustered Index verwenden.

*****

Bei der Auswahl von potentiellen Spalten für den Clustered Index sollte man Spalten vermeiden, deren Daten häufig verändert werden. Jedes Mal wenn sich ein Wert eines Clustered Index verändert, müssen sämtliche anderen Nonclustered Indizes ebenfalls verändert werden, um diese Modifikation zu reflektieren, was einen nicht zu unterschätzenden Overhead darstellt.

*****

Bei der Auswahl der Spalte oder der Spalten für den Clustered Index, sollte man die Spalte verwenden, nach der am häufigsten gesucht wird. Bei einem Zusammengesetzten Clustered Index sollte diese Spalte am erster Stelle stehen.

*****

Falls eine Tabelle sowohl einen Clustered Index als auch Nonclustered Indizes enthält, wird die Performance optimiert, wenn der Clustered Index auf einer einzelnen Spalte liegt, die so kompakt als möglich ist. Grund dafür ist, daß sämtliche Nonclustered Indizes die Schlüssel des Clustered Index verwendet, um die Daten zu lokalisieren. Sowohl der Clustered Index als auch die anderen Indizes profitieren davon.

*****

Der Primärschlüssel einer Tabelle muß nicht zwingend auch deren Clustered Index sein. Dies ist zwar SQL Servers Standard, sofern man es nicht anders angibt, ist aber nicht immer die beste Wahl. Man sollte nur dann den Primärschlüssel auch zum Clustered Index machen, wenn man regelmäßig Range Abfragen über den Primärschlüssel ausführt oder wenn das Ergebnis anhand des Primärschlüssels sortiert sein soll.

*****

Löscht man einen Clustered Index, sollte man bedenken, daß man freien Speicherplatz braucht, der ca. 1.2 x so groß ist wie die Tabelle selbst.
Dies gilt auch, wenn ein Clustered Index neu erstellt werden soll.

*****

Man sollte stets den kleinsten Datentypen auswählen, der ausreicht, um die Daten zu speichern. Die Gründe dafür sind einfach und einleuchtend:

• Je kompakter die Daten sind, desto mehr Daten passen auf eine Datenseite. Also ist SQL Server in der Lage mit jeder einzelnen IO Operation mehr Daten aufzunehmen, was wiederum dazu führt, daß auch weniger IO Operationen benötigt werden, um eine Aufgabe auszuführen.
• Je kompakter die Daten sind, umso weniger Daten müssen vom Server auf den Client geschafft werden. Dadurch wird der Netzwerkverkehr und Latenzen verringert.
• Eine Spalte wird schneller sortiert, je schmaller sie ist. Dies gilt besonders für die Zeichenfolgen Datentypen.
• Die belegte Speichermenge im Buffer Cache wird verringert. Dadurch können mehr Daten gecached werden.

*****

Auch wenn es vielleicht selbstverständlich ist, sollte man für Spalten mit deren Werten man Berechnungen anstellen will, einen der nummerischen Datentypen des SQL Servers wählen. Immer wieder kann man beobachten, das solche Daten in Zeichenfolgen Datentypen gespeichert werden. Die Wahl eines nummerischen Datentypen hat den Vorteil, das keine Konvertierung (explizit oder implizit) notwendig ist, um mit den Daten rechnen zu können.

*****

 NVARCHAR oder NCHAR sollten nur dann verwendet werden, wenn man unbedingt Unterstützung für Unicode Zeichen braucht. In allen anderen Fällen ist der Einsatz eine Verschwendung von SQL Server Resourcen. Da sie doppelt soviel Speicherplatz belegen, muß SQL Server unnötig viele I/O Operationen durchführen. Ferner belegen sie unnötig viel Platz im Buffer Cache.

*****

Variieren die Daten in Textspalten deutlich in Länge, ist der Einsatz von VARCHAR gegenüber CHAR vorzuziehen. Dadurch das VARCHAR nur die tatsächlich eingegebene Anzahl an Zeichen belegt, kann man deutlich an Speicherplatz sparen. Was wiederum I/O Operationen spart und damit die allgemeine Performance erhöht.

Ein weiterer Vorteil von VARCHAR ist, daß solche idR schneller sortiert werden können als CHAR Spalten, da SQL Server nur die tatsächlich belegten Zeichen sortieren muß und nicht die gesamte Länge der Spalte.

*****

Variieren die Daten einer Spalte jedoch nicht deutlich in Länge, sollten man den Einsatzt von CHAR in Betracht ziehen. Üblicherweise können solche Daten mit einer CHAR Spalte schneller verarbeitet werden.

*****

FLOAT (oder REAL) Datentypen sind nicht für PRIMARY KEYs geeignet. Nicht nur haben solche Spalten eine unnötigen Overhead, es existieren auch diverse KB Artikel zu Problemen bei FLOAT Daten in Indizes.

*****

Verwendet man Zeichendatentypen fester Länge (CHAR oder NCHAR), sollte man nach Möglichkeit vermeiden, NULL dort zu speichern. Zeichenfolgen Spalten fester Länge belegen immer den vordefinitierten Platz, egal was dort gespeichert wird. Es ist also eine immense Speicherplatzverschwendung, wenn man in einer CHAR(50) Spalten einen NULL Marker speichert. In solchen Fällen macht es mehr Sinn, einen Datentypen variabler Länge zu verwenden.

*****

Normalerweise ist vom Einsatz berechneter Spalten eher abzuraten, da sie Normalisierungsregeln verletzen. Aber manchmal kann es auch effizienter sein, eine solche Spalte einzusetzen als immer wieder diese Berechnung in Abfragen durchzuführen. Dies gilt umsomehr für Berechnung in Abfragen, die sehr häufig ausgeführt werden. Durch ein berechnete Spalte kann man hier die allgemeine Arbeit, die SQL Server verrichten muß, deutlich reduzieren. Allerdings sollte man stets im Einzelfall entscheiden, ob eine berechnete Spalte sich lohnt.

*****

Der SQL_VARIANT Datentyp mag zwar wie eine eierlegende Wollmilchsau erscheinen, bietet aber mehr Nachteile als Vorteile. Er sollte nach Möglichkeit vermeiden werden.

*****

Hat man eine Spalte, von der man im Vorfeld weiß, daß diese Daten häufig sortiert werden müssen, sollte man versuchen, diese Spalte INTEGER basiert zu erstellen, nicht Zeichenfolgenbasiert. Integers lassen sich wesentlich einfacher und schneller sortieren als Zeichenfolgen.

*****