Hinweise

Die vorliegende Arbeit stellt eine fachliche Ausarbeitung dar, weshalb Grundkenntnisse im Computer- und Datenbankbereich vorausgesetzt werden.

Ein Großteil der aufgezeigten Beispiele und Performancetests beruht auf der von Microsoft zur Verfügung gestellten Beispieldatenbank „Contoso BI Demo Dataset for Retail Industry“, im Folgenden nur noch als „Contoso“ bezeichnet. Diese Datenbank kann kostenlos aus dem

Internet heruntergeladen werden ¹ .

Für sämtliche Beispiele und Tests wurde die Microsoft SQL Server 2008 Enterprise Edition verwendet. Eine 180-Tage lang verwendbare Testversion kann ebenfalls aus dem Internet

² heruntergeladen werden .

Soweit möglich, wurde versucht unnötige Fremdwörter zu vermeiden und die Begriffe der deutschen Originaldokumentation zu verwenden. In den meisten Fachtexten und Büchern werden die englischen Bezeichnungen bevorzugt, weshalb eine gewisse Vermischung von deutschen und englischen Begriffen im weiteren Text vorkommen kann.

Alle in dieser Arbeit verwendeten Firmen- und/oder Produktnamen sind Warenzeichen und/oder eingetragene Warenzeichen ihrer jeweiligen Hersteller in ihren Märkten und/oder Ländern.

¹ 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

Zusammenfassung

Datenbanken bilden das zentrale Rückgrat nahezu jeder modernen Unternehmensanwendung. Neben der Datensicherheit und -integrität spielt auch die Hochverfügbarkeit, Lastverteilung und Geschwindigkeit eine zunehmend wichtigere Rolle.

Diese Arbeit gibt einen Überblick über die am häufigsten auftretenden Ursachen für Performanceprobleme und deren Lösungsmöglichkeiten.

Als Einstieg in die Thematik werden die erforderlichen Grundlagen aus Sicht der internen Verarbeitung von SQL-Anweisungen, der Datenverwaltung durch den SQL Server, der Möglichkeiten einer Performanceüberwachung und Leistungsmessung, sowie der technischen Systemumgebung, besprochen. Anschließend werden die vom SQL Server unterstützten Indizes, deren Verwaltung und die Rolle von Statistiken im Optimierungsprozess thematisiert. Der letzte Teil stellt die Möglichkeiten einer Daten- und Lastverteilung durch Partitionierungsmechanismen, sowie die Aspekte der SQL-Code Optimierung vor.

Als technische Grundlage dieser Arbeit dient der Microsoft SQL Server 2008. Ein Großteil der hier genannten Performanceprobleme und Lösungsansätze lässt sich aber auch auf frühere Versionen des Microsoft SQL Server, wie auch auf andere relationale Datenbanksysteme übertragen.

Abstract

Databases form the central backbone of almost every modern business application. The high availability, load distribution and performance play an increasingly important role besides the data security and integrity.

This thesis gives an overview of the most frequent causes for performance problems and their possible solutions.

As introduction to the theme, the necessary basics from the perspective of the internal processing of SQL statements, the data management through the SQL Server, the possibilities of performance monitoring and measurement, as well as the system environment are being discussed. Afterwards, the thesis describes the supported indices and the role of statistics in the optimization process. The last part presents the possibilities of a data and load distribution through partitioning mechanisms, as well as aspects of SQL code optimization.

The Microsoft SQL Server 2008 serves as the technical basis for this thesis. Most of the treated performance problems and their possible solutions can be applied to older versions of the Microsoft SQL Server, as well as other relational database systems.

Inhaltsverzeichnis I

Inhaltsverzeichnis   

Inhaltsverzeichnis   I

Begriffs  - und Abkürzungsverzeichnis  III

1.  Einleitung  1

2.  Grundlagen  2

2.1  Optimierungsmodell  2

2.2  Interne Verarbeitung von SQL-Anweisungen  3

2.3  Datenverwaltung durch den SQL Server  8

2.3.1  Datenbank  8

2.3.2  Lesen von Datenseiten  9

2.3.3  Schreiben von Datenseiten  10

2.4  Performanceüberwachung und Leistungsmessung  11

2.4.1  Ausführungszeit einer SQL-Anweisung messen  11

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

2.4.3  Grafische Ausführungspläne  14

2.4.4  Der Aktivitätsmonitor  17

2.4.5  Berichte  19

3.  Systemumgebung  27

3.1  Arbeitsspeicher  27

3.2  Prozessor  27

3.3  Festplatten.  28

3.3.1  Festplattenarten  28

3.3.2  RAID-Systeme  30

3.4  Netzwerk  34

3.5  Betriebssystem  34

3.6  Datenbankmanagementsystem  35

4.  Indizes  36

4.1  Grundlagen von Indizes  36

4.2  Verwendung von Indizes  37

4.2.1  HEAP - Tabelle ohne gruppierten Index  37

4.2.2  Gruppierte Indizes (Clustered Index)  40

4.2.3  Nicht gruppierte Indizes (Nonclustered Index)  43

4.2.4  Eindeutige Indizes (Unique Indexes)  47

4.2.5  Gefilterte Indizes (Filtered Indexes)  47

Inhaltsverzeichnis II

4.2.6  Kombinierte Indizes (Combined Indexes)  49

4.2.7  Abdeckende Indizes (Covering Indexes)  50

4.3  Verwaltung von Indizes  51

4.3.1  Der Füllfaktor  51

4.3.2  Indizes reorganisieren  52

4.3.3  Indizes neu erstellen  53

4.3.4  Fehlende Indizes aufspüren.  56

4.3.5  Überflüssige Indizes  59

5.  Statistiken  61

5.1  Statistiken anzeigen  61

5.2  Statistiken anlegen  65

5.3  Statistiken aktualisieren  67

5.4  Gefilterte Statistiken  70

6.  Partitionierung.  73

6.1  Hardwareorientierte Partitionierung  73

6.2  Vertikale Partitionierung  73

6.3  Horizontale Partitionierung  77

7.  Abfrageoptimierung  86

7.1  Aufbau einer Abfrage  86

7.2  Parametrisierung  92

7.2.1  Automatische Parametrisierung  92

7.2.2  Erzwungene Parametrisierung  93

7.2.3  Explizite Parametrisierung  95

7.2.4  Parametrisierung durch gespeicherte Prozeduren  95

7.2.5  Parametrisierungsproblematik  97

7.3  Hinweise  99

7.3.1  Abfragehinweise (Query Hints)  99

7.3.2  Tabellenhinweise (Table Hints)  99

7.3.3  Verknüpfungshinweise (JOIN Hints)  100

8.  Fazit  104

9.  Literaturverzeichnis  105

10.  Abbildungsverzeichnis  108

11.  Tabellenverzeichnis  112

12.  Anhang.  113

13.  Eidesstattliche Erklärung  114

Begriffs- und Abkürzungsverzeichnis III

Begriffs- und Abkürzungsverzeichnis

Cache

CPU

DBCC

Deadlock Als Deadlock wird der Zustand bezeichnet, bei dem sich ein

DMV

E/A

EB

GB Ein GB (Gigabyte) ist eine Maßeinheit der Informatik.

HDD

IAM

Index Scan

Begriffs  - und Abkürzungsverzeichnis  IV

Index Seek Bei einem Index Seek werden auf einzelne Zeilen des Index

gezielt  zugegriffen.  

LDF  LDF (Log Data File) ist die Standard-Dateiendung einer  

Protokolldatei  im SQL Server.  

MDF  MDF (Main Data File / Master Data File) ist die Standard-  

Dateiendung  einer primären Datendatei im SQL Server.  

NDF  NDF (Non Primary Data File) ist die Standard-Dateiendung  

einer  sekundären Datendatei im SQL Server.  

PB  Ein PB (Petabyte) ist eine Maßeinheit der Informatik und  

entspricht  1.048.576 GB.  

RAID  Ein RAID (Redundant Array of Independent Disks) ist ein  

Verbund  mehrerer Festplatten zu einem logischen Laufwerk.  

RID  -Lookup Bei einem Index-Fund muss die gefundene Zeile mit einem  

RowID  -Lookup aus der Tabelle geholt werden, um alle  

ben  ötigten Daten zu erhalten.  

SAS  SAS (Serial Attached SCSI) ist eine Computerschnittstelle zur  

Daten  übertragung.  

SATA  SATA (Serial ATA) bezeichnet eine Computerschnittstelle, die  

in  erster Linie für den Datenaustausch zwischen der CPU und  

einer  Festplatte entwickelt wurde.  

SCSI  SCSI (Small Computer System Interface) ist eine  

Computerschnittstelle  zur Datenübertragung.  

SSD  SSD (Solid State Drive) ist ein nichtflüchtiger Speicher, der  

mittlerweile  auch in Festplatten verbaut wird.  

SQL  SQL (Structured Query Language) ist die Standard-  

Datenbanksprache  für relationale Datenbanksysteme  

Begriffs- und Abkürzungsverzeichnis V

SQL Server Agent

T-SQL

Table Scan

TB

Thread

Einleitung 1

1. Einleitung

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.

Grundlagen 2

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 Performance-Optimierung als geeignet erwiesen, wobei die einzelnen Optimierungsphasen jeweils auf die Vorhergehenden aufbauen.

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.

Grundlagen 3

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:

Grundlagen 4

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.

Grundlagen 5

Ü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.

Jeder Verarbeitungsschritt erzeugt gewissermaßen eine temporäre Tabelle, welche an den nächsten Bearbeitungsschritt 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.

Grundlagen 6

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

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

Grundlagen 7

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.

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.

Grundlagen 8

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 ³ 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 5 Optimierer auf Verteilungsstatistiken zurück (siehe Kapitel ).

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.

³ Eine genauere Beschreibung der physikalischen JOIN-Operationen ist in Kapitel 7.3.3 zu finden.

Grundlagen 9

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 Datencache-Trefferrate und ausreichend Arbeitsspeicher sind demnach Hauptindikatoren für eine gute Performance.

Abbildung 3: Vorgang beim Lesen von Datenseiten

Grundlagen 10

2.3.3 Schreiben von Datenseiten

Bei Datenänderungen, wie UPDATE, INSERT und DELETE, findet immer ein logischer Schreibvorgang 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. ⁴ 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 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.

⁴ 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.

Grundlagen 11

2.4 Performanceüberwachung und Leistungsmessung

Dieses Kapitel basiert im Wesentlichen auf: [Asbr11], [Baud06], [Baud08], [Lore06], [Lore10], [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 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.

Grundlagen 12

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

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- Funktionauch 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

Grundlagen 13

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 LOB-Lesevorgä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.

⁵ 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!

Grundlagen 14

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.

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)

Grundlagen 15

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:

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 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.