SQL Server - Performanceprobleme analysieren und beheben

3 Ausführliche Performanceanalyse

Wenn das dringende Performanceproblem beseitigt oder zumindest eine schnelle Lösung gefunden wurde, damit Server und Datenbanken kurzfristig wieder nutzbar sind, ist es an der Zeit, sich einer ausführlichen Analyse zuzuwenden. Das Hauptziel dabei liegt darin, die eigentlichen Ursachen der Performanceprobleme zu erkennen (und idealerweise auch zu beheben).

3.1 Vorgehensweise

Um bei der medizinischen Analogie zu bleiben, befindet sich der Patient (der SQL Server) jetzt nicht mehr im kritischen Zustand, sodass man sich die Zeit für eine gründliche und ausführliche Analyse nehmen kann. Dadurch erhält man auch die Möglichkeit, nicht nur die akuten Symptome eines Problems zu behandeln, sondern auch den Problemursachen auf den Grund zu gehen, um diese im Idealfall zu beseitigen.

Dabei müssen Sie sich nicht an die Reihenfolge der in diesem Kapitel beschriebenen Analysemaßnahmen halten. Im besten Fall hat die schnelle Erstanalyse des akuten Problems bereits einige mögliche Problembereiche aufgedeckt, die Sie nun genauer unter die Lupe nehmen können. Aber auch wenn dies nicht der Fall ist, können Sie die im Folgenden beschriebenen Abschnitte des Kapitels in beliebiger Folge abarbeiten.

3.2 Prüfung von Server und Betriebssystem

Damit eine Serveranwendung wie der SQL Server performant laufen kann, muss die richtige Hardware nicht nur vorhanden, sondern auch entsprechend konfiguriert sein. Dasselbe gilt für das Betriebssystem, das quasi als Schnittstelle zwischen Hardware und SQL Server fungiert und letzterem die benötigten Ressourcen zur Verfügung stellt.

Prozessor

Seit einigen Jahren steigen die Taktraten von neueren Prozessorgenerationen nicht mehr gravierend, sondern haben sich bei Werten zwischen 2 und 3 GHz eingependelt. Stattdessen setzt man mittlerweile neben besserem Caching-Verhalten (durch einen größeren prozessorinternen Cache) auf mehr Parallelität durch die Verwendung von mehr CPUs, aber auch mehr Kernen pro CPU.

Dies muss jedoch von dem verwendeten Betriebssystem sowie der genutzten Edition von SQL Server auch unterstützt werden. Windows 2008 R2 Standard unterstützt beispielsweise maximal vier CPUs, während die Enterprise Edition acht CPUs nutzen kann – jeweils unabhängig von der Anzahl der Cores pro CPU. Bei SQL Server kann die Express Edition nur eine CPU nutzen, die Standard Edition schon vier und erst die Enterprise Edition hat hier keine eigene Obergrenze, sondern wird nur durch das darunterliegende Betriebssystem begrenzt. Wenn Sie also eine zu kleine Edition von Windows oder SQL Server nutzen, laufen Sie Gefahr, in teure CPUs investiert zu haben, die nicht alle verwendet werden.

Hauptspeicher

Dies ist der Bereich, in dem sich durch Hardwareaufrüstung am ehesten etwas erreichen lässt. Dies setzt allerdings voraus, dass sowohl vom Betriebssystem als auch von SQL Server eine Edition verwendet wird, die diesen auch nutzen kann.

Generell sollte man heutzutage im Serverumfeld ausschließlich die 64-Bit-Varianten nutzen, da die 32-Bit-Varianten lediglich 4 GB adressieren können.

Auch die Editionen von SQL Server haben definierte Obergrenzen für den nutzbaren Hauptspeicher. So kann die Express Edition maximal 1 GB verwenden, die Standard Edition mindestens 64 GB (bei SQL Server 2008 unbegrenzt), und spätestens die Enterprise Edition kann den gesamten vom Betriebssystem bereitgestellten Speicher nutzen.

Stellen Sie daher sicher, dass die verwendete Version und Edition des Betriebssystems den gesamten vorhandenen Speicher auch verwenden kann.

Festplatten

Zugriffe auf physikalische Datenträger wie Festplatten sind vergleichsweise langsam. Daher versucht SQL Server, diese nach Möglichkeit durch intelligentes Caching zu minimieren. Muss aber dennoch auf den Datenträger selbst zugegriffen werden, gibt es ein paar hilfreiche Tricks, mit denen die Zugriffe möglichst performant ausgeführt werden können.

Prüfen Sie, ob die folgenden Dateien auf verschiedene Platten(-systeme) verteilt sind, um einen parallelen Zugriff zu ermöglichen:

• System (Betriebssystem und SQL Server)
• Windows-Auslagerungsdatei
• Datenbankdateien
• Protokolldateien
• TempDB (Datenbank und Protokoll)
• Filestream-Freigabe (sofern verwendet)
• Back-up-Dateien

Wenn weniger Platten zur Verfügung stehen, sollten zumindest die Dateien, die häufig gleichzeitig verwendet werden, auf diesen verteilt werden: System, Datenbank, Protokoll und TempDB.

Mit frei erhältlichen Tools wie beispielsweise SQLIO lassen sich die Zugriffszeiten der verschiedenen Plattensysteme testen. Prüfen Sie alle verfügbaren Systeme, um die Dateien dann so zu verteilen, dass insbesondere Protokoll und TempDB auf möglichst schnellen Datenträgern liegen, während für Back-ups die langsamsten Datenträger genutzt werden können.

Alle Datenträger nutzen eine fest definierte Blockgröße, die bei Dateisystemen wie NTFS beim Formatieren festgelegt werden kann. Da SQL Server Daten in 64-KB-Blöcken liest und schreibt, stellt dies auch die optimale Blockgröße für die Datenträger dar, auf denen Datenbank- und Protokolldateien liegen.

Netzwerk

Prüfen Sie, ob die im Server verwendeten Netzwerkkarten mit der maximal möglichen Geschwindigkeit und im Vollduplex-Modus laufen. Hierzu finden Sie im Betriebssystem unter den Eigenschaften der Netzwerkverbindung einen Punkt zur Konfiguration der Netzwerkkarte. Bei den meisten Netzwerkkartentreibern finden Sie hier unter den erweiterten Eigenschaften eine Einstellung für Verbindungsgeschwindigkeit und Duplex-Modus. Sollte hier ein zu niedriger Wert oder auch Halbduplex-Modus eingestellt sein, korrigieren Sie die Auswahl auf den maximal von der Netzwerkkarte (und Verkabelung) unterstützten Wert. Auch wenn hier Auto eingestellt sein sollte, ist eine explizite Einstellung vorzuziehen, da die Auswahl Auto gelegentlich dafür sorgt, dass ein zu langsamer Modus verwendet wird.

Abbildung 3.1: Konfiguration der Netzwerkkarte

Betriebssystem

Auch wenn man die richtige Betriebssystemedition für die vorhandene Hardware einsetzt, gibt es dort einige Einstellungen zu prüfen, die dafür verantwortlich sein können, dass der Server langsamer als nötig läuft.

Die neueren Versionen von Windows nutzen einen Energiesparplan, der in der Windows-Systemsteuerung konfiguriert werden kann. Sollten Sie die Einstellung dort nicht finden, können Sie alternativ auch die Kommandozeilenvariante nutzen, die durch die Zeichenfolge Powercfg aufgerufen wird. Dadurch wird nicht nur gesteuert, nach welcher Zeit der Rechner den Bildschirm ausschaltet oder in den Energiesparmodus wechselt, sondern beispielsweise auch, ob oder wann der Prozessor mit voller Taktfrequenz läuft. Während diese Energiesparfunktionen für einen Desktoprechner und noch viel mehr für ein Notebook durchaus sinnvoll sind, sollten Server generell den Modus Höchstleistung nutzen. Selbst die Standardeinstellung Ausbalanciert, in der bei Bedarf auch die volle Leistung bereitgestellt wird, reagiert nicht schnell genug auf kurzzeitige Lastspitzen, um diese mit voller Geschwindigkeit abzufangen. Echte Serverhardware sollte dafür ausgelegt sein, auch bei dauerhaft maximaler Taktfrequenz nicht zu überhitzen.

Ein weiterer wichtiger Aspekt ist die Konfiguration der Windows-Performanceoptionen. Diese sind in der Systemsteuerung unter System und Sicherheit | System | Erweiterte Systemeinstellungen zu finden1. Hier gibt es dann im Bereich Leistung eine Schaltfläche für Einstellungen, durch die ein weiteres Dialogfeld mit der Bezeichnung Leistungsoptionen erscheint. Hier finden Sie auf der Registerkarte Erweitert eine Möglichkeit, die Prozessorzeitplanung entweder für Programme oder Hintergrunddienste optimieren zu lassen. Während die erste Option für Arbeitsplatzrechner die richtige Wahl ist, sollte für einen Server, der beispielsweise einen Datenbankserver beinhaltet, die Einstellung Hintergrunddienste gewählt werden.

Abbildung 3.2: Die Performanceoptionen von Windows Server 2012

Auf derselben Registerkarte befindet sich in dem Bereich Virtueller Arbeitsspeicher auch eine Schaltfläche, um die Windows-Auslagerungsdatei (engl. Pagefile) zu konfigurieren. Wenn man hier die Checkbox für die automatische Konfiguration entfernt, lässt sich stattdessen manuell ein Wert für die minimale und maximale Größe festlegen. Da ein häufiges Vergrößern und Verkleinern der Auslagerungsdatei eine hohe Last erzeugt und für zusätzliche Fragmentierung der Festplatte sorgt, sollte hier für beide Einstellungen derselbe Wert verwendet werden. Microsoft empfiehlt als Einstellung die zweieinhalbfache Größe des installierten Arbeitsspeichers, wobei man bei sehr umfangreichem Arbeitsspeicher auch einen niedrigeren Wert wählen oder eventuell sogar ganz auf eine Auslagerungsdatei verzichten kann.

Wie bereits weiter oben erwähnt, ist aus Performancegründen die Nutzung einer separaten Festplatte für die Auslagerungsdatei zu empfehlen. Hier kann beispielsweise eine kostengünstige SATA-II-Platte genutzt werden.

Vor dem Ändern der Größe und/oder des Speicherorts der Auslagerungsdatei sollte eine Defragmentierung des entsprechenden Laufwerks durchgeführt...