Ab und zu kann es vorkommen, dass ein SQL Server langsam läuft und die Frage im Raum steht warum das so ist. Ist der SQL Server langsam, so ist das oft die Auswirkung der darunterliegenden Hard- und Softwarekonfiguration. Einen Überblick darüber wie sich der Server, auf dem sich der SQL Server befindet, verhält bekommt man mit dem Performance Monitor. Aus den, mit dem Performance Monitor gemessenen Werten, kann man dann ableiten, was genau dem Server fehlt, d.h. ob er beispielsweise zu wenig RAM hat oder ob der Prozessor überlastet ist oder ob es ansonsten Probleme gibt. Im Performance Monitor kann man zahlreiche Performance Counter auswählen. Damit man ein einigermaßen realistisches Bild bekommt und nicht in zu vielen Informationen ertrinkt ist es wichtig, die richtigen Performance Counter auszuwählen. In diesem Artikel möchte ich gerne zusammenfassen welche Performance Counter am besten für eine erste Analyse verwendet werden sollten.
Memory Counter
Memory – Available MBytes: Dieser Performance Counter zeigt an, wie viel Speicherplatz im System noch frei ist, d.h. wie viel RAM noch verwendet werden kann. Idealerweise sollte man hier nicht zu wenig RAM zur Verfügung stellen, damit bestimmte Aktionen, wie z.B. das Öffnen des SQL Server Management Studios auf dem Server diesen nicht in die Knie zwingen. Ein guter Wert mit dem man auf jeden Fall auf der sicheren Seite ist, ist 1 GB. Wenn man 1 GB (oder mehr) frei hat, hat der Server sicherlich auch noch genügend Ressourcen um unvorhergesehene Ereignisse bewältigen zu können.
Paging File – % Usage: Dieser Performance Counter zeigt an, zu wie viel Prozent die Auslagerungsdatei verwendet wird. Ein idealer Wert für diesen Performance Counter ist sicherlich der Wert 0. Wird auf einem Server ein Wert 0 für die Page File -% Usage gemessen bedeutet das, dass der Server alles was benötigt wird im Hauptspeicher halten kann und dass es keine Notwendigkeit gibt Daten in die Auslagerungsdatei zu verschieben. Wird der Wert 0 nicht erreicht (was in den meisten Fällen der Fall ist), so sollte hier doch ein sehr kleiner Wert angestrebt werden.
SQLServer: Buffer Manager – Page life expectancy: Dieser Performance Counter zeigt an wie lange eine Seite im Speicher gehalten werden kann bevor sie im Speicher gelöscht werden muss um Platz für etwas Anderes zu schaffen. Je länger eine Seite im Speicher verweilt umso besser da wir ja den Hauptspeicher bzw. den Buffer dazu nutzen wollen Seiten von Platte im Speicher zu cachen. Die Page life expectancy wird in Sekunden gemessen und ein guter Wert hierfür sind 180s. Sollte es hier zu Problemen kommen kann man zwei Strategien fahren. Zum einen kann man einfach mehr Speicher in den Server einbauen und hoffen, dass sich das Problem dann von alleine löst. Die andere Möglichkeit die man hat ist, dass man sich einmal die Abfragen anschaut. Ist die Page life expectancy gering kann es auch daran liegen, dass Abfragen eine sehr große Datenmenge in den Speicher laden wollen. Hier ist zu prüfen, ob diese große Datenmenge überhaupt notwendig ist oder ob man die Abfragen so tunen kann, dass man sich schon sehr früh in der Abfrage von sehr vielen, nicht notwendigen Daten trennt (hohe Selektivität). Ein anderer Grund kann sein, dass der SQL Server durch die Abfrage dazu gezwungen wird einen Table-Scan durchzuführen. Das ist insbesondere bei sehr großen Tabellen ein Problem, da ein Table-Scan viel Speicher benötigt (jeder einzelne Datensatz wird angeschaut) und somit andere Dinge aus dem Speicher entfernt werden müssen. Hat man Probleme mit Table-Scans macht es sicherlich Sinn, sich einmal anzuschauen ob vielleicht Indizes fehlen und diese dann anzulegen damit Table Scans vermieden werden können.
SQLServer:Memory Manager – Memory Grants Pending: Dieser Wert stellt die Anzahl an Abfragen dar die gerade auf Ihre Ausführung warten weil es momentan nicht genügend Speicher gibt der den Abfragen zur Verfügung gestellt werden kann. Dieser Wert sollte immer auf 0 stehen. Ist das einmal nicht der Fall ist das ein klares Zeichen dafür, dass der Server zu wenig Speicher besitzt.
Storage Counter – Physical Disk
Sehr oft ist der Weg zur und von der Festplatte ein typischer Flaschenhals. Daher macht es viel Sinn, sich diese Performance Counter einmal anzuschauen. Da heutzutage oft SANs oder andere Konstrukte verwendet werden ist es nicht immer ganz einfach die Performance zu messen. Was aber für den SQL Server entscheidend ist ist, wie schnell Daten von der Platte gelesen und wieder auf die Platte geschrieben werden können. Hierbei ist völlig unerheblich ob es sich um eine Platte auf dem SAN handelt oder um eine lokale Festplatte. Wichtig ist nur, wie schnell die Platte reagiert.
Avg. Disk sec/Read: Dieser Wert gibt an, wie lange es dauert Daten von der Festplatte zu lesen. Die Werte die hier gemessen werden, werden in Sekunden gemessen. Eine Sekunde ist beim Lesen oder Schreiben auf Disk eine unendlich lange Zeit. Daher macht es Sinn sich für diesen Performance Counter drei Nachkommastellen anzeigen zu lassen. In der Regel sollte die Zeit die der Server braucht um von der Platte zu lesen bzw. auf die Platte zu schreiben unter 100ms liegen. Ist das nicht der Fall hat man definitiv ein Problem mit dem Storage-System.
Avg. Disk sec/Write: Dieser Wert gibt an wie lange es dauert Daten auf die Festplatte zu schreiben. Für diesen Wert gelten dieselben Regeln wie für Avg. Disk sec/Read. Alles was über 100ms liegt ist ziemlich schlecht.
Disk Reads/sec: Zeigt an wie viele Lesezugriffe pro Sekunde auf der Festplatte ausgeführt werden. An diesem Wert kann man nicht viel ändern, er ist aber ein guter Indikator dafür, wie viel der Server zu tun hat. Ist der Wert recht klein, so hat der Server nicht so viel zu tun, ist der Wert hoch, so steht der Server ziemlich unter Dampf.
Disk Writes/sec: Zeigt an wie viele Schreibzugriffe es pro Sekunde auf der Festplatte gibt. Hier gilt dieselbe Aussage wie für Disk Reads/sec – je höher der Wert ist, desto beschäftigter mein Server.
Disk Reads/sec und Disk Writes/sec sind Werte die man gut für den Aufbau einer Baseline nutzen kann. Wenn man diese Werte über das Jahr aufnimmt und zeigen kann, dass die Anzahl der Reads/s und Writes/s zunimmt ist das ein guter Indikator dafür, dass das System immer stärker ausgelastet wird und dass es eine gute Idee ist in mehr Speicher zu investieren.
CPU
Prozessor: % Prozessor Time: Zeigt an wie ausgelastet der Prozessor ist.
System – Prozessor Queue Length: Dieser Wert zeigt an wie viele Prozesse darauf warten von der CPU ausgeführt zu werden. Dieser Wert ist nicht SQL Server spezifisch, sondern bezieht sich auf alle Prozesse, die auf dem Server laufen.
SQL Server spezifische Performance Counter
SQLServer:Batch Statistics – Compilations/sec: Dieser Wert gibt an wie viele Compilierungen pro Sekunde durchgeführt werden, d.h. wie oft SQL Code übersetzt wird.
SQLServer:Batch Statistics – Recompilations/sec: Dieser Wert gibt an wie oft Recompilierungen pro Sekunde durchgeführt werden. Recompilierungen sind oft darauf zurückzuführen, dass mit nicht parametrisierten Abfragen gearbeitet wurde. Dazu ein kleines Beispiel. Möchte man aus einer Tabelle Person eine bestimmte Person ermitteln, dann werden die beiden Abfragen
SELECT FirstName ,
LastName
FROM Person.Person
WHERE FirstName = 'Ken';
SELECT FirstName ,
LastName
FROM Person.Person
WHERE FirstName = 'Rob';
Als separate Abfragen gesehen und es findet eine Recompilierung statt. Arbeitet man aber stattdessen mit Parametern wie im folgenden Beispiel
DECLARE @FirstName VARCHAR(100) = 'Ken';
SELECT FirstName ,
LastName
FROM Person.Person
WHERE FirstName = @FirstName;
SET @FirstName = 'Rob';
SELECT FirstName ,
LastName
FROM Person.Person
WHERE FirstName = @FirstName;
so kann der SQL Server erkennen, dass es dieselbe Abfrage ist und muss nicht neu compilieren. Ein anderer Grund für Recompilierungen sind Änderungen im Schema oder im Index.
Insbesondere der Vergleich zwischen Compilations/sec und Recompilations/sec kann aufschlussreich sein. Werden z.B. viele Recompilierungen pro Sekunde vorgenommen ist dies ein Zeichen dafür, dass z.B. sich die Statistiken oder die Indices massiv ändern.
SQLServer:General Statistics – User Connections: Die Anzahl der Benutzer ist auch ein Performance Wert den man aufzeichnen kann um ein Gefühl dafür zu bekommen wie viele Benutzer den SQL Server verwenden.
Bei einigen Performance Countern ist es möglich mehrere Instanzen der Counter aufzuzeichnen bzw. einen Durchschnittswert zu nehmen (z.B. Prozessor: % Prozessor Time). Im Bild kann man das gut erkennen. Man kann entweder _Total, <All instances> oder die beiden Prozessorkerne 0 und 1 aufzeichnen.In der Regel macht es mehr Sinn die einzelnen Counter zu verwenden statt den Durchschnittswert zu nehmen, da der Durchschnittswert bestimmtes Verhalten herausfiltern kann (z.B. eine CPU ist zu 100% ausgelastet und die anderen nur schwach, dann wird der Mittelwert OK aussehen).
Um einen ersten Überblick zu bekommen sollten wir also die folgenden Performance Counter aufzeichnen:
- Memory – Available MBytes
- Paging File – % Usage
- Physical Disk – Avg. Disk sec/Read
- Physical Disk – Avg. Disk sec/Write
- Physical Disk – Disk Reads/sec
- Physical Disk – Disk Writes/sec
- Processor – % Processor Time
- SQLServer: General Statistics – User Connections
- SQLServer: Memory Manager – Memory Grants Pending
- SQLServer: SQL Statistics – Batch Requests/sec
- SQLServer: SQL Statistics – Compilations/sec
- SQLServer: SQL Statistics – Recompilations/sec
- System – Processor Queue Length
Damit man die Performance Counter nicht mühsam mit der Hand zusammenklicken muss habe ich ein kleines PowerShell Script geschrieben, dass die Counter automatisch aufzeichnet. Man muss nur (in Sekunden) angeben wie lange gemessen werden soll und wo die Ergebnisdatei gespeichert werden soll.
<#################################################################################################################
PowerShell Template Version V1.1
.Author
Frank Geisler
.Created date
2015-09-28
.Prerequisite
PowerShell V2
.Revision History
yyyy-mm-dd FGE
.Project
SQL Performance Messung
.Description
Dieses Script zeichnet die für eine grundlegende Performance-Messung eines SQL Servers benötigten
Performance-Counter auf. Dabei werden die Counter im Sekundenrythmus so lange aufgezeichnet wie in
der Variablen $timeToRun festgelegt ist. Das Ergebnis wird in dem in $path angegebenen Pfad gespeichert
und kann dann weiter ausgewertet werden.
.Execution Example
.\sqlperf.ps1
#################################################################################################################>
## Wie lange (in Sekunden) soll aufgezeichnet werden?
$timeToRun = 60
## Wo soll die Datei mit dem Messergebnis abgelegt werden?
$path = "C:\temp\SQLPerfFirstLook.blg"
Get-Counter `
-Counter "\memory\available MBytes",
"\Paging File(*)\% Usage",
"\PhysicalDisk(*)\Avg. Disk sec/Read",
"\PhysicalDisk(*)\Avg. Disk sec/Write",
"\PhysicalDisk(*)\Disk Reads/sec",
"\PhysicalDisk(*)\Disk Writes/sec",
"\Processor(*)\% Processor Time",
"\SQLServer:General Statistics\User Connections",
"\SQLServer:Memory Manager\Memory Grants Pending",
"\SQLServer:SQL Statistics\Batch Requests/sec",
"\SQLServer:SQL Statistics\SQL Compilations/sec",
"\SQLServer:SQL Statistics\SQL Re-Compilations/sec",
"\System\Processor Queue Length" `
-SampleInterval 1 `
-MaxSamples $timeToRun | Export-Counter `
-Path $path `
-Force
Das Script ist auf einem englischen Windows 2008 R2 Server mit SQL Server R2 entstanden und muss je nach Konfiguration ggf. angepasst werden, da die Namen der Performance Counter (z.B. ins Deutsche) übersetzt worden sind. Außerdem kann es je nach SQL Server Version sein, dass andere SQL-Server spezifische Performance Counter registiert werden.