Datenbank-Performance-Optimierung

Inhaltsverzeichnis

Begriffs- und Abkürzungsverzeichnis

1. Einleitung

2. Grundlagen
2.1 Optimierungsmodell
2.2 Interne Verarbeitung von SQL-Anweisungen
2.3 Datenverwaltung durch den SQL Server
2.3.1 Datenbank
2.3.2 Lesen von Datenseiten
2.3.3 Schreiben von Datenseiten
2.4 Performanceüberwachung und Leistungsmessung
2.4.1 Ausführungszeit einer SQL-Anweisung messen
2.4.2 Statistische Größen mittels T-SQL "SET-Anweisungen" ermitteln
2.4.3 Grafische Ausführungspläne
2.4.4 Der Aktivitätsmonitor
2.4.5 Berichte

3. Systemumgebung
3.1 Arbeitsspeicher
3.2 Prozessor
3.3 Festplatten
3.3.1 Festplattenarten
3.3.2 RAID-Systeme
3.4 Netzwerk
3.5 Betriebssystem
3.6 Datenbankmanagementsystem

4. Indizes
4.1 Grundlagen von Indizes
4.2 Verwendung von Indizes
4.2.1 HEAP - Tabelle ohne gruppierten Index
4.2.2 Gruppierte Indizes (Clustered Index)
4.2.3 Nicht gruppierte Indizes (Nonclustered Index)
4.2.4 Eindeutige Indizes (Unique Indexes)
4.2.5 Gefilterte Indizes (Filtered Indexes)
4.2.6 Kombinierte Indizes (Combined Indexes)
4.2.7 Abdeckende Indizes (Covering Indexes)
4.3 Verwaltung von Indizes
4.3.1 Der Füllfaktor
4.3.2 Indizes reorganisieren
4.3.3 Indizes neu erstellen
4.3.4 Fehlende Indizes aufspüren
4.3.5 Überflüssige Indizes

5. Statistiken
5.1 Statistiken anzeigen
5.2 Statistiken anlegen
5.3 Statistiken aktualisieren
5.4 Gefilterte Statistiken

6. Partitionierung
6.1 Hardwareorientierte Partitionierung
6.2 Vertikale Partitionierung
6.3 Horizontale Partitionierung

7. Abfrageoptimierung
7.1 Aufbau einer Abfrage
7.2 Parametrisierung
7.2.1 Automatische Parametrisierung
7.2.2 Erzwungene Parametrisierung
7.2.3 Explizite Parametrisierung
7.2.4 Parametrisierung durch gespeicherte Prozeduren
7.2.5 Parametrisierungsproblematik
7.3 Hinweise
7.3.1 Abfragehinweise (Query Hints)
7.3.2 Tabellenhinweise (Table Hints)
7.3.3 Verknüpfungshinweise (JOIN Hints)

8. Fazit

9. Literaturverzeichnis

10. Abbildungsverzeichnis

11. Tabellenverzeichnis

12. Anhang

Begriffs- und Abkürzungsverzeichnis

Abbildung in dieser Leseprobe nicht enthalten

1. Einleitung

„Die Optimierung von Datenbanken bedeutet im Allgemeinen, von den vielfältigen Möglichkeiten der Konfiguration des Gesamtsystems Gebrauch zu machen, so dass das Datenbanksystem gemessen an den Anforderungen seiner Anwendungen optimal, d.h. den Erwartungen entsprechend reagiert. “ (KNOLLE in Kudr07, S. 300)

Datenbanken sind das Kapital einer modernen Informationsgesellschaft und bilden die Grundlage fast jeder IT-Architektur. Durch stetig wachsende Datenmengen, die zunehmende Komplexität und Vernetzung von unterschiedlichen Anwendungen, sowie die Anforderungen an eine Hochverfügbarkeit, gewinnt auch die Performance-Optimierung verstärkt an Bedeutung.

Diese Arbeit zeigt unterschiedliche Möglichkeiten der Datenbank-Performance-Optimierung mit dem Microsoft SQL Server 2008 auf, die einerseits durch interne Funktionen und Automatismen, andererseits durch geeignete manuelle Maßnahmen vorgenommen werden können. Aufgrund der Komplexität, des enormen Umfangs der Thematik, sowie der zahlreichen Sonderfälle konzentriert sich diese Arbeit auf ausgewählte Ansatzpunkte einer Performance-Optimierung. Neben den Aspekten der eigentlichen Optimierungsmöglichkeiten bilden die Grundlagen und die Funktionsweise der einzelnen Themenbereiche einen wichtigen Teil dieser Arbeit, um ein übergreifendes Verständnis der Gesamtzusammenhänge zu erhalten. Insbesondere auf die Überwachung einzelner Komponenten und Parameter, sowie die Ursachenfindung bestehender Probleme wird speziell eingegangen.

Diese Arbeit gliedert sich im Wesentlichen in sechs Abschnitte:

Kapitel zwei stellt eine grundsätzliche Optimierungsstrategie vor und gibt einen Überblick über die nötigen Grundlagen, welche für eine spätere Performance-Optimierung und Überwachung des Gesamtsystems erforderlich sind. Im dritten Kapitel werden die notwendigen technischen Voraussetzungen für eine optimale Gesamtperformance dargestellt. Das vierte Kapitel thematisiert die vom SQL Server unterstützten Indizierungsmöglichkeiten und deren Verwaltung. Kapitel fünf beschäftigt sich mit Statistiken und ihrer Auswirkung auf die Abfrageperformance. Das sechste Kapitel widmet sich dem Konzept der Datenverteilung mittels Partitionierung. Abschließend wird im siebten Kapitel in die Thematik der Abfrageoptimierung aus Sicht des Anwendungsentwicklers eingeführt.

2. Grundlagen

2.1 Optimierungsmodell

Dieses Kapitel basiert im Wesentlichen auf: [Kali05a], [Pant07], [Pant10], [Schm07] und [Schm09].

Für eine erfolgreiche Performance-Optimierung ist ein systematisches Vorgehen unabdingbar. Nach Schmeling hat sich in der Praxis das in Abbildung 1 gezeigte Modell zur PerformanceOptimierung als geeignet erwiesen, wobei die einzelnen Optimierungsphasen jeweils auf die Vorhergehenden aufbauen.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 1: Optimierungsmodell (Quelle: Eigene Darstellung in Anlehnung an [Schm09])

Eine Performance-Optimierung erscheint oftmals erst im laufenden Produktivsystem erforderlich, da das Gesamtsystem plötzlich spürbar langsamer geworden ist. Dies ist jedoch der falsche Ansatz. Eine Optimierung sollte bereits auf dem speziell für eine Anwendung ausgelegten Datenbankdesign aufsetzen, um die Zugriffe auf die Datenbank zu minimieren. Der Fokus liegt dabei vor allem auf einer effizienten Datentypenwahl, ebenso wie dem sinnvollen Einsatz von Normalisierung und Denormalisierung in Bezug auf die anwendungsspezifische Nutzung.

Die zweite Phase, die Optimierung des eigentlichen SQL Codes, bietet in der Regel das größte Potential, um spürbaren Performancegewinn zu erzielen. Genaueste Kenntnisse über die Funktionsweise des SQL Servers, sowie die interne Verarbeitung eines SQL Befehls, sind dabei notwendig.

Im Anschluss an die SQL Code Optimierung empfiehlt sich eine Indexoptimierung, um die Datenbankstruktur durch entsprechende Indizes zu ergänzen und überflüssige zu eliminieren. Eine einmalige Erstellung reicht nicht aus, weshalb die vorhanden Indizes von Zeit zu Zeit reorganisiert oder komplett neu erstellt werden sollten.

Im vierten Optimierungsschritt gilt es, Blockierungen durch gesperrte Ressourcen aufzuspüren und zu minimieren. Gesperrte Ressourcen entstehen, indem mehrere Transaktionen versuchen gleichzeitig auf bestimmte Ressourcen zuzugreifen.

Für viele Systemadministratoren ist die Hardwareaufrüstung der erste Schritt um Performanceprobleme zu beheben. Dadurch werden jedoch meistens die eigentlichen Problemursachen nicht beseitigt, sondern nur eine kurzzeitige Performancesteigerung erreicht. Dementsprechend sollte eine Hardwareaufrüstung das letzte Mittel sein, um eine Performancesteigerung zu erreichen.

2.2 Interne Verarbeitung von SQL-Anweisungen

Dieses Kapitel basiert im Wesentlichen auf: [MaUn03], [Micr11], [Pant10], [Schm09], [Warn07] und[Wood07].

Die SQL Code Optimierung erfordert genaueste Kenntnisse des SQL Servers und der Verarbeitung von SQL-Anweisungen. Um die Funktionsweise des SQL Servers, insbesondere die Reihenfolge der Verarbeitung von SQL-Anweisungen zu verstehen, wird zunächst folgendes Beispiel betrachtet:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 1: Einfache SQL Abfrage - Reihenfolge der Verarbeitung von SQL-Befehlen

Die obige Abfrage liefert eine Auflistung aller Unterkategorien, sowie die Anzahl der in ihr enthaltenen Produkte zurück. Dabei werden nur Kategorien beachtet, welche mindestens 75 Produkte und den String „kamera“ im Kategorienamen enthalten.

Der SQL Server verarbeitet die einzelnen Klauseln der Abfrage in einer exakt festgelegten, logischen Reihenfolge, welche Abbildung 2 verdeutlicht.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 2: Reihenfolge der Verarbeitung von SQL-Befehlen

Über FROM werden zunächst alle benötigten Tabellen gelesen und anschließend über JOIN und ON miteinander verknüpft. Im nächsten Verarbeitungsschritt werden die Ergebnisse durch WHERE-Bedingungen gefiltert und somit in der Zeilenanzahl eingeschränkt. Das entstandene Zwischenergebnis wird dann über GROUP BY zeilenweise zusammengefasst. WITH CUBE bzw. WITH ROLLUP ergänzen die Zeilen mit Zwischensummen anhand der GROUP BY- Aggregationen. Die HAVING-Klausel filtert das Zwischenergebnis und reduziert folglich die Zeilenanzahl weiter. Erst im achten logischen Verarbeitungsschritt werden die benötigten Spalten über SELECT ausgewählt und das Zwischenergebnis in der Spaltenanzahl dezimiert. Eine anschließende Zeilenfilterung erfolgt dann über DISTINCT. Das Ergebnis kann nun noch mit Hilfe der ORDER BY-Klausel sortiert und abschließend über TOP weiter in der Zeilenanzahl eingeschränkt werden. Letztendlich wird nun das Gesamtergebnis zurückgegeben.^[1]

Jeder Verarbeitungsschritt erzeugt gewissermaßen eine temporäre Tabelle, welche an den nächsten Bearb ei tungs schritt weitergereicht wird. Diese temporäre Tabelle wird hierbei in jedem Schritt durch Hinzufügen und Löschen von Zeilen und Spalten verändert.

Die logische Schlussfolgerung daraus ist: Je früher die Quelldaten gefiltert werden, desto weniger Daten müssen an den nächsten Bearbeitungsschritt weitergegeben und verarbeitet werden.

Folgende Beispiele sollen den Performanceunterschied zwischen einer Abfrage über die HAVING-Klausel (Beispiel 2) und über die WHERE-Klausel (Beispiel 3) verdeutlichen. Beide Abfragen geben alle Produkte aus, die im Produktnamen den String „kamera‘‘ enthalten und im Onlineshop mindestens 5.000 mal verkauft wurden.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 2: Vergleich von HAVING und WHERE – Abfrage mit HAVING-Klausel

Die Abfrage mit Hilfe der HAVING-Klausel benötigte insgesamt 2.656 Millisekunden.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 3: Vergleich von HAVING und WHERE – Abfrage mit WHERE-Klausel

Eine entsprechend modifizierte Abfrage über eine WHERE-Bedingung benötigte 1.627 Millisekunden. Die Umstellung von HAVING auf WHERE bewirkte demnach eine Geschwindigkeitssteigerung von über 60%.

Es ist offensichtlich, dass eine Filterung über die WHERE-Klausel effektiver ist, da die Quelldaten bereits vor der Gruppierung selektiert werden und die darauffolgenden

Bearbeitungsschritte somit weniger Daten verarbeiten müssen. Bei HAVING werden die Zeilen mit GROUP BY zusammengefasst und erst im Anschluss erfolgt die eigentliche Filterung der Zeilen. Eine Verwendung der HAVING-Klausel macht also nur Sinn, wenn diese Aggregatfunktionen (z.B. COUNT(), SUM(), AVG(), MIN(), MAX() ) enthält - welche im WHERE-Teil nicht erlaubt sind. Ein weiterer Performancevorteil der WHERE-Klausel besteht in der Möglichkeit der Verwendung von Indizes für die angegebenen Bedingungen, die für HAVING nicht benutzt werden können.

Bevor jedoch eine SQL-Anweisung überhaupt ausgeführt wird, erstellt der SQL-Server einen Abfrageausführungsplan (engl. Execution Plan). In diesem wird unter anderem festgelegt, in welcher Reihenfolge auf die einzelnen Tabellen zugegriffen wird und welche Methoden verwendet werden, um die benötigten Daten aus den Tabellen zu filtern.

Der Abfrageausführungsplan wird hierbei in drei Bearbeitungsschritten von den drei nachfolgend beschriebenen Modulen erzeugt.^[2]

Der Parser

Im ersten Schritt scannt der Parser die SQL-Anweisung und zerlegt die einzelnen Anweisungen in logische Einheiten. Dabei wird eine einfache, formale Prüfung der SQL- Syntax durchgeführt. Letztendlich erstellt der Parser eine Abfragestruktur (auch Ausführungsbaum, Sequenzstruktur oder Syntaxbaum genannt), welche die logischen Schritte beschreibt, die für die Umwandlung der Quelldaten benötigt werden.

Der Algebrizer

Die vom Parser erzeugte Abfragestruktur wird nun vom Algebrizer weiterverarbeitet. Dabei führt er eine genauere Syntaxprüfung durch, die nicht nur die Richtigkeit der beteiligten Tabellen und Spalten, sondern auch die Datentypen überprüft. Der Algebrizer optimiert die Abfragestruktur weiter, entfernt dabei redundante Operationen und hilft bei der Bestimmung von Unterabfragen und Aggregatfunktionen. Als Ergebnis erzeugt der Algebrizer einen optimierten Syntaxbaum, welcher im Plancache gespeichert wird. Der Plancache ermöglicht die spätere Wiederverwendung von Ausführungsplänen, um das erneute Erstellen eines bereits erzeugten Ausführungsplans zu verhindern.

Der Abfrageoptimierer

Der Abfrageoptimierer erstellt die endgültige Abfragestruktur - den Ausführungsplan. Der Optimierer analysiert verschiedene Möglichkeiten des Zugriffs auf die Quelldaten, die letztlich alle zum korrekten Ergebnis führen. Dabei legt er unter anderem die Reihenfolge der Zugriffe auf die einzelnen Tabellen, die Verwendung von Indizes, sowie die physikalische Ausführung von JOIN-Operationen^[3] fest. Der Optimierer arbeitet hierbei kostenorientiert, dass heißt er wählt den Ausführungsplan, welcher die geringsten zu erwartenden Kosten bezüglich der benötigten Ressourcen verursacht und die Ergebnisse so schnell wie möglich an den Benutzer zurückgibt. Um die zu erwartenden Kosten schätzen zu können, greift der Optimierer auf Verteilungsstatistiken zurück (siehe Kapitel 5).

2.3 Datenverwaltung durch den SQL Server

Dieses Kapitel basiert im Wesentlichen auf: [Baud08], [Geis06], [KaCZ08], [Lang01], [Micr11], [Pant10], [Schm07] und[Schm09].

Die Hauptaufgabe einer Datenbank besteht im Speichern und Abfragen von Daten. Diese Daten werden von der Festplatte oder aus einem Puffercache im Hauptspeicher gelesen. Aufgrund mechanischer Vorgänge, wie der Neupositionierung des Schreib- und Lesekopfes einer Festplatte, ist die Ein- und Ausgabe (E/A) von Daten einer der größten Engpässe in einer Datenbank. Ein Optimierungsansatz besteht deshalb in der Minimierung der Lese- und Schreibvorgänge auf dem Datenträger, was ein gewisses Grundverständnis des E/A-Systems des SQL Servers voraussetzt.

2.3.1 Datenbank

Jede Microsoft SQL Server Datenbank besteht aus mindestens zwei Betriebssystemdateien, einer Datendatei und einer Protokolldatei. In Datendateien werden Daten und Objekte gespeichert. Dies sind in erster Linie die eigentlichen Datenbanktabellen, aber auch Indizes, Prozeduren und Sichten. Protokolldateien dagegen enthalten Informationen zur Wiederherstellung einer Datenbank.

Primäre Datendatei

Die primäre Datendatei ist, wie der Name schon verrät, die zentrale Datei einer Microsoft SQL Server Datenbank. Diese Datendatei, mit der Standard-Dateiendung „MDF“, enthält allgemeine Startinformationen für die Datenbank, sowie Referenzen auf weitere Datendateien. In der primären Datendatei können zudem Daten und Objekte gespeichert werden.

Sekundäre Datendateien

Sekundäre Datendateien sind optional für den Betrieb einer Microsoft SQL Datenbank. Sie können verwendet werden, um Daten und Objekte einer Datenbank auf mehreren Speicherorten zu verwalten. Eine sekundäre Datendatei besitzt die Standard-Dateiendung „NDF“.

Protokolldateien

Jede Microsoft SQL Server Datenbank besitzt mindestens eine Protokolldatei mit der Standard-Dateiendung „LDF“. Transaktionsprotokolldateien enthalten Informationen über jegliche Änderungen einer Datenbank, welche noch nicht in einer Datendatei gespeichert wurden. Hiermit werden zum einen Transaktionen und zum anderen eine komplette Wiederherstellung einer Datenbank ermöglicht.

2.3.2 Lesen von Datenseiten

Da, wie bereits erwähnt, physikalische E/A-Vorgänge zu erhöhtem Ressourcenverbrauch führen, verwendet der SQL Server einen Puffercache im Hauptspeicher. Wenn eine Abfrage Daten benötigt, wird zunächst versucht, die erforderlichen Daten aus dem Puffercache zu holen - ein sogenannter logischer Lesevorgang. Befinden sich die benötigten Daten nicht im Puffercache, so werden die entsprechenden Datenseiten von der Festplatte gelesen und in den Puffercache kopiert - ein sogenannter physikalischer Lesevorgang. Eine hohe DatencacheTrefferrate und ausreichend Arbeitsspeicher sind demnach Hauptindikatoren für eine gute Performance.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 3: Vorgang beim Lesen von Datenseiten

2.3.3 Schreiben von Datenseiten

Bei Datenänderungen, wie UPDATE, INSERT und DELETE, findet immer ein logischer Schreib vorgang statt. Hierbei werden die Daten aus Geschwindigkeitsgründen zunächst im Puffercache geändert und dort als modifiziert markiert. In regelmäßigen Abständen werden die geänderten Datenseiten dann über einen physikalischen Schreibvorgang auf die Festplatte übertragen.^[4] Für jeden logischen Schreibvorgang werden die Änderungen im Transaktionsprotokoll gespeichert. Erst wenn die Änderungen im Transaktionsprotokoll auf der Festplatte gesichert sind, können die modifizierten Seiten auf den Datenträger kopiert werden. Dies ist ein grundlegendes Prinzip von Transaktionen und ausschlaggebend für eine reibungslose Wiederherstellung der Datenbank. Dieser Vorgang wird allgemein als Write- Ahead-Transaction Logging bezeichnet.

Die folgende Abbildung 4 verdeutlicht die Funktionsweise bei Datenänderungen:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 4: Schreibvorgang bei Datenänderungen (Quelle: Eigene Darstellung in Anlehnung an [Micr11])

Ein Performancegewinn kann also erzielt werden, wenn die Transaktionsdaten von den eigentlichen Datenbankdaten getrennt werden, um die Schreib- und Lesezugriffe auf diese Dateien optimal zu verteilen. Für das Transaktionsprotokoll empfiehlt sich, aufgrund der vielen Schreibvorgänge, eine Festplatte mit einer hohen Schreibgeschwindigkeit zu verwenden.

2.4 Performanceüberwachung und Leistungsmessung

Dieses Kapitel basiert im Wesentlichen auf: [Asbrll], [Baud06], [Baud08], [Lore06], [LorelO], [Micr11], [MiSc04], [Pant10], [Schm07] und[Schm09].

Für die komplette Überwachung der SQL Server Umgebung, Leistungsmessung und Ursachenfindung von Performance-Engpässen stehen eine Vielzahl praktischer Programme, Werkzeuge und Funktionen zur Verfügung. Ergänzend sei hier unter anderem auf den Windows Task-Manager, den Windows Systemmonitor, den Windows Performance Monitor, den SQL Server Profiler, den Datenbankoptimierungsratgeber, die Verwaltung eines Management Data Warehouses, Ablaufverfolgungen sowie diverse kommerzielle Programme wie der Quest Software oder Redgate, hingewiesen.

Im Folgenden wird jedoch nur auf einige grundlegende Tools und Funktionen des Microsoft SQL Server Management Studio eingegangen.

SQL Server Management Studio

Das SQL Server Management Studio ist die zentrale Software zur Administration und Pflege des kompletten SQL Servers und soll nach und nach andere Werkzeuge und Tools zur Verwaltung oder Performancemessung des SQL Servers ablösen. Es bietet eine Vielzahl nützlicher Funktionen, um die Datenbank-Performance zu messen und den SQL Server zu überwachen.

2.4.1 Ausführungszeit einer SQL-Anweisung messen

Einen ersten Ansatz zur Performancekontrolle bietet die Zeitmessung einer SQL-Anweisung. Diese lässt sich relativ einfach über das SQL Server Management Studio auslesen. Dort befindet sich in der rechten unteren Ecke eine „Stoppuhr“, welche die Ausführungszeit einer durchgeführten SQL-Anweisung, sowie die Anzahl der Ergebniszeilen ausgibt.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 5: Zeitmessung mit dem SQL Server Management Studio

Die im SQL Server Management Studio integrierte Stoppuhr zeigt allerdings nur Zeitangaben in Stunden, Minuten und Sekunden an, weshalb eine genauere Messung mit Hilfe von Transact SQL (T-SQL) durchgeführt werden kann.

Hierfür genügt es, eine SQL-Anweisung mit folgendem T-SQL Befehl zu umschließen:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 4: Zeitmessung mit Hilfe von T-SQL

Die eigentliche SQL-Anweisung wird durchgeführt und anschließend die Ausführungszeit in Millisekunden ausgegeben. Um eine andere Zeiteinheit zu erhalten, können der DATEDIFF- Funktion auch andere Parameter übergeben werden. Hier sei insbesondere auf die DATEDIFF-Funktionsbeschreibung der Onlinedokumentation verwiesen.

2.4.2 Statistische Größen mittels T-SQL "SET-Anweisungen" ermitteln

Die Ausführungszeit einer SQL-Anweisung allein ist oftmals jedoch nicht aussagekräftig genug, sondern muss in Abhängigkeit von weiteren Parametern betrachtet werden. So spielt die Anzahl der Lesevorgänge, wie auch die Anzahl der ausgeführten Index- und Tabellenscans eine wesentliche Rolle für die Performanceoptimierung.

SET STATISTICS IO

Wenn die Option SET STATISTICS IO aktiviert ist, werden nach der Ausführung einer SQL-Anweisung im Meldungsfenster des SQL Management Studio detaillierte Informationen über die Datenträgeraktivitäten der beteiligten Tabellen ausgegeben. Die Meldung gibt unter anderem Aufschluss über:

Scananzahl - Anzahl der ausgeführten Index- und Tabellenscans

Logische Lesevorgänge - Anzahl der aus dem Cache gelesenen Datenseiten

Physische Lesevorgänge - Anzahl der von der Festplatte gelesenen Datenseiten

Read-Ahead-Lesevorgänge - Anzahl der neu im Cache hinterlegten Datenseiten

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 5: Ermittlung statistischer Größen mit Hilfe von SET STATISTICS IO

So liefert das vorangegangene Beispiel 5 folgende Ausgabe im Meldungsbereich:

DimProduct-Tabelle. Scananzahl 5, logische Lesevorgänge 481, physische Lesevorgänge 5, Read-Ahead-Lesevorgänge 160, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

FactOnlineSales-Tabelle. Scananzahl 5, logische Lesevorgänge 46664, physische Lesevorgänge 626, Read-Ahead-Lesevorgänge 46503, logische LOBLesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Bei mehrmaliger Ausführung einer Anweisung werden die physischen Lesevorgänge gegen 0 tendieren, da die erforderlichen Datenseiten bereits im Cache liegen und somit nur noch logische Lesevorgänge nötig sind. Um dies zu verhindern, kann man zur genaueren Analyse mit Hilfe der T-SQL Anweisung DBCC DROPCLEANBUFFERS den Puffercache vorher leeren.^[5]

SET STATISTICS TIME

Mit Hilfe der Option SET STATISTICS TIME lassen sich Informationen über die Analyse- und Kompilierungszeit (Erstellung des Ausführungsplans), sowie die eigentliche Ausführungszeit einer SQL-Anweisung ermitteln. Die einzelnen Zeitangaben werden dabei jeweils in „CPU-Zeit“ und „verstrichene Zeit“ angegeben.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 6: Ermittlung statistischer Größen mit Hilfe von SET STATISTICS TIME

Die Ausführung des vorangegangenen Skriptes erzeugt folgende Ausgabe:

SQL Server-Analyse- und Kompilierzeit:

, CPU-Zeit = 31 ms, verstrichene Zeit = 145 ms.

SQL Server-Ausführungszeiten:

, CPU-Zeit = 9062 ms, verstrichene Zeit = 10411 ms.

In diesem Beispiel belief sich die Erstellung des Ausführungsplans auf 145 Millisekunden. Die Gesamtausführungszeit der Anweisung lag bei 10.411 ms, wobei 9.062 ms von der CPU benötigt wurden.

Falls eine Abfrage auf mehreren CPUs ausgeführt wird, kann es vorkommen, dass die CPU- Zeit größer als die verstrichene Zeit ist, da in diesem Fall die einzelnen CPU-Zeiten addiert werden.

2.4.3 Grafische Ausführungspläne

Das SQL Server Management Studio ermöglicht die grafische Darstellung des Ausführungsplans einer SQL-Anweisung, was eine detaillierte Analyse erlaubt. Dabei kann zwischen der grafischen Darstellung des geschätzten (Estimated Execution Plan) und des tatsächlichen Ausführungsplans (Actual Execution Plan) gewählt werden.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 6: Aktivieren der Anzeige eines grafischen Ausführungsplans

Der geschätzte Ausführungsplan beruht jedoch lediglich auf den momentan verfügbaren Verteilungsstatistiken des Abfrageoptimierers. Beim tatsächlichen Plan wird im Gegensatz zum geschätzten die SQL-Anweisung auch real ausgeführt. Dies hat den entscheidenden Vorteil, dass die benutzten Statistiken bei der Ausführung noch aktualisiert werden können und somit keine falsche Annahmen (z.B. Anzahl der Datensätze, Zugriff auf Indizes) getroffen werden. Da der geschätzte Ausführungsplan aufgrund veralteter Statistiken teilweise deutlich vom tatsächlichen Plan abweichen kann, sollte er nur benutzt werden, um sehr zeitintensive Abfragen zu kontrollieren und somit eine ständige Ausführung zu umgehen.

Folgendes Beispiel dient zunächst zum Anzeigen des tatsächlichen Ausführungsplans:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 7: Einfache SQL-Abfrage

Im Meldungsfenster des SQL Server Management Studio wird nach der Ausführung des obigen Beispiels unter dem Reiter „Ausführungsplan“ folgender grafischer Plan dargestellt:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 7: Grafische Darstellung eines Ausführungsplans

Der Ausführungsplan entspricht der Reihenfolge der physikalischen Ausführung der SQL- Anweisung und wird von rechts nach links gelesen. Die einzelnen Symbole repräsentieren die Operationen. Die Verbindungspfeile stellen den Datenfluss zwischen den einzelnen Operationen dar, wobei die Dicke der Verbindungslinie die Anzahl, der an die nächste Operation übergebene Datenmenge symbolisiert. Wie bereits unter 2.2 erwähnt, sollten die Quellendaten möglichst früh, z.B. über eine WHERE-Klausel, eingegrenzt werden. Im grafischen Ausführungsplan weit links befindliche, dicke Linien, deuten auf Performanceengpässe hin und sollten genauer untersucht werden. Unter jeder Operation wird zusätzlich der prozentualen Kostenanteil der Operation, im Verhältnis zur gesamten SQL- Anweisung, angezeigt. Diese Operatorkosten können ebenfalls auf Performanceprobleme in einer Abfrage hinweisen und dabei helfen, zeitintensive Operationen zu optimieren.

Beim Überfahren einer Operation oder eines Verbindungspfeils mit der Maus werden zusätzliche Informationen angezeigt. Ein Verbindungspfeil gibt so beispielsweise Angaben über die Anzahl der übergebenen Zeilen („Tatsächliche Anzahl von Zeilen“), die geschätzte Anzahl von Zeilen, die geschätzte Zeilengröße, sowie die geschätzte Datengröße. Weicht die tatsächliche Anzahl von Zeilen deutlich von der geschätzten Anzahl von Zeilen ab, ist dies in der Regel ein Indiz für veraltete Statistiken.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 8: Verbindungspfeilinformationen im grafischen „tatsächlichen Ausführungsplan“

Eine Operation gibt unter anderem Auskunft über den physikalischen, sowie den logischen Vorgang, die Anzahl der übergebenen Zeilen, die geschätzten E/A-Kosten, die gesamten Operatorkosten sowie die Kosten aller vom Knoten rechts befindlichen Operationen („Geschätzte Unterstrukturkosten“).

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 9: Informationen einer „Nested Loop“ Operation im grafischen „tatsächlichen Ausführungsplan“

Durch die Analyse eines Ausführungsplans lassen sich also Performanceprobleme, wie beispielsweise zeitintensive Operationen, veraltete Statistiken oder nicht vorhandene Indizes aufspüren.

2.4.4 Der Aktivitätsmonitor

Der Aktivitätsmonitor ist ein praktisches Tool zur Leistungsüberwachung des SQL Servers und bietet eine Übersicht mit Informationen zu Prozessen der aktuellen Server-Instanz. Der Monitor lässt sich über die Symbolleiste des Microsoft SQL Server Management Studio oder dem Tastenkürzel [STRG] + [ALT] + [A] aufrufen. Er ist in die fünf Bereiche „Übersicht“, „Prozesse“, „Ressourcenwartevorgänge“, „Datendatei-E/A“ und „Aktuelle wertvolle Abfragen“ eingeteilt.

Der Bereich Übersicht bietet grafische Anzeigen - ähnlich dem Windows Taskmanger - zu „Prozessorzeit“, „Wartenden Tasks“, „Datenbank-E/A“ sowie „Batchanforderungen pro Sekunde“. Per Rechtsklick auf eines der Diagramme lässt sich das Aktualisierungsintervall je nach Bedarf zwischen einer Sekunde und einer Stunde einstellen.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 10: Aktivitätsmonitor - Übersicht

Im Bereich Prozesse werden alle aktiven Prozesse mit der jeweiligen „Sitzungs-ID“, „Anwendung“, „Wartezeit in Millisekunden“, „Blockiert von“, „Arbeitsspeicherverwendung“, etc. aufgelistet. Dieser Bereich ist besonders interessant, um Blockierungen aufzuspüren und zu kontrollieren.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 11: Aktivitätsmonitor - Prozesse

Der Reiter Ressourcenwartevorgänge gibt Auskunft über aktuelle und kumulierte Wartezeiten, sowie die Anzahl an Wartevorgängen auf bestimmte Ressourcen. Die einzelnen Ressourcen werden nach Kategorien gruppiert und können dabei helfen, Ressourcenengpässe zu identifizieren.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 12: Aktivitätsmonitor - Ressourcenwartevorgänge

Das Fenster Datendatei-E/A stellt die aktuellen E/A-Vorgänge je Datenbank dar. Die einzelnen E/A-Vorgänge sind in primäre und sekundäre Datendateien aufgeteilt. Sie geben Auskunft über die Antwortzeiten in Millisekunden, sowie die gelesenen und geschriebenen Daten in MB/Sekunden.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 13: Aktivitätsmonitor - Datendatei-E/A

Im letzten Bereich Aktuelle wertvolle Abfragen werden die SQL-Abfragen mit dem größten Ressourcenverbrauch angezeigt. Es werden für jede Abfrage die Anzahl der Ausführungen pro Minute, der CPU-Verbrauch in Millisekunden pro Sekunde, die Anzahl der physischen und logischen Lesevorgänge pro Sekunde, die Anzahl der logischen Schreib vorgänge pro Sekunde, sowie die durchschnittliche Laufzeit der Abfrage in Millisekunden dargestellt. Dadurch können sehr leicht kostenintensive Abfragen identifiziert werden, die anschließend genauer untersucht und optimiert werden können. Per Rechtsklick auf eine Abfrage lässt sich diese wahlweise in ein separates Abfragefenster kopieren oder der dazugehörige Ausführungsplan anzeigen. Sehr hohe Ausführungszeiten und viele physische Lesevorgänge sind Indikatoren für Performanceprobleme einer Abfrage.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 14: Aktivitätsmonitor - Aktuelle wertvolle Abfragen

2.4.5 Berichte

Die integrierte Reporting Funktion des Microsoft SQL Server Management Studio bietet eine weitere Möglichkeit der Leistungsüberwachung und Performancekontrolle. Mit Hilfe dieser Reporting Funktion können grafische und tabellarische Informationen der aktuellen Datenbank, wie auch Server-Instanz angezeigt werden. Für regelmäßige Analysen stehen eine Vielzahl von vordefinierten Standardberichten zur Verfügung, die durch benutzerdefinierte Berichte erweitert werden können. Standardberichte können über den Objekt Explorer per Rechtsklick auf einen Knoten ^ Berichte ^ Standardberichte geöffnet werden. Je nach Knoten hat man die Auswahl zwischen diversen Berichten zu allgemeinen Serverinformationen, Verwendung der Datenträger, Arbeitsspeichernutzung, Informationen zu Transaktionen oder detaillierten Leistungsinformationen.

Im Folgenden werden einige grundlegende Standardberichte beschrieben:

Serverdashboard

Das Serverdashboard bietet eine Gesamtübersicht der aktuellen Server-Instanz mit allgemeinen Konfigurationsinformationen, Aktivitätsdetails, CPU-Verwendung und den kumulierten Anteil der ausgeführten logischen E/A-Operationen. Dieser Bericht ermöglicht einen einfachen Einstieg und erste Ansatzpunkte zur weiterführenden Analyse von Performanceproblemen.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 15: Serverbericht: Serverdashboard

Arbeitsspeichern utzung

Die Arbeitsspeichernutzung stellt detaillierte Daten über die Verwendung des Arbeitsspeichers einzelner Komponenten, die Seitenverteilung im Puffercache, sowie Änderungen der Arbeitsspeichernutzung dar.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 16: Serverbericht: Arbeitsspeichernutzung

Aus Performancesicht ist vor allem die Pufferseitenverteilung von Bedeutung. Die Seitenverteilung wird untergliedert in folgende Bereiche:

Dirty - Als „Dirty“ werden die Bereiche im Puffercache bezeichnet, die per UDPATE, INSERT oder DELETE verändert und als modifiziert markiert, aber noch nicht auf der Festplatte gesichert wurden.

Free - Als „Free“ werden die Bereiche bezeichnet, die momentan nicht belegt sind und somit zum Speichern von Datenseiten verfügbar stehen.

Latched - Als „Latched“ werden Bereiche bezeichnet, die aktuell aufgrund von E/A- Operationen gesperrt sind. Ein hoher prozentualer Anteil an „Latched Pages“ ist ein Indikator für eine starke E/A-Belastung des Systems.

Stolen - Als „Stolen“ werden Bereiche des Caches bezeichnet, die an andere interne Prozesse abgegeben werden mussten. Dies geschieht vor allem bei Sortier- und Hashing-Operationen, die kurzfristig Arbeitsspeicher benötigen. Ein hoher prozentualer Anteil an „Stolen Pages“ ist Indikator für zu wenig Arbeitsspeicher.

Leistung - Batchausführungsstatistik

Der Bericht Leistung - Batchausführungsstatistik zeigt detaillierte Statistiken zu den ressourcenintensivsten SQL-Anweisungen an. Die einzelnen Anweisungen werden unterschieden nach „durchschnittlicher CPU-Zeit“, „durchschnittlichen logischen Lesevorgängen“, „durchschnittlichen logischen Schreibvorgängen“, „CPU-Zeit gesamt“ und „logischer E/A gesamt“. Dieser Bericht ermöglicht es, problematische SQL-Anweisungen aufzuspüren und schafft somit eine Grundlage für weiterführende Optimierungsmaßnahmen.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 17: Serverbericht: Leistung - Batchausführungsstatistik (Teil 1)

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 18: Serverbericht: Leistung - Batchausführungsstatistik (Teil 2)

Alle blockierenden Transaktionen

Dieser Bericht listet auf Datenbankebene alle Transaktionen auf, die andere Transaktionen blockieren und ermöglicht so das Aufspüren langfristiger Deadlocks.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 19: Datenbankbericht - Alle blockierenden Transaktionen

Statistik zur Indexverwendung

Die Statistik zur Indexverwendung zeigt eine Vielzahl an Details über die Verwendung der einzelnen Indizes in der Datenbank an und ist sehr hilfreich, um überflüssige Indizes zu finden und gegebenenfalls zu löschen.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 20: Datenbankbericht: Statistik zur Indexverwendung

Physische Statistik indizieren

Dieser Bericht bietet einen Überblick über die Indizes der Datenbank aus Sicht der Fragmentierung und ist sehr hilfreich, um Indizes regelmäßig zu überwachen (siehe auch 4.3). Besonders nützlich ist auch die Spalte „Empfohlener Vorgang“, die eine Neuerstellung oder Reorganisierung des entsprechenden Index vorschlägt.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 21: Datenbankbericht: Physische Statistik indizieren

Leistung - erste Abfragen nach durchschnittlicher CPU-Zeit

Mit Hilfe dieses Berichts lassen sich die durchschnittlich kostenintensivsten SQL- Anweisungen nach CPU-Zeit anzeigen. Abfragen, die deutlich zeitintensiver sind als andere, sollten genauer untersucht werden.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 22: Serverbericht: Leistung - erste Abfragen nach durchschnittlicher CPU-Zeit

3. Systemumgebung

Dieses Kapitel basiert im Wesentlichen auf: [HaLu01], [Micrll], [Pant10], [WaSaOl] und[Wood07].

Eine Hardwareaufrüstung führt in den wenigsten Fällen zu langfristigem Performancegewinn und sollte deshalb der letzte Schritt einer Optimierungsstrategie sein. Trotzdem bildet eine von Beginn an vernünftige, aufeinander angepasste Hard- und Software eine entsprechende Basis für spätere Optimierungsmaßnahmen.

3.1 Arbeitsspeicher

Da der SQL Server permanent auf einen Puffercache im Hauptspeicher zurückgreift, um Daten zu lesen und zu schreiben, bietet die Aufrüstung des Arbeitsspeichers die besten Chancen, um eine erkennbare Performancesteigerung zu erreichen. Je nach SQL Server Edition und installiertem Betriebssystem kann jedoch nur eine bestimmte Größe des Arbeitsspeichers verwendet werden. Standardmäßig reserviert sich der SQL Server Hauptspeicher und gibt diesen wieder frei, sobald andere Anwendungen zusätzlichen Speicher benötigen. Um nicht mit anderen Prozessen zu konkurrieren und dem Betriebssystem ausreichend Speicher zu überlassen, ist es daher ratsam, den verfügbaren Arbeitsspeicher für den SQL Server sinnvoll zu begrenzen.^[6]

3.2 Prozessor

Bei der Wahl eines geeigneten Hauptprozessors sollte vor allem auf die Taktfrequenz, die Größe des Prozessorcaches, die Anzahl der Prozessorkerne sowie die Bauart (32-Bit bzw. 64- Bit) geachtet werden.

Die Taktfrequenz der CPU ist ausschlaggebend für die Geschwindigkeit, mit der Operationen bearbeitet werden können. Aufgrund physikalischer Beschränkungen ist jedoch die Entwicklung höherer Taktfrequenzen kaum noch möglich, was die Entwicklung von Mehrkernprozessoren vorangetrieben hat.

Da der SQL Server seine Arbeit auf mehrere Prozessoren verteilen kann und Thread- Parallelisierung^[7] unterstützt, ist ein Mehrprozessorsystem sinnvoll und in Unternehmensumgebungen auch gängige Praxis.

Der interne Cache des Hauptprozessors wird benötigt, um Daten vom Arbeitsspeicher zwischenzuspeichern und somit ein Warten des Prozessors auf den vergleichsweise langsamen Hauptspeicher zu verhindern. Da sich der prozessorinterne Cache also direkt auf die Performance des Datenbanksystems auswirkt, sollte ein Prozessor mit möglichst großem Cache gewählt werden.

Ein weiterer wichtiger Aspekt ist die Bauart des Prozessors. So ist es möglich mit einem 64- Bit Prozessor insgesamt 16 EB Speicher zu adressieren. Im Gegensatz dazu kann ein 32-Bit Prozessor lediglich 4 GB adressieren.

Um den Prozessor optimal nutzen zu können, muss auch hier auf das installierte Betriebssystem des Servers und die eingesetzte SQL Server Edition geachtet werden.

3.3 Festplatten

Festplatten spielen eine essentielle Rolle in einem Datenbanksystem, da auf ihnen die eigentlichen Daten hinterlegt sind und von dort gelesen bzw. bei Änderungen wieder zurückgeschrieben werden müssen. Generell ist eine physikalische Trennung der Daten- und Transaktionsprotokolldateien auf unterschiedlichen Festplatten empfehlenswert, um ein paralleles Lesen und Schreiben der Daten zu ermöglichen. Auch die eigentlichen Lese- und Schreibgeschwindigkeiten der Festplatten sind von großer Bedeutung für die Gesamtperformance.

3.3.1 Festplattenarten

SATA

SATA-Festplatten sind für den Einsatz in einem Datenbankserver eher ungeeignet. Sowohl die Festplattenumdrehungen, wie auch die Zugriffszeit der Platten, sind nach wie vor SCSI/SAS-Festplatten unterlegen. Auch der Duplex-Betrieb (Parallele Lese- und Schreiboperationen) ist mit SATA-Festplatten im Gegensatz zu SCSI/SAS-Festplatten nicht möglich.

SCSI/SAS

SCSI- bzw. SAS-Festplatten sind für den Dauerbetrieb ausgelegt und deshalb besonders für den Einsatz in Serverumgebungen geeignet. Datenbanken sind generell sehr E/A-lastig. Schnelle E/A-Operationen wirken sich also positiv auf die Datenbankperformance aus und ermöglichen einen möglichst effizienten Datenbankbetrieb. Diese E/A-Performance übersteigt bei SCSI/SAS-Festplatten die von herkömmlichen SATA-Festplatten teilweise um das Vierfache.

SSD

Die erfolgreichsten Aussichten auf spürbaren Performancegewinn beim Lesen von Daten bieten SSD-Festplatten (Solid-State-Drive). Diese Platten bestehen nicht wie eine herkömmliche Festplatte aus vielen mechanischen Bauteilen, sondern aus einzelnen Flashspeicher-Bausteinen, wodurch extrem geringe Zugriffszeiten ermöglicht werden. Die Transferraten beim Schreiben sind jedoch in der Regel langsamer als bei einer normalen Festplatte. Es ist also empfehlenswert für Datendateien eine SSD-Festplatte und für Transaktionsprotokolle eine klassische HDD-Festplatte zu verwenden.

Das folgende Beispiel 8 verdeutlicht den Geschwindigkeitsunterschied bei Leseoperationen einer SSD-Platte im Vergleich zu einer herkömmlichen HDD-Festplatte:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 8: SQL-Abfrage zum Geschwindigkeitsvergleich einer SSD-Festplatte mit einer herkömmlichen HDD-Festplatte

Während die Abfrage mit einer SSD-Festplatte lediglich 2.612 Millisekunden benötigte, dauerte die Abfrage mit einer herkömmlichen HDD-Festplatte 10.147 Millisekunden. Dies entspricht einer Geschwindigkeitsdifferenz vom Faktor 3,8 und macht den gravierenden Leistungsunterschied dieser Festplattenarten offensichtlich^[8].

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 23: Vergleich der Abfragezeit zwischen einer SSD-Festplatte und einer HDD-Festplatte

Die beiden größten Nachteile der SSD-Festplatten liegen im momentan noch sehr hohen Preis und der, im Vergleich zu herkömmlichen Festplatten, relativ kurzen Lebensdauer. Erfahrungsgemäß fallen die Preise in der Computerindustrie sehr schnell, wodurch die Preise in wenigen Jahren das Niveau normaler HDD-Festplatten erreichen dürften. Die kurze Lebensdauer ist jedoch gerade für eine Serverumgebung das größere Manko und der Einsatz sollte wohl überlegt sein.

3.3.2 RAID-Systeme

Um das Serversystem möglichst performant und ausfallsicher zu gestalten, werden in der Regel RAID-Systeme (Redundant Array of Independent Disks) eingesetzt. RAID-Systeme stellen ein Verbund mehrerer Festplatten zu einem logischen Laufwerk dar. Dies ermöglicht, je nach RAID Level, eine höhere Datensicherheit bzw. schnellere Lese- und Schreibzugriffe. Die einzelnen RAID-Stufen werden in sogenannte Level eingeordnet. Im Folgenden eine Übersicht der gängigsten RAID Level.

RAID 0

Bei einem RAID 0 werden die Daten in mehrere gleichgroße Blöcke unterteilt. Diese Blöcke werden in logischer Reihenfolge auf die einzelnen Festplatten verteilt. Ein RAID 0 steigert die Lese- und Schreibleistung, da die Daten gleichzeitig von mehreren Festplatten gelesen

bzw. gleichzeitig auf mehrere Festplatten geschrieben werden können. Die Sicherheit der Daten sinkt jedoch bei einem RAID 0. Fällt eine Festplatte aus, sind alle Daten verloren, da die einzelnen Festplatten nur einen Teil des Gesamtverbundes darstellen.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 24: Aufbau eines RAID 0

RAID 1

Ein RAID 1 erstellt eine Komplettspiegelung der gesamten Daten einer Festplatte auf eine andere. Dies erhöht die Lesegeschwindigkeit, da die Daten von zwei Festplatten gleichzeitig gelesen werden können. Allerdings wird die Schreibgeschwindigkeit reduziert, da sämtliche Daten parallel gespeichert werden müssen. Fällt jedoch eine Festplatte aus, so sind die kompletten Daten immer noch auf der zweiten Festplatte vorhanden.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 25: Aufbau eines RAID 1

RAID 5

Bei einem RAID 5 werden die Daten auf allen Festplatten gleichmäßig verteilt. Zusätzlich werden Paritätsinformationen gespeichert, die eine Wiederherstellung der Daten im Falle eines Festplattendefekts ermöglichen. Die Lesegeschwindigkeit bei einem RAID 5 steigt, da wie bei einem RAID 0 die Daten von mehreren Platten gleichzeitig gelesen werden können. Allerdings wird die Schreibgeschwindigkeit aufgrund des zweiphasigen Schreibvorgangs^[9] reduziert.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 26: Aufbau eines RAID 5

RAID 10

Ein RAID 10 setzt sich aus mehreren RAID 1-Systemen zusammen, die wiederum ein RAID 0 darstellen. Ein RAID 10 besitzt somit die Vorteile eines RAID 0 und eines RAID 1. Die Datensicherheit wird durch die Spiegelung der Daten von einem RAID 1 auf das andere RAID 1 gewährleistet. Die Lese- und Schreibleistung wird durch das RAID 0 gesteigert.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 27: Aufbau eines RAID 10

Für einen SQL Server empfiehlt sich folgende Kombination der unterschiedlichen RAIDStufen 0, 1, 5 und 10:

RAID 1: Aufgrund der maximalen Datensicherheit und kompletter Redundanz der Daten ist für die Installation des Betriebssystem und des eigentlichen SQL Servers der Einsatz eines RAID 1 geeignet.

RAID 10: Die eigentlichen Datenbankdateien sowie die Protokolldateien sollten aufgrund der hohen Lese- und Schreibgeschwindigkeiten, sowie einer maximalen Datensicherheit in einem RAID 10 hinterlegt werden.

RAID 5: Für Backups der Datenbank- und Protokolldateien empfiehlt sich ein RAID 5. Das Speichervolumen der einzelnen Platten kann so optimal ausgenutzt werden und durch Paritätsinformationen ist eine einfache Wiederherstellung der Daten gewährleistet.

RAID 0: Aufgrund der maximalen Lese- und Schreibperformance empfiehlt sich der Einsatz eines RAID 0 für die temporäre Datenbank und die Auslagerungsdateien.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 28: Möglicher Aufbau eines RAID-Systems für den SQL Server

3.4 Netzwerk

Ein oftmals unterschätzter Punkt von Performanceengpässen ist die Netzwerkumgebung eines Datenbanksystems. Jede Datenbankanwendung greift über die Netzwerkschnittstelle des Servers auf die Datenbank zu. Es liegt auf der Hand, dass eine schnelle Netzwerkkarte die Gesamtperformance positiv beeinflussen wird. Des Weiteren sollte die Netzwerkkarte im sogenannten Vollduplex-Modus arbeiten. Mit dieser Einstellung wird es ermöglicht zwischen dem Switch und der Netzwerkkarte Daten gleichzeitig in beide Richtungen zu senden und zu empfangen. Ist die Einstellung nicht gewählt, oder unterstützt die Netzwerkkarte des Servers keinen Vollduplex-Modus, so kann immer nur eine Station Daten senden. Auch die Verwendung mehrerer Netzwerkkarten kann gegebenenfalls einen Performancegewinn erwirken. Es sollte aber nicht nur die Netzwerkkarte des Servers in Betracht gezogen, sondern die gesamte Netzwerkumgebung untersucht werden. So ist es beispielsweise zwecklos, eine 1- Gbit-Netzwerkkarte im Server zu verwenden, wenn diese mit einem Switch verbunden ist, der lediglich 100-Mbit übertragen kann.

3.5 Betriebssystem

Bei der Wahl des Betriebssystems sollte in erster Linie auf die Bauart des Prozessors (32-Bit bzw. 64-Bit), sowie auf die ausgewählte SQL Server Edition geachtet werden, um den installierten Arbeitsspeicher und die Anzahl der Prozessorkerne möglichst effizient nutzen zu können.

3.6 Datenbankmanagementsystem

Um ein optimales Zusammenspiel zwischen Betriebssystem, Arbeitsspeicher und Prozessor zu erreichen, muss auch eine passende SQL Server Edition gewählt werden. Der SQL Server 2008 ist in insgesamt sieben verschiedenen Editionen verfügbar, die sich vor allem in der Nutzung der CPU, dem maximal nutzbaren Arbeitsspeicher und zahlreichen unterstützten Funktionen unterscheiden. So ist beispielsweise eine Online-Indizierung oder Partitionierung nur mit der Enterprise Edition möglich.

Die folgende Tabelle 1 zeigt die Hardwarebeschränkungen der einzelnen SQL Server Editionen. Die jeweils verfügbaren Funktionen, insbesondere der Hochverfügbarkeits- und Performance-Features, der unterschiedlichen Editionen lassen sich in der Onlinedokumentation einsehen.

Abbildung in dieser Leseprobe nicht enthalten

Tabelle 1: Vergleich der verschiedenen SQL Server 2008 Editionen

4. Indizes

Dieses Kapitel basiert im Wesentlichen auf: [Baud08], [Kali04], [Kali05b], [Kali06], [Lang01], [Lore07], [Micr11], [Pant10], [Schm07] und[Schm09].

4.1 Grundlagen von Indizes

Indizes sind elementare Bestandteile jeder performanten Datenbank und können Such- und Sortiervorgänge drastisch beschleunigen. Ein Index ist eine besondere Datenstruktur, die in der Form [Schlüsselwert, Adresse] aufgebaut ist und sortierte Verweise auf die eigentlichen Datensätze einer Tabelle enthält. Durch diese Indexsortierung können speziell optimierte Suchalgorithmen verwendet werden. Der SQL Server speichert Indizes als B-Baum.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 29: Indexorganisation in Form eines B-Baums

Die oberste Ebene (Root page) stellt die Wurzel des Index dar und enthält für jeden Indexwert Verweise auf die Knotenebene (Intermediate Level). Ein Knoten referenziert dann weiter auf die nächste Knotenebene bzw. die entsprechende Blattseite (Leaf Level). Dieser hierarchische Aufbau ermöglicht eine effiziente Suche von Datensätzen.

Indizes benötigen nur geringen Speicherplatz, wodurch deutlich mehr Informationen zeitgleich in den Hauptspeicher übertragen werden können. Häufige Leseoperationen vom Datenträger werden dadurch reduziert.

Besonders bei Abfragen die ausschließlich Spalten enthalten die auch im Index vorkommen kann das Datenbankmanagementsystem komplett auf einen Tabellenzugriff verzichten, da alle relevanten Informationen bereits im Index vorhanden sind.

Der Einsatz eines Index empfiehlt sich insbesondere bei Spalten, die häufig in JOIN- Operationen oder als Filterbedingung in einer Abfrage verwendet werden. Auch wenn eine

Spalte sehr viele unterschiedliche Werte enthält, ist ein Index sinnvoll, da durch die hohe Selektivität sehr schnell auf die entsprechenden Daten zugegriffen werden kann.

Es gilt jedoch immer zu prüfen, ob mit einem Index die erhoffte Performancesteigerung erreicht wird, da bei Änderungen der Tabellendaten immer auch die Indizes aktuell gehalten werden müssen. INSERT, UPDATE oder DELETE Operationen werden demnach in der Regel mehr Zeit benötigen. Des Weiteren bedürfen Indizes einer regelmäßigen Wartung (siehe Kapitel 4.3). Gerade bei Tabellen, deren Daten sehr häufig geändert werden, ist der Einsatz eines Index genauer abzuwägen.

Der SQL Server speichert Tabellen in einzelnen 8KB großen Datenseiten, die entweder in Form eines HEAP oder als Tabellen mit einem gruppiertem Index organisiert sind.

4.2 Verwendung von Indizes

4.2.1 HEAP - Tabelle ohne gruppierten Index

Als HEAP bezeichnet man eine Tabelle ohne einen gruppierten Index. Ein HEAP besteht aus separaten Datenblöcken, die jeweils 8 Datenseiten enthalten. Ein Datenblock ist dementsprechend 64KB groß. Die einzelnen Datenseiten wiederum sind aufgeteilt in einen 96Byte großen Header und einen 8.060Byte großen Body, der die Datenzeilen enthält. In einer HEAP-Tabelle liegen die einzelnen Datenzeilen, wie auch die Datenseiten in unsortierter Reihenfolge und ohne jegliche Beziehung zueinander auf dem Datenträger. Neue Datenzeilen werden immer dort eingefügt, wo gerade Platz zur Verfügung steht. Beim Löschen einer Datenzeile entsteht neuer Speicherplatz, der später wieder aufgefüllt werden kann. Um Daten in einem HEAP zu finden, verwaltet der SQL Server eine sogenannte Index Allocation Map (IAM). Diese Zuordnungstabelle stellt Verweise zu den einzelnen Seiten des HEAPs bereit. Da die Daten in einer HEAP-Tabelle nicht sortiert sind, muss bei einem Suchvorgang stets der gesamte HEAP gelesen werden - ein sogenannter „Table Scan“. Aus diesem Grund ist die Verwendung eines HEAP nur sinnvoll, wenn die Tabelle sehr wenige Datensätze enthält.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 30: Aufbau eines HEAP

Zu weiteren Performanceuntersuchungen der einzelnen Indizierungsmöglichkeiten des SQL Servers wird zunächst eine neue Tabelle „IndexTest‘ erstellt, mit Beispieldaten gefüllt und im Folgenden darauf aufgebaut:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 9: Erzeugung einer Testtabelle zu Performanceuntersuchungen der unterschiedlichen Indizierungsmöglichkeiten

Das Skript erzeugt eine Tabelle mit fiktiven Personendaten und enthält für jede Person den Vornamen, Nachnamen sowie eine eindeutige ID.

Beispiel 10 dient als Grundlage für die folgenden Performancemessungen und gibt alle Daten zur Person mit der „id‘ 207754 aus.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 10: SQL-Abfrage zur Performancemessung der unterschiedlichen Indizierungsmöglichkeiten

Da für die Tabelle „IndexTest‘ noch kein Index angelegt wurde, sind die Tabellendaten momentan in einem HEAP organisiert. Nach Ausführung der obigen Abfrage lässt sich im tatsächlichen Ausführungsplan erkennen, dass ein Table Scan erfolgte, um die gesuchten Daten zu filtern.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 31: Ausführungsplan bei der Suche in einem HEAP

Dieser Table Scan beanspruchte das System mit folgenden Lastkennzahlen:

Abbildung in dieser Leseprobe nicht enthalten

Es ist offensichtlich, dass der Table Scan sehr hohe E/A-Kosten und viele Lesevorgänge verursacht hat.

Die Anzahl der Lesevorgänge lässt sich auch relativ einfach rechnerisch ermitteln. Die Tabelle „IndexTest‘ besteht aus einem Integer-Wert, der 4Byte einer Datenseite beansprucht, sowie aus jeweils zwei 100 Zeichen langen nchar-Werten, die gemeinsam 400Byte Speicherplatz benötigen. Der Body einer Datenseite hat, wie bereits erwähnt, 8.060Byte Speicherplatz zur Verfügung. Entsprechend können pro Datenseite 8.060Byte/404Byte = 19 Tabellenzeilen in einer Datenseite gespeichert werden. Die Tabelle „IndexTest‘ enthält derzeit 251.218 Datenzeilen, weshalb insgesamt 13.222 Lesevorgänge (251.218/19) für die Abfrage nötig sind.

4.2.2 Gruppierte Indizes (Clustered Index)

Bei einem gruppierten Index sind die Daten in sortierter Reihenfolge des Indexschlüssels gespeichert. Sowohl die einzelnen Blattseiten, wie auch die Knoten sind mit einer doppelten Liste verknüpft, um die Fortbewegung im Indexbaum zu beschleunigen. Die eigentlichen Tabellendaten sind auf der Blattebene des gruppierten Index selbst gespeichert, weshalb auch nur ein gruppierter Index pro Tabelle erzeugt werden kann - der gruppierte Index ist also die Tabelle.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 32: Gruppierter Index auf der Spalte „id“ der Tabelle „IndexTest“

Die roten Pfeile symbolisieren die Suche nach der „id‘ 207754. Die Indexwurzel verweist zunächst auf die Datei 1 mit der Seitennummer 20019. Dieser Knoten wiederum referenziert auf die Blattebene der Datei 1 mit Seitennummer 10935, auf welcher sich die eigentlichen Tabellendaten befinden.

Beim Einfügen neuer Daten kann es vorkommen, dass die bisherigen Datensätze verschoben werden, um die Sortierung gemäß des Index zu gewährleisten. Dies vermindert entsprechend die Leistung bei Einfüge-Operationen.

Um einen eindeutigen, gruppierten Index auf die Spalte „id‘ der soeben angelegten Tabelle „IndexTest‘ zu erzeugen ist folgende Anweisung erforderlich:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 11: Anlegen eines eindeutigen, gruppierten Index auf der Spalte „id“ der Tabelle „IndexTest“

Anschließend wird erneut die Abfrage Beispiel 10 zur Performancemessung ausgeführt. Der tatsächliche Ausführungsplan verdeutlicht, dass die Suche der Daten nun über eine Indexsuche im gruppierten Index „IX ID‘ erfolgte.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 33: Ausführungsplan bei der Suche über einen gruppierten Index

Die Abfrage über die Indexsuche im gruppierten Index verursachte die nachfolgenden Kosten:

E/A-Kosten: 0,003125

CPU-Kosten: 0,0001581

Logische Lesevorgänge: 3

Physische Lesevorgänge: 2

Durch die Verwendung des Index sind sowohl die E/A-Kosten, wie auch die Anzahl der erforderlichen Lesevorgänge drastisch gesunken. Anstatt 13.222 Lesevorgängen bei der Suche in einem HEAP benötigte das System nun über die Suche im gruppierten Index lediglich 3 Lesevorgänge - eine Leistungsverbesserung um den Faktor 4407!

Um die Suche nach Daten zu beschleunigen, sollte für jede Tabelle, abgesehen von wenigen Ausnahmen, ein gruppierter Index angelegt werden. Da für jede Tabelle nur ein gruppierter Index erzeugt werden kann, sollte die Wahl geeigneter Spalten möglichst sorgfältig getroffen werden. Hierbei spielen die auf die Tabelle zugreifenden Abfragen eine wesentliche Rolle und sollten in die Überlegungen mit einbezogen werden. Die nachfolgenden Kriterien unterstützen die Wahl geeigneter Spalten:

Die Spalte(n) weist/weisen einen hohen Grad der Eindeutigkeit auf.

Die Spalte(n) ermöglicht/ermöglichen sinnvolle Bereichsabfragen mit BETWEEN, < und > und/oder eignet/eignen sich für Aggregatabfragen mit MIN, MAX, AVG oder

COUNT.

Die Spalte(n) wird/werden häufig als JOIN-, Sortier- oder Gruppierungskriterium verwendet.

Die Spalte(n) wird/werden, wenn überhaupt, nur sehr selten verändert.

Die Spalte(n) enthält/enthalten stetig steigende, eindeutige Werte.

Die Spalte(n) wird/werden nicht bereits in einem nicht gruppierten Index verwendet.

4.2.3 Nicht gruppierte Indizes (Nonclustered Index)

Der Aufbau der Indexstruktur eines nicht gruppierten Index ist identisch mit der des gruppierten Index. Allerdings sind die eigentlichen Tabellendaten vollständig vom nicht gruppierten Index getrennt. Diese werden entweder in einem HEAP oder in einem gruppierten Index gespeichert und über Verweise in den Blattseiten des nicht gruppierten Index erreicht. Aus diesem Grund benötigt der nicht gruppierte Index in der Regel weniger Datenseiten und wird weniger Stufen im B-Baum aufweisen, als ein entsprechender, gruppierter Index. Für eine Tabelle können insgesamt 249 nicht gruppierte Indizes angelegt werden.

- Auf einem Heap

Bei einem nicht gruppierten Index auf einem HEAP enthalten die Blattseiten Verweise auf die entsprechenden Positionen der Tabellendaten im HEAP in der Form [Datei- ID:Seitennummer:Position im Block],

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 34: Nicht gruppierter Index auf einem HEAP (Index auf der Spalte „id“ der Tabelle „IndexTest“)

Die roten Linien verdeutlichen erneut die Suche nach der „id‘ 207754. Die ersten beiden Schritte sind identisch zur Suche im gruppierten Index. Das Abrufen der Daten erfolgt nun aber nicht mehr in der Blattebene selbst, sondern über Zeiger in den Blattseiten, die auf die entsprechende Position im HEAP referenzieren. Das Holen der Daten aus dem HEAP wird als RID-Lookup (RowID-Lookup) bezeichnet.

Für die Leistungsmessung wird zunächst der soeben erstellte gruppierte Index „IX_ID‘‘ gelöscht, ein neuer nicht gruppierter Index auf der Spalte „id‘ erzeugt und anschließend die Abfrage Beispiel 10 ausgeführt.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 12: Anlegen eines eindeutigen, nicht gruppierten Index auf der Spalte „id“ der Tabelle „IndexTest“

Über den Ausführungsplan wird deutlich, dass die Suche über eine Indexsuche im nicht gruppierten Index „IX_ID2‘ durchgeführt und die Daten über einen RID-Lookup im HEAP geholt wurden.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 35: Ausführungsplan bei einem nicht gruppierten Index auf einem HEAP

Die gesamte Abfrage über den nicht gruppierten Index verursachte folgende Lastkennzahlen:

Abbildung in dieser Leseprobe nicht enthalten

Die Anzahl der benötigten Lesevorgänge haben sich im Vergleich zur Suche über den gruppierten Index nicht verändert. Dies liegt in erster Linie an der geringeren Indextiefe des nicht gruppierten Index. Allerdings haben sich sowohl die E/A-Kosten, wie auch die CPU- Kosten aufgrund des erforderlichen RID-Lookup verdoppelt.

- Auf einem gruppierten Index

Bei einem nicht gruppierten Index auf einem gruppierten Index sind in den Blattseiten des nicht gruppierten Index die Schlüsselwerte des gruppierten Index gespeichert, weshalb die eigentliche Suche auch erst im gruppierten Index erfolgt.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 36: Nicht gruppierter Index auf einem gruppierten Index (Index auf den Spalten „vorname, nachname“ der Tabelle „IndexTest“)

In Abbildung 36 wird abermals die Suche nach der „id“ 207754 erkennbar. Über den nicht gruppierten Index wird zunächst in den Blattseiten der Schlüsselwert des gruppierten Index ermittelt. Anschließend wird mit Hilfe des Schlüsselwertes die Indexsuche im gruppierten Index durchgeführt. Diese entspricht der „normalen“ Suche im gruppierten Index.

Für die Performanceuntersuchung wird der zuvor erstellte nicht gruppierte Index „IXID2“ wieder gelöscht und im Anschluss ein gruppierter Index „IX_ID“ auf der Spalte „id‘ sowie ein nicht gruppierter Index über die Spalte „vorname“, „nachname“ erzeugt.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 13: Anlegen eines eindeutigen, nicht gruppierten Index auf den Spalten „vorname, nachname“ der Tabelle „IndexTest“

Die zur Performancemessung erforderliche Abfrage wird entsprechend abgeändert und ausgeführt:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 14: Abgeänderte SQL-Abfrage zu Performancemessungen der unterschiedlichen Indizierungsarten

Der tatsächliche Ausführungsplan verdeutlicht auch hier, dass die Suche über eine Indexsuche im nicht gruppierten Index „IX VORN NACHN“ erfolgte. In diesem Fall ist das Holen der Daten über einen RID-Lookup nicht notwendig, da die Daten über den gruppierten Index „IX ID2“ abgerufen werden können.

Die Indexsuche über den nicht gruppierten Index ergab folgende Kosten:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 37: Ausführungsplan bei einem nicht gruppierten Index auf einem gruppierten Index

Die Indexsuche über den nicht gruppierten Index ergab folgende Kosten:

Abbildung in dieser Leseprobe nicht enthalten

Die verursachten E/A-Kosten sowie die erforderlichen CPU-Kosten haben sich im Vergleich zum gruppierten Index nicht verändert. Da für jeden im nicht gruppierten Index ermittelten Schlüsselwert anschließend die entsprechenden Datenseiten im gruppierten Index gelesen werden müssen, haben sich die Lesevorgänge jedoch entsprechend erhöht.

4.2.4 Eindeutige Indizes (Unique Indexes)

Bei einem eindeutigen Index dürfen mehrere Datensätze nicht den identischen Schlüsselwert besitzen. Der Primärschlüssel einer Tabelle ist somit auch immer ein eindeutiger Index, da er jeden Datensatz eindeutig identifiziert. Wurde ein eindeutiger Index angelegt, verhindert das Datenbankmanagementsystem das Hinzufügen doppelter Schlüsselwerte. Die Indexleistung kann durch die Verwendung eines eindeutigen Index deutlich erhöht werden, da eine Suche abgebrochen werden kann, sobald der gesuchte Wert gefunden wurde.^[10]

Wird ein gruppierter Index nicht als UNIQUE erstellt, wird intern ein zusätzlicher Schlüssel angelegt um den Index eindeutig identifizieren zu können. Nach Möglichkeit sollte man einen gruppierten Index immer mit der Option UNIQUE erstellen, da sämtliche nicht gruppierten Indizes dieser Tabelle neu erstellt werden müssten, sobald der gruppierte Index neu erstellt oder gelöscht wird.

4.2.5 Gefilterte Indizes (Filtered Indexes)

Mit dem SQL Server 2008 wurden die sogenannten gefilterten Indizes eingeführt. Ein gefilterter Index ist ein nicht gruppierter Index, der nur eine bestimmte Teilmenge der Tabelle enthält, die einer Filterbedingung entspricht. Die Abfrageleistung kann durch gefilterte Indizes spürbar verbessert werden, da diese weniger Speicherplatz benötigen und auf gefilterte und somit genauere Statistiken zurückgreifen können (vgl. Kapitel 5.4).

Der Einsatz eines gefilterten Index lohnt sich insbesondere bei regelmäßigen Abfragen, die eine bestimmte Teilmenge beinhalten und in der die indizierte Spalte sehr viele gleiche Werte enthält (z.B. NULL).

Ein gefilterter Index sollte aber immer sehr bedacht eingesetzt werden und setzt genaueste Kenntnisse über die vorhandene Datenbankstruktur, sowie das zu erwartende Abfrageergebnis voraus. In den meisten Fällen ist ein normaler, nicht gruppierter Index daher nützlicher.

Angenommen, in der Tabelle „IndexTest‘ wird sehr häufig nach Personen mit dem Vornamen „Daniel‘ gesucht, so könnte mit einem gefilterten Index in diesem Fall spürbarer Performancegewinn erreicht werden.

Um dies zu untersuchen werden zunächst alle zuvor erstellten Indizes mit folgender Anweisung gelöscht:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 15: Löschen der Indizes für weitere Performanceuntersuchungen

Für die Tabelle „IndexTest‘ existiert nun also kein Index mehr und die Tabellendaten sind wie zu Beginn in einem HEAP organisiert.

Die folgende Abfrage soll alle Personen mit dem Vornamen Daniel ausgeben.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 16: SQL-Abfrage zu Performancemessungen der unterschiedlichen Indizierungsarten

Die obige Abfrage ergab folgende Lastkennzahlen:

Abbildung in dieser Leseprobe nicht enthalten

Wie bereits erwartet sind die E/A-Kosten sowie die erforderlichen Lesevorgänge erneut sehr hoch, da sämtliche Tabellendaten über einen Table Scan aus dem HEAP geholt wurden.

Nun wird ein nicht gruppierter Index mit der Filterbedingung WHERE vorname = 'Daniel' auf der Spalte „vorname“ erzeugt:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 17: Anlegen eines nicht gruppierten Index auf der Spalte „vorname“ der Tabelle „IndexTest“

Beispiel 16 wird erneut ausgeführt und verursachte dabei die folgenden Lastkennzahlen:

Abbildung in dieser Leseprobe nicht enthalten

Durch die Abfrage über den gefilterten Index haben sich sowohl die E/A-Kosten, die CPU- Kosten, wie auch die Anzahl der erforderlichen Lesevorgänge spürbar gesenkt. Dies liegt zum einen an den bereits vorgefilterten Statistiken, wie auch der Indexstruktur, welche deutlich weniger Ebenen als ein vergleichbarer, gruppierter, bzw. nicht gruppierter Index benötigt.

Das vorangegangene Beispiel würde im Produktiveinsatz natürlich keinen Sinn ergeben. Ein nicht gruppierter Index wäre an dieser Stelle vollkommen ausreichend und zweckmäßig. Der gefilterte Index würde in diesem Fall lediglich bewirken, dass andere Abfragen den Index nicht verwenden können und somit ein Table Scan erforderlich wäre. In einigen speziellen Anwendungsfällen kann durch den gezielten Einsatz eines gefilterten Index jedoch die erwünschte Verbesserung der Abfrageperformance erzielt werden.

4.2.6 Kombinierte Indizes (Combined Indexes)

Ein kombinierter Index, auch zusammengesetzter Index genannt, ist im Grunde genommen ein „normaler“ Index, der lediglich mehrere Spalten abdeckt. Ein kombinierter Index kann aus insgesamt 16 Spalten bestehen. Allerdings sollte darauf geachtet werden, den zusammengesetzten Index so klein wie möglich zu halten, da umso mehr E/A-Operationen verursacht werden, je mehr Spalten der Index umfasst. Besonders gilt es auch die Reihenfolge der indizierten Spalten im Index zu beachten. Diese spielt eine essentielle Rolle, da der Index unter Umständen vom Optimierer nicht verwendet wird, wenn die Selektivität der ersten angegebenen Spalte zu gering ist. Als Faustregel gilt: Die Spalten, die am häufigsten in WHERE-Bedingungen enthalten sind, sollten auch im Index als Erstes angegeben werden.

Ein kombinierter Index macht in der Regel Sinn, wenn mehrere Spalten gleichzeitig durchsucht werden sollen. Oftmals ist es jedoch ratsamer das Datenbankdesign entsprechend anzupassen, um einen zusammengesetzten Index zu vermeiden.

4.2.7 Abdeckende Indizes (Covering Indexes)

Als abdeckende Indizes bezeichnet man Indizes, die sämtliche benötigten Spalten einer Abfrage enthalten. Ein abdeckender Index wird die Abfrageleistung steigern, da kein RID- Lookup mehr erforderlich ist und sich alle Zeilendaten bereits im Index selbst befinden.

Eine Möglichkeit abdeckende Indizes indirekt zu schaffen besteht darin, zusätzlich benötigte Spalten mit der INCLUDE-Option im Index mitzuspeichern. Diese Spalten sind dann auch im Index selbst enthalten, der Index wird jedoch nicht anhand dieser Spalten sortiert. Auch ein Umsortieren des Indexbaums entfällt bei Änderungen in den inkludierten Spalten.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 18: Anlegen eines abdeckenden Index

Die Ausführung des Beispiel 16 beanspruchte das System mit folgenden Lastkennzahlen:

Abbildung in dieser Leseprobe nicht enthalten

Im Vergleich zur vorherigen Suche über den gefilterten Index hat sich die Anzahl der erforderlichen Lesevorgänge drastisch gesenkt, da für das Holen der Tabellendaten kein RID- Lookup mehr erforderlich ist.

4.3 Verwaltung von Indizes

Damit Indizes vom SQL Server optimal genutzt werden können, ist eine Wartung vorhandener, die Eliminierung überflüssiger, sowie die Erstellung fehlender Indizes erforderlich.

Bei häufigen Änderungen der Tabellendaten (INSERT's, UPDATE's und DELETE's) steigt zunehmend die Fragmentierung^[11] der Indizes. Einerseits entstehen Lücken in den Blattseiten durch DELETE's, andererseits werden neue Daten hinzugefügt und Tabellendaten geändert, wodurch eine Umstrukturierung des Indexbaums notwendig wird. Diese Umsortierung wird unter anderem durch Seitenteilungen erreicht, was sowohl Zeit, als auch Systemressourcen in Anspruch nimmt und unter Umständen Fragmentierungen verursacht, die letztendlich wieder zu erhöhten E/A-Vorgängen führen.

Um einen hohen Fragmentierungsgrad zu verhindern, oder betroffene Indizes wieder zu defragmentieren, stellt der SQL Server unterschiedliche Methoden zur Verfügung.

4.3.1 Der Füllfaktor

Der Füllfaktor ist ein prozentualer Wert, der sowohl beim Erstellen, wie auch bei der Neuerstellung eines Index angegeben werden kann. Er legt dabei den Anteil fest, zu dem die Blattseiten des Index mit Daten gefüllt werden sollen und kann somit potentielle Seitenteilungen reduzieren. Der freie Speicherplatz steht für neue Daten zur Verfügung und wird zwischen den einzelnen Indexzeilen reserviert. Hierbei ist jedoch zu beachten, dass ein geringerer Füllfaktor auf einer Indexschlüsselspalte mit fortlaufenden Werten (z.B. IDENTITY-Spalte) keinen Sinn macht, da sämtliche neuen Einträge am Ende eingefügt werden. Der freie Speicherplatz in den Datenseiten kann deshalb nicht aufgefüllt werden. Es wird unnötig Speicherplatz verschwendet, was kostenintensive E/A-Vorgänge nach sich zieht.

Richtig angewendet, wird ein geringer Füllfaktor Platz für zusätzliche Daten schaffen und eine potentielle Umsortierung des Index folglich verhindern. Allerdings steigt der Speicherplatz, den der Index benötigt, und die Anzahl der Lesevorgänge bei Abfragen wird sich entsprechend erhöhen.

Ein Index mit einem hohen Füllfaktor wird bei Abfragen weniger Lesevorgänge benötigen. Bei vielen Tabellenänderungen werden jedoch schnell Seitenteilungen nötig, der B-Baum immer unausgeglichener und der Fragmentierungsgrad entsprechend hoch. Ein zu hoher Fragmentierungsgrad bewirkt vermehrte E/A-Operationen, da der Lese-/Schreibkopf der Festplatte öfter neupositioniert werden muss. Unter Umständen wird der SQL Server einen Index mit zu hohem Fragmentierungsgrad auch gar nicht mehr verwenden, was dann zu erheblichen Performanceeinbußen führt.

Als Faustregel lässt sich also feststellen: Bei vielen Änderungen der Tabellendaten kann es hilfreich sein, einen geringen Füllfaktor anzugeben. Werden die Daten hauptsächlich gelesen, ist ein hoher Füllfaktor empfehlenswert.

Das nachfolgende Beispiel 19 zeigt die erforderliche SQL-Syntax bei der Neuerstellung eines Index mit dem Füllfaktor von 80%.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 19: Erstellung eines nicht gruppierten Index mit dem Füllfaktor von 80%

Damit der Füllfaktor nicht nur für die Blattseiten des Index, sondern auch für die Knotenebenen verwendet wird kann zusätzlich die PAD_INDEX-Option angegeben werden:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 20: Erstellung eines nicht gruppierten Index mit dem Füllfaktor von 80% und der Option PAD_INDEX

Bei zunehmenden Fragmentierungsgrad sollten die betroffenen Indizes reorganisiert oder neu erstellt werden.

4.3.2 Indizes reorganisieren

Die Reorganisierung eines Index wird von Microsoft bei einem Fragmentierungsgrad zwischen 5% und 30% empfohlen und kann über die ALTER INDEX-Anweisung mit der REORGANIZE-Klausel veranlasst werden. Dabei werden die Blattseiten des Index gemäß der Indexsortierung umorganisiert, was nur geringe Ressourcen beansprucht und komplett online erfolgt. Dies bedeutet, dass der Index während der Reorganisierung weiterhin verwendet werden kann. Die Neuanordnung der Blattseiten verbessert die Leistung beim Durchsuchen der Indizes erheblich, schafft weiteren Speicherplatz durch eine Komprimierung der einzelnen Datenseiten und entfernt leere Seiten.

Die Reorganisierung eines Index kann mit folgender SQL-Anweisung angestoßen werden:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 21: Reorganisierung des Index „IX_ID“ der Tabelle „IndexTest“

4.3.3 Indizes neu erstellen

Microsoft empfiehlt das Neuerstellen eines Index ab einem Fragmentierungsgrad von 30%. Die Neuerstellung kann über die ALTER INDEX-Anweisung mit der REBUILD-Klausel oder über den CREATE INDEX-Befehl mit der DROP_EXISTING-Klausel erfolgen. Dabei wird der Index zunächst gelöscht und anschließend neu aufgebaut. Bei der Neuerstellung werden die vorhandenen Datenseiten komprimiert, die einzelnen Indexzeilen neu angeordnet und die Fragmentierung entfernt. Aus diesem Grund wird die Anzahl der erforderlichen Lesevorgänge sinken, was zu einem spürbaren Performancegewinn führt.

Während der Neuerstellung kann der Index nicht benutzt werden. Allerdings kann, wie bei der Reorganisierung, auch die Neuerstellung eines Index online vorgenommen werden. Hierfür muss die Option WITH (ONLINE = ON) angegeben werden. Dabei wird zunächst eine Kopie des Index angelegt. Diese Index-Kopie wird dann neu erstellt und der ursprüngliche Index anschließend gelöscht. Die Online-Indizierung benötigt jedoch deutlich mehr Ressourcen und aufgrund der Index-Kopie auch kurzfristig zusätzlichen Speicherplatz.

Ein Index kann mit folgender SQL-Anweisung neu erstellt werden:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 22: Online-Neuerstellung des Index „IX_ID“ der Tabelle „IndexTest“

Eine Untersuchung der Indizes über die unter Kapitel 2.4.5 vorgestellten Berichtsfunktionen kann unter Umständen sehr aufwendig werden. Folgendes Beispiel-Skript kann daher hilfreich sein, um stark fragmentierte Indizes möglichst effizient aufzuspüren:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 23: Skript zum Aufspüren stark fragmentierter Indizes

Das Skript greift hierbei auf Systemtabellen und -sichten zurück und ermittelt dabei unter anderem die Indextiefe, die Seitenanzahl, den Fragmentierungsgrad in Prozent und gibt zusätzlich die empfohlene Defragmentierungs-Methode aus. Abbildung 38 zeigt das entsprechende Ergebnis.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 38: Übersicht der Indizes, die einer Defragmentierung unterzogen werden sollten (vor einer Defragmentierung)

Um die vom Skript vorgeschlagenen Indizes zu defragmentieren wird der jeweils empfohlene Vorgang angewendet:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 24: Indizes reorganisieren und neu erstellen

Um den Erfolg der Defragmentierung festzustellen wird erneut das Skript aus Beispiel 23 ausgeführt, was zu folgendem Ergebnis führt:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 39: Übersicht der Indizes, die einer Defragmentierung unterzogen werden sollten (nach einer Defragmentierung)

Aus Abbildung 39 wird ersichtlich, dass immer noch für fünf der sieben Indizes ein relativ hoher Fragmentierungsgrad besteht, was sowohl auf die minimale Seitenanzahl wie auch die geringe Indextiefe zurückzuführen ist. Bei kleinen Indizes ist die Fragmentierung meist nicht steuerbar, weshalb eine Reorganisierung oder Neuerstellung keinen Erfolg zeigt. Man könnte das Skript entsprechend abändern und weiter nach der Indextiefe und der Seitenanzahl filtern. Eine pauschale Angabe über geeignete Werte lässt sich hierbei nicht machen. Diese Angaben sollten auf eigenen Erfahrungswerten und entsprechendem Know-how mit der vorliegenden Datenbank beruhen.

Die Indexwartung sollte je nach Datenbank, Datenbankanwendung und Einsatzgebiet eine tägliche oder wöchentliche Wartungsaufgabe darstellen. Um diesen administrativen Aufwand geringer zu gestalten, könnte ein einfaches, ab gewandeltes T-SQL Skript wie das Nachfolgende dienen:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 25: Skript zur Automatisierung der Indexdefragmentierung

Dieses Skript baut auf das Vorangegangene auf und speichert zusätzlich die einzelnen vorgeschlagenen Defragmentierungsanweisungen in der Variable @kommando, welche am Ende des Skripts über den EXEC-Befehl ausgeführt werden und die Defragmentierung in Gang setzt.

Um eine komplett automatische Defragmentierung zu erreichen, könnte das Skript als Wartungsaufgabe über einen entsprechenden Wartungsplan im SQL Server Agent angelegt werden.^[12]

4.3.4 Fehlende Indizes aufspüren

Wie in den vorangegangenen Kapiteln deutlich geworden ist, beschleunigen Indizes Datenbankabfragen um ein Vielfaches. Nicht vorhandene Indizes können daher enorme negative Auswirkungen auf die Abfrageleistung haben. Um fehlende Indizes aufzuspüren stehen mehrere Möglichkeiten zur Verfügung.

Zum einen stellt der Abfrageplan interessante Informationen über fehlende Indizes bereit, zum anderen speichert der SQL Server zahlreiche Informationen zu fehlenden Indizes in seinen Systemtabellen.

Ausführungsplan

Während der Optimierer eine Abfrage analysiert, werden Informationen über fehlende Indizes erzeugt, welche die Abfrageleistung steigern könnten. Im Ausführungsplan werden diese nützlichen Informationen dann als Empfehlungen angezeigt.

Folgendes Beispiel soll dies verdeutlichen. Zunächst werden alle existierenden Indizes der Tabelle „IndexTest" gelöscht, was mit folgendem Skript erledigt wird:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 26: Alle vorhandenen Indizes der Tabelle „IndexTest“ löschen

Anschließend wird die Ausgabe des tatsächlichen Ausführungsplans aktiviert und das Beispiel 10 ausgeführt, um alle Informationen zur Person mit der „id" 207754 zu erhalten.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 40: Hinweise zu einem fehlenden Index im Ausführungsplan

Im Ausführungsplan wird nun ein Hinweis angezeigt, dass ein fehlender Index die Leistung für diese Abfrage steigern könnte. Besonders interessant ist zudem die Angabe über die prozentuale Auswirkung, die ein entsprechender Index auf diese Abfrage bewirkt. Auch der fertige SQL Befehl zur Erzeugung des Index wird bereits angegeben.

Dynamische Management-Sichten

Eine weitere Möglichkeit fehlende Indizes ausfindig zu machen bieten die dynamischen Management-Sichten (DMV = Dynamic Management Views) des SQL Servers. Diese Sichten ermöglichen einen einfachen Zugriff auf interne Statistiken und Daten des SQL Servers. In diesen werden unter anderem sämtliche Informationen zu fehlenden Indizes

protokolliert, die mit Hilfe von SQL Skripten wie dem Nachfolgendem ausgelesen werden können^[13]

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 27: Fehlende Indizes über dynamische Management-Sichten aufspüren

Die Abfrage ermittelt die entsprechenden Tabellen, die Anzahl der Suchen, die Anzahl der Scans, die empfohlenen Indexspalten, nicht empfohlene Indexspalten, Spalten, die für einen INCLUDE in Frage kommen, sowie die prozentuale Auswirkung auf die entsprechende Abfrage. ^[14] Das Ergebnis der obigen Abfrage liefert genauere Angaben über fehlende Indizes wie in Abbildung 41 gezeigt.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 41: Ermittlung fehlender Indizes über dynamische Management-Sichten

In diesem Fall wird ein Index für die Tabelle „IndexTest‘ auf der Spalte „id‘ vorgeschlagen. Dieser Index könnte eine Abfragesteigerung von 99,68% bewirken.

Die vom Optimierer erzeugten Informationen zu fehlenden Indizes sollten jedoch nicht unüberlegt umgesetzt, sondern einer gewissen Kontrolle unterzogen werden. Die vorgestellten Methoden zur Analyse fehlender Indizes beziehen sich immer nur auf konkrete und bereits durchgeführte Abfragen. Unter Umständen sind die Empfehlungen für die restliche Anwendung nicht von Vorteil, sondern sogar nachteilig.

Zur weiteren Überprüfung können sowohl der Aktivitätsmonitor, wie auch die vorgestellten Berichtsfunktionen herangezogen werden.

4.3.5 Überflüssige Indizes

Überflüssige Indizes sollten verhindert werden, da diese bei UPDATE, INSERT und DELETE-Operationen ebenfalls aktualisiert werden müssen und unnötig Speicherplatz verbrauchen. Folglich werden nicht verwendete Indizes die E/A-Last erhöhen und die Gesamtperformance negativ beeinflussen.

Ein Index gilt als überflüssig, sobald dieser über einen längeren Zeitraum nicht mehr benutzt wurde. Wie fehlende Indizes lassen sich auch überflüssige Indizes über dynamische Management-Sichten ermitteln.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 28: Überflüssige Indizes über dynamische Management-Sichten ermitteln

In obiger Abfrage werden alle Indizes ermittelt, die seit dem letzten Serverstart für keinen Index Seek, Index Scan oder Index Lookup verwendet wurden. Abbildung 42 zeigt einen Auszug des Resultats der obigen SQL Anweisung.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 42: Auflistung möglicher überflüssiger Indizes

Aber auch hier gilt es, nicht unüberlegt alle vorgeschlagenen Indizes zu löschen, sondern diese zunächst genauer zu überprüfen. Denn ein überflüssiger Index wird die Performance bei weitem nicht so negativ beeinflussen wie ein fehlender Index.

Eine weitere Möglichkeit der Identifizierung überflüssiger Indizes besteht in der bereits vorgestellten Berichtsfunktion zur Indexverwendung.

5. Statistiken

Dieses Kapitel basiert im Wesentlichen auf: [Baud08], [Micrll], [Pant10], [Schm09], [SchmlO] und [WaSa01].

Statistiken enthalten Informationen über die Verteilung unterschiedlicher Werte in einer oder mehreren Tabellenspalten. Sie beruhen auf Stichproben der Originaldaten, um die Datenmenge für Kardinalitätsschätzungen zu reduzieren und die Optimierungsphase zu beschleunigen. Der Abfrageoptimierer verwendet sie, um den effizientesten Ausführungsplan für eine SQL Anweisung zu ermitteln. Dabei nimmt er unter anderem Kardinalitätsschätzungen über die Anzahl der Zeilen im jeweiligen Abfrageergebnis vor. Diese Schätzungen sind ausschlaggebend für die Wahl der physikalischen JOIN-Operationen, der Suchoperationen, des angeforderten Arbeitsspeichers, sowie der Verwendung von Indizes und haben enorme Auswirkungen auf die Performance einer Abfrage.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 43: Die Rolle von Statistiken in der Optimierungsphase (Quelle: Eigene Darstellung in Anlehnung an [Schm10])

5.1 Statistiken anzeigen

Zum Anzeigen einzelner Statistiken und Statistikinformationen stehen mehrere Möglichkeiten zur Verfügung. Statistiken lassen sich über den Objekt-Explorer der jeweiligen Tabelle unter dem Punkt „Statistik“ finden (Abbildung 44). Ein Doppelklick auf eine entsprechende Statistik öffnet ein neues Fenster mit Detailinformationen (Abbildung 45).

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 44: Auflistung der vorhandenen Statistiken der Tabelle „FactOnlineSales“ im Objekt-Explorer

Die Seite „Allgemein“ zeigt allgemeine Informationen wie den Tabellennamen, den Statistiknamen, die einzelnen Statistikspalten, sowie das Datum der letzten Statistikaktualisierung an. Zudem ist eine Option zur sofortigen Aktualisierung der Statistik gegeben.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 45: Statistikeigenschaften - Allgemeiner Bereich

Auf der Seite „Details“ lassen sich genauere Informationen der jeweiligen Statistik einsehen

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 46: Auszug der Statistikeigenschaften - Detailbereich

Diese Übersicht ist in drei Bereiche aufgegliedert:

Der erste Bereich zeigt generelle Informationen über die Anzahl der Zeilen in der Tabelle bzw. im Index, die Anzahl der verwendeten Zeilen für die Stichprobe, sowie das Datum der letzten Statistikaktualisierung.

Der zweite Bereich gibt Auskunft über die Verteilungsdichte der einzelnen Werte, die durchschnittliche Länge der Werte und die einzelnen Spalten der Statistik.

Der dritte Bereich stellt Histogrammdaten in Form einer Tabelle dar und ist in folgende fünf Spalten aufgeteilt:^[15]

Abbildung in dieser Leseprobe nicht enthalten

Tabelle 2: Angezeigte Spalten im Statistikhistogramm

Aufgrund von Darstellungsproblemen im Microsoft SQL Server Management Studio lassen sich über den Objekt-Explorer nur Detailinformationen zu Indexstatistiken anzeigen. Bei Tabellenstatistiken erscheint in der Detailübersicht lediglich der Hinweis „Es sind keine Statistikinformationen verfügbar“. Diese Meldung ist aber nicht korrekt. Die entsprechenden Informationen lassen sich sowohl für Tabellenstatistiken, wie auch für Indexstatistiken über folgenden DBCC-Befehl anzeigen:^[16]

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 29: Abfragen der Statistikdetails über einen DBCC-Befehl

Die erzeugte Übersicht ist hierbei in die gleichen drei Bereiche „Allgemeine Informationen“, „Verteilungsinformationen“ und „Histogrammdaten“ aufgeteilt und enthält daher dieselben Informationen wie beim Aufruf über den Objekt-Explorer.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 47: Auszug der Statistikdetails über einen DBCC-Befehl

Sehr hilfreich kann unter Umständen auch die integrierte Exportfunktion sein. Über [Rechtsklick] ^ [Ergebnisse speichern unter] lassen sich die Histogrammdaten in eine CSV- Datei exportieren und anschließend beispielsweise mit Excel weiterverarbeiten, um eine grafische Auswertung der Verteilungswerte zu erhalten.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 48: Grafische Auswertung der Verteilungswerte mit Hilfe von Excel

5.2 Statistiken anlegen

Bei der Erzeugung von Statistiken muss zwischen Spaltenstatistiken (bzw. Tabellenstatistiken) und Indexstatistiken unterschieden werden. Indexstatistiken werden automatisch angelegt, sobald ein neuer Index erzeugt wird. Spaltenstatistiken hingegen werden erst erstellt, wenn eine Abfrage ausgeführt wird und dabei Spalten ohne Indizes in einem Prädikat verwendet. Die Voraussetzung hierfür ist jedoch, dass die Option für die automatische Erstellung aktiviert ist. Aus Performancesicht sollte die automatische Erstellung nicht deaktiviert werden, da der Abfrageoptimierer während der Planerstellung sonst fehlende Statistiken nicht mehr selbständig erstellt und Spaltenstatistiken dann manuell erzeugt werden müssen. Auf die automatische Erstellung von Statistiken beim Anlegen eines Index hat diese Option allerdings keine Auswirkung.

Automatisch erstellte Tabellenstatistiken lassen sich bereits am Namen erkennen, der mit „_WA_Sys_“ beginnt. Automatisch generierte Indexstatistiken tragen den gleichen Namen wie der zugrundeliegende Index.

Die Problematik automatisch erstellter Tabellenstatistiken liegt darin, dass diese immer nur für eine einzelne Spalte der Tabelle angelegt werden und die Stichprobenqualität nicht beeinflussbar ist. Um mehrspaltige Statistiken anzulegen oder eine Statistik manuell zu erzeugen, kann eine entsprechende SQL-Anweisung mit folgender Syntax dienen:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 30: Manuelle Erstellung einer Statistik

Um die Qualität der Stichproben zu beeinflussen kann diese SQL-Anweisung mit den nachfolgenden Argumenten versehen werden:

Abbildung in dieser Leseprobe nicht enthalten^[17]

Tabelle 3: Argumente um die Repräsentativität der Statistik-Stichproben zu beeinflussen

Allerdings lassen sich mit dieser Methode nur einzelne Statistiken für eine Tabelle erstellen. Aus diesem Grund existiert die Systemprozedur „sp createstats“. Mit Hilfe dieser Prozedur lassen sich Spaltenstatistiken für alle Spalten, die nicht bereits der ersten Spalte einer vorhandenen Statistik entsprechen, anlegen. Dies wird zum einen dazu führen, dass der Abfrageoptimierer keine fehlenden Statistiken mehr erzeugen muss. Zum anderen können genauere Kardinalitätsschätzungen vorgenommen werden. Somit werden längere Antwortzeiten beim Client verhindert und die Abfrageleistung gesteigert. Die Prozedur generiert jedoch Statistiken für sämtliche Spalten aller Tabellen der Datenbank, wovon ein Großteil vermutlich nie benötigt wird. Microsoft zufolge ist eine Anwendung dieser Systemprozedur in den meisten Fällen nicht erforderlich.

Zusammenfassend lässt sich festhalten, dass die manuelle Erzeugung einzelner Statistiken die Performance verbessern kann. Insbesondere, um mehrspaltige Statistiken anzulegen, die Anzahl der für die Stichprobe verwendeten Zeilen zu beeinflussen oder eine ressourcenintensive Erstellung während der Abfrageausführung zu vermeiden, kann die manuelle Statistikerstellung hilfreich sein. Automatisch erstellte Statistiken werden in der Regel jedoch ausreichend sein.

5.3 Statistiken aktualisieren

Entscheidend für optimale Annahmen des Optimierers sind sowohl die Aktualität der Statistiken, wie auch die Repräsentativität der Statistikinformationen.

Um Statistiken aktuell zu halten stehen mehrere Varianten zur Verfügung. Die erste Methode besteht in der synchronen Aktualisierung von Statistiken. Hierbei stößt der Optimierer die 18 Aktualisierung veralteter^[18] Tabellen- und Indexstatistiken an, bevor der entsprechende Ausführungsplan erstellt wird. Erst nach der Aktualisierung der veralteten Statistiken wird der Abfrageplan erstellt und die SQL-Anweisung ausgeführt. Der Nachteil der synchronen Aktualisierung liegt darin, dass die Aktualisierung der Statistiken je nach Umfang der Daten eine gewisse Zeit in Anspruch nimmt. Aus diesem Grund kann sich die Ausführung der eigentlichen SQL-Anweisung deutlich verzögern und teilweise enorme Antwortzeiten und Timeouts beim Client hervorrufen.

Eine weitere Möglichkeit bietet die asynchrone Aktualisierung von Statistiken. Im Gegensatz zur synchronen Aktualisierung erstellt der Optimierer erst den Abfrageplan auf Basis der „veralteten“ Statistiken und veranlasst im Hintergrund die Aktualisierung. Die SQL- Anweisung wird folglich sofort ausgeführt. Die asynchrone Aktualisierung ist allerdings eine Zusatzoption und kann daher nur verwendet werden, wenn die automatische Aktualisierung generell aktiviert ist. Diese Methode ist empfehlenswert, wenn die Statistikaktualisierung sehr viel Zeit in Anspruch nimmt und die Abfrage dadurch deutlich verzögert ausgeführt wird. Potentiell lange Wartezeiten beim Client entfallen somit.

Die dritte Möglichkeit besteht in einer manuellen Aktualisierung der Statistiken. Eine manuelle Aktualisierung kann wahlweise über den Objekt-Explorer (siehe 5.1) oder über entsprechende SQL-Anweisungen vorgenommen werden.

Um die Statistiken einer gesamten Tabelle manuell zu aktualisieren, ist eine SQL-Anweisung mit folgender Syntax erforderlich:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 31: Manuelle Aktualisierung aller Statistiken einer Tabelle

Wie bei der Statistikerstellung kann auch die Statistikaktualisierung mit den bereits vorgestellten Argumenten ergänzt werden, um die Repräsentativität der Stichproben zu beeinflussen.

Die manuelle Aktualisierung benötigt immer Angaben über die Tabelle, sowie die zu aktualisierende Statistik. Um alle veralteten Statistiken der gesamten Datenbank zu aktualisieren, liefert Microsoft eine weitere Systemprozedur mit dem Namen „sp updatestats“. Die Ausführung dieser Prozedur kann mit folgender SQL-Anweisung angestoßen werden:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 32: Manuelle Aktualisierung aller veralteter Statistiken über die Systemprozedur „sp_updatestats“

Oftmals wird eine manuelle Statistikaktualisierung über diese Systemprozedur in periodischen Abständen mit Hilfe eines Wartungsauftrags im SQL Server Agent erledigt. Dabei wird meistens jedoch nicht beachtet, dass die Aktualisierung nur Stichproben der Tabellendaten verwendet. Insbesondere bei zuvor durchgeführten Neuindizierungen ist dies problematisch, da bei der Neuerstellung eines Index auch die entsprechenden Statistiken auf Basis aller Daten aktualisiert werden. Dies kann dazu führen, dass die sehr genauen Statistiken durch schlechtere ersetzt werden.

Auf die automatische Aktualisierung sollte niemals verzichtet werden, allerdings kann die manuelle Aktualisierung mit geeigneten Wartungsintervallen diese ergänzen, um den Produktivbetrieb nicht zu stark zu beeinträchtigen, die automatische Aktualisierung zu entlasten und die Qualität der Stichproben zu beeinflussen.

Welche negative Auswirkung veraltete Statistiken haben können, soll folgendes Beispiel verdeutlichen:

Zunächst wird ein nicht gruppierter Index auf der Spalte „OnHandQuantity“ der „FactInventory“-Tabelle angelegt, da diese Spalte als Suchkriterium dienen soll.

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 33: Erzeugen eines nicht gruppierten Index

Durch die Erstellung des Index wird automatisch eine entsprechende Indexstatistik angelegt. Für diesen fiktiven Fall könnten die Lager aufgrund einer Firmenübernahme deutlich aufgefüllt werden, weshalb die Datenbank entsprechend aktualisiert werden muss:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 34: Aktualisierung der Inventar-Tabelle

Da von der Änderung weniger als 20% der Datenzeilen betroffen sind, wird die nachfolgende Abfrage auf Basis veralteter Statistiken durchgeführt:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 35: Abfragen aller Inventardaten mit über 5.500 vorrätigen Produkten

Bei der Betrachtung des zugehörigen Ausführungsplans fällt auf, dass die tatsächliche Anzahl von Zeilen drastisch von der geschätzten Anzahl von Zeilen abweicht. Die geschätzte Anzahl von Zeilen veranlasst den Abfrageoptimierer dazu, einen Index Seek durchzuführen, der für diese Abfrage nicht die optimale Lösung darstellt.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 49: Abfrageplan bei einer veralteten Statistik

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 36: Manuelle Aktualisierung der veralteten Indexstatistik

Bei erneuter Ausführung der obigen Abfrage wird im Ausführungsplan ersichtlich, dass der Optimierer nun aufgrund der aktuelleren Statistiken genauere Schätzungen vornehmen kann:

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 50: Ausführungsplan auf Basis aktuellerer Indexstatistiken

5.4 Gefilterte Statistiken

Durch gefilterte Statistiken kann die Qualität der Statistiken für Abfragen, die nur eine bestimmte Teilmenge der Daten enthalten, verbessert werden. Dabei muss zwischen gefilterten Statistiken durch gefilterte Indizes und manuell erstellten gefilterten Statistiken unterschieden werden.

Gefilterte Statistiken durch gefilterte Indizes

Wie bei „normalen“ Indizes werden auch für gefilterte Indizes automatisch Indexstatistiken angelegt. Aufgrund der bereits vorgefilterten Zeilen sind diese Statistiken aber genauer als herkömmliche Index- oder Tabellenstatistiken. Der Abfrageoptimierer kann daher für Abfragen, die einen gefilterten Index verwenden, auf diese präziseren Statistiken zurückgreifen, was zu einem verbesserten Ausführungsplan führt.

Manuell erstellte gefilterte Statistiken

Neben den gefilterten Statistiken durch gefilterte Indizes können im SQL Server 2008 auch gefilterte Statistiken manuell angelegt werden. Eine manuelle Erzeugung gefilterter Statistiken kann über die CREATE S TAT IS TICS-Anweisung mit der WHERE-Klausel erfolgen. Das nachfolgende praktische Beispiel soll dies verdeutlichen.

Um bereits seit längerem im Lager befindliche Ware in den Onlineshop aufzunehmen und die Lager zu entlasten, sollen alle Produkte in den Warenlagern mit Onlineshopanbindung ermittelt werden, die seit mindestens 90 Tagen auf Lager liegen. In der Contoso-Datenbank gibt es drei Geschäfte die online verkaufen (StoreKey 199, 306 und 307). Um die relevanten Produkte zu ermitteln wird die nachfolgende Abfrage verwendet:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 37: Ermitteln aller seit mindestens 90 Tagen auf Lager befindlichen Produkte der Stores mit Onlineshop

Bei einem Blick auf die Operatorinformationen des Clustered Index Scan im tatsächlichen Ausführungsplan fällt auf, dass die geschätzte Anzahl von Zeilen deutlich von der tatsächlichen abweicht. Der Abfrageoptimierer erwartet als Ergebnis ca. 27.139 Produkte. Die tatsächliche Anzahl liegt jedoch nur bei 17.375. Es liegt also auf der Hand, dass die automatisch erstellten Statistiken für diese Abfrage nicht optimal sind.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 51: Falsche „Geschätzte Anzahl von Zeilen“

Um die Qualität der Statistiken zu erhöhen, könnte in diesem Fall eine gefilterte Statistik weiterhelfen. Es wird also je eine gefilterte Statistik auf die Spalte „DaysInStock“ für alle

Geschäfte mit Onlineshop und eine für alle Geschäfte ohne Onlineshop mit folgender Anweisung angelegt:

Abbildung in dieser Leseprobe nicht enthalten

Beispiel 38: Anlegen von gefilterten Statistiken für die Spalte „DaysInStock“

Wird die gleiche Abfrage erneut ausgeführt, ist über die Operatorinformationen im Ausführungsplan ersichtlich, dass der Optimierer nun aufgrund der gefilterten Statistiken deutlich bessere Annahmen treffen kann. Die geschätzte Anzahl von Zeilen ist der tatsächlichen Anzahl von Zeilen annähernd gleich.

Abbildung in dieser Leseprobe nicht enthalten

Abbildung 52: „Geschätzte Anzahl von Zeilen“ mit gefilterten Statistiken

Durch maßgeschneiderte, gefilterte Statistiken können also Abfragen profitieren, die WHERE- Klauseln auf nicht indizierten Spalten anwenden oder aus korrelierenden Spalten auswählen.

[...]

---

^[1] Downloadlink: http://www.microsoft.com/downloads/de-de/details.aspx?FamilyID=868662dc-187a-4a85-b611- b7df7dc909fc

^[2] Downloadlink: http://www.microsoft.com/downloads/de-de/details.aspx?FamilyID=265f08bc-1874-4c81-83d8-0d48dbce6297

^[3] Eine genauere Beschreibung der physikalischen JOIN-Operationen ist in Kapitel 7.3.3 zu finden.

^[4] Für das Schreiben von modifizierten Datenseiten auf die Festplatte verwendet der SQL Server die drei Methoden „Verzögertes Schreiben", „Eager-Writing" und „Prüfpunktprozess", die hier aufgrund fehlender Bedeutung nicht näher beschrieben werden.

^[5] In Produktivsystemen sollte dieser Befehl nur bedingt eingesetzt werden, da das Leeren des Puffercaches enorme Auswirkungen auf die Gesamtperformance hat. Neben der im Puffercache befindlichen Daten werden auch die Ausführungspläne gelöscht!

^[6] Der maximale Serverarbeitsspeicher lässt sich unter anderem im Microsoft SQL Server Management Studio über den Objekt-Explorer mit Rechtsklick auf den Server ^ Eigenschaften ^ Arbeitsspeicher ^ Maximaler Serverarbeitsspeicher (in MB) festlegen.

^[7] Eine Abfrage wird in mehreren Threads gleichzeitig bearbeitet.

^[8] Aufgrund unterschiedlicher Hardwarekomponenten der beiden Testsysteme sollten diese Messwerte nur als Näherungswerte betrachtet werden. Beide Systeme unterscheiden sich neben der verwendeten Festplatte insbesondere auch durch den Prozessor und den Arbeitsspeicher. Die genauen Umgebungsbedingungen sind im Anhang angegeben.

^[9] Schreiben der eigentlichen Daten und Schreiben der Paritätsinformationen.

^[10] Die Suche kann selbstverständlich nur abgebrochen werden, wenn die WHERE-Bedingung den entsprechenden eindeutigen Schlüsselwert enthält. Bei einer Suche in anderen Spalten ist dies nicht möglich.

^[11] „Fragmentierung liegt vor, wenn Indizes über Seiten verfügen, in denen die logische Reihenfolge (basierend auf dem Schlüsselwert) nicht der physikalischen Reihenfolge in der Datendatei entspricht." (Micr11)

^[12] Für die genaue Funktionsweise und Handhabung der Wartungsaufgaben, insbesondere des SQL Server Agent sei auf die Onlinedokumentation verwiesen.

^[13] Die Informationen zu fehlenden Indizes werden nicht dauerhaft gespeichert, sondern beim Neustart des SQL Servers gelöscht. Um aussagekräftige Informationen zu erhalten, sollte der SQL Server daher mehrere Monate laufen.

^[14] Die Abfrage kann je nach Bedarf sehr leicht um weitere nützliche Informationen wie dem Datum der letzten Suche, der gesamten verursachten Kosten, etc. erweitert werden.

^[15] Bei mehrspaltigen Statistiken sind die Histogrammdaten nur für die führende Spalte angegeben.

^[16] Als Übergabeparameter werden der Tabellenname sowie der Name der Statistik erwartet.

^[17] „Der tatsächliche Prozentsatz oder die tatsächliche Anzahl von Zeilen, die vom Abfrageoptimierer als Stichprobe entnommen werden, stimmt möglicherweise nicht mit dem angegebenen Prozentsatz oder der angegebenen Anzahl überein. Der Abfrageoptimierer scannt z. B. alle Zeilen auf einer Datenseite. “ (Micr11)

^[18] Statistiken gelten als veraltet, wenn sich mehr als 20% der Daten, aber mindestens 500 Zeilen, seit der letzten Statistikaktualisierung verändert haben.