Der Einsatz eines SQL Servers erfordert ab einer bestimmten Datenbankgröße auch immer ein gewisses Maß an Konfigurations- und Wartungsaufwand. Ein guter Start hierfür ist die Verwendung des Maintenance Plan Wizards oder eines SQL Jobs, der den im MP Wizard empfohlenen Tasks nachempfunden ist. Der folgende Artikel gibt ein paar Tipps über Konfigurationseigenschaften des SQL Servers, die vielleicht nicht auf den ersten Blick ersichtlich sind, aber weitreichende Konsequenzen haben.

Grundsätzlich erfordert die Optimierung des SQL Servers Know-How in der generellen Administration, sowie in der Arbeitsweise der Datenbank, bzw. der darüber liegenden Applikation. Das Problem (und zugleich sein großer Vorteil) des SQL Servers ist, dass er beim Setup durch die MS Installationsroutine so konfiguriert wird, dass er sofort einsatzbereit ist. So angenehm dies auch ist, wird bei größeren Datenbanken damit niemals ein Optimum an Performance erreicht.

Speicherverwaltung

In den Eigenschaften des SQL Servers steht die Möglichkeit der Speicherkonfiguration zur Verfügung. Hier kann sowohl der minimale als auch der maximale Speicherverbrauch angegeben werden. Je mehr Speicher dem SQL Server an dieser Stelle zugewiesen werden kann, desto besser die Performance. Allerdings führt die Standardeinstellung von 2 Petabyte pro Instanz dazu, dass speziell bei mehreren installierten Instanzen dem OS oder anderen laufenden Prozessen kein Arbeitsspeicher mehr zugewiesen werden kann, da der SQL Server den kompletten Speicher allokiert hat. Daher ist empfehlenswert, den maximalen Serverspeicher so zu konfigurieren, das dem OS immer mindestens 2 GB bleiben. Weitere auf SQL Server laufende Applikationen müssen ebenfalls eingerechnet werden.

Wichtig ist noch anzumerken, dass es sich hier um den Buffer-Pool handelt. Dies bedeutet, dass ausschließlich die Pages (also die Daten der Tabellen) zu je 8 kB und die Ausführungspläne hier gespeichert werden. Zusätzliche Komponenten, wie z.B. Stored Procedures, sind nicht Bestandteil des Buffer Pools und belegen weiteren Arbeitsspeicher. Dies hat z.B. Auswirkungen auf die Discovery Datenbank, in der sehr viel Logik in Stored Procedures abgebildet ist.

Parallelisierung

Eine weitere Interessante Eigenschaft ist der „Degree of Parallelism“ der definiert, wie viele CPUs gleichzeitig für die Ausführung einer Abfrage verwendet werden sollen. Die Standardeinstellung von „0“ führt dazu, dass der SQL Server alle CPUs benutzt sofern dies Sinn macht. Leider nutzt der SQL Server diese Möglichkeit sehr schnell, was zur Folge hat, dass auch für sehr kleine Abfragen ein paralleler Task eröffnet wird. Werden mehrere parallele Threads eröffnet, muss aber immer auf die Beendigung aller Threads gewartet werden, um das Ergebnis ausgeben zu können. Die dabei entstehende Wartezeit kann also ein Indiz dafür sein, dass der SQL Server zu aggressiv auf parallele Ausführungspläne zurückgreift.

Diese Wartezeit kann mit der SQL Abfrage

select * from sys.dm_os_wait_stats where wait_type = 'CXPACKET'

ausgewertet werden. Wichtig ist hierbei der Wert 'wait_time_ms'. Sollte die angegebene Wartezeit zu hoch sein (wobei der Schwellwert im Auge des Betrachters liegt) kann die Anzahl der genutzten CPUs mit dem Wert “Max. Degree of Parallelism” modifiziert werden.

Trennen der Datenbankdatei und der Logdatei

Der SQL Server ist im Grunde eine In-Memory Datenbank. Alle Änderungen werden im Buffer Manager, also im Arbeitsspeicher durchgeführt. Um die Synchronisation zur Festplatte zu gewährleisten, werden Transaktionen wie folgt ausgeführt:

1. Der Benutzer setzt eine schreibende Abrage ab (INSERT; DELETE; UPDATE)

2. Der Buffer-Manger lädt die notwendigen Daten in den Buffer-Pool

3. Der Buffer-Manager ändert die Daten im Buffer-Pool und markiert alle betroffenden Pages als dirty

4. Der Log-Manager schreibt in das Transaktionslog die Änderung, die in der Data-Page durchgeführt wurde

5. Sobald das Windows IO Subsystem bekannt gibt, dass die Änderung im Transaktionlog festgehalten wurde, wird die Transakation „commited“

6. Der Lazy-Writer Prozess übernimmt zu einem festgelegten Zeitpunkt die geänderten Pages in die Datenbankdatei

Mit diesem Verfahren werden 2 konkurrierende Zugriffe auf der Festplatte betrieben:

• Der mehr oder weniger zufällige Zugriff auf die Datenbankdatei

• Der sequenzielle Zugriff auf das Transaktionsprotokoll

Im Standardinstallationsumfang liegen Transaktionsprotokoll und Datenbankdaten auf der gleichen Festplatte wie das Betriebssystem. Da der Zugriff auf das Transaktionsprotokoll sequenziell ist, könnte der Festplattenkopf aber bei seiner letzten Position verharren und darauf warten, dass die nächste Schreiboperation eintrifft. Daher empfiehlt es sich, das Transaktionsprotokoll auf eine eigene lokale Festplatte ohne konkurrierende Zugriffe zu legen.

Weiterhin können auch die Tabellen auf verschiedene Datenbankdateien und diese wiederum auf verschiedene Festplatten verteilt werden. Die bestehende Konfiguration einer Enteo/DSM/Discovery-Datenbank sollte nicht nachträglich verändert werden, somit fällt diese Option (zum Erstellungszeitpunkt dieses Artikels) weg. Allerdings wird bei Enteo/DSM die Historientabelle schon durch das Setup in eine eigene Datenbankdatei geschrieben. Diese Datei kann problemlos auf eine andere Festplatte verschoben werden.

Initialgrösse und Autogrowth Optionen

Je nach SQL Serverversion sind die Intialgrössen der Datenbanken nicht ideal gesetzt. Oft ist hier die Größe von 3 MB mit einem Wachstumswert von 1 MB angegeben. Hier sollte die Datenbank immer mit dem Wert angelegt werden, was auch im Zielzustand erwartet wird. Sowohl bei der Discovery- als auch bei der Enteo/DSM-Datenbank kann von ca. 2-3 MB pro Client ausgegangen werden. Die Autogrowth Option sollte großzügig aber auf den verfügbaren Plattenplatz angepasst gesetzt werden.

Wird die Datenbankdatei vergrößert, werden die neuen Bereiche der Datenbankdatei mit Nullen gefüllt, um alte Datenwerte gesichert zu löschen. Das heißt aber, dass bei einem Autogrowth die Datenbank so lange steht, bis alle Bereiche mit Nullen gefüllt sind. Um dies zu verhindern, muss das SQL Dienstkonto das Recht „Perform volume maintenance tasks“ (gpedit.msc → Local Policies → User Right Assignments) besitzen. Der SQL Server schreibt nun nach Neustart des SQL Server Dienstkontos keine Nullen mehr in die neuen Dateibereiche. Bei einer SSD Festplatte verringert sich die Zeit, die für das Wachstum einer Datenbankdatei von 1GB notwendig ist, von 5 Sekunden auf unter 1 Sekunde. Noch dramatischer wird die Differenz beim Einsatz von normalen Festplatten.
Diese Einstellung gilt allerdings nur für die Datenbankdateien, nicht für das Transaktionsprotokoll.

Wird ein Autogrowth bei einem Transaktionsprotokoll durchgeführt, legt der SQL Server ein „Virtual Log File (VLF)“ an und hängt dieses innerhalb des Transaktionsprotokolls an die letzte Stelle. Durch die Menge der VLFs wächst der Verwaltungsaufwand für den SQL Server. Die Anzahl der VLFs kann über den Befehl DBCC LOGINFO('Datenbankname') ausgegeben werden. Also ist auch hier eine Initialgrösse, die der Anzahl der zu erwartenden Anzahl an Transaktionen entspricht, ausschlaggebend für die Gesamtperformance der Datenbank.

Löschen abgeschlossener Transaktionen vs. Simple Recovery Modell

Auf Ebene der Datenbank kann das Recovery Modell konfiguriert werden. Dabei gibt es 2 wesentliche Möglichkeiten: Simple und Full.

Im einfachen Recovery Modell werden abgeschlossene Transaktionen wieder direkt aus dem Transaktionsprotokoll entfernt. In Folge dessen, bleibt die Daten für das Transaktionsprotokoll relativ klein. Der Nachteil ist, das ein Restore nur zum Zeitpunkt des letzten Full- oder Differential Backup möglich ist.

Im Falle des vollen Transaktionsprotokoll ist das Restore mindestens bis zum letzten Backup des Transaktionsprotokoll möglich. Kann das Transaktionslog auch von der kaputten Festplatte gesichert werden, kann der Restore bis zur letzten im Log eingecheckten Transaktion durchgeführt werden. Im Falle des Full Recovery Modells muss manuell dafür gesorgt werden, dass die abgeschlossenen Transaktionen aus der Log-Datei entfernt werden. Dies wird grundsätzlich im Rahmen eines Full-Backups der Datenbank gemacht.

Datenbankshrink

Speziell bei der Enteo/DSM-Datenbank kommt es vor, dass ein Datenbankshrink durchgeführt wird, um die Datenbank nach dem Löschen der Historieneinträge wieder zu verkleinern. Dies führt aber zu extremen Performanceeinbußen. Intern versucht der SQL Server die ersten freien Speicherbereiche einer Datenbankdatei durch bereits bestehende Datenbankobjekte zu befüllen. Es werden dadurch die letzten Pages eines Index nach vorne verschoben und so rückwärts der komplette Index neu aufgebaut. Dadurch ergibt sich bei einem Index, der zuvor eine sehr geringe Fragmentierung vorwies, nach dem Shrink eine Fragmentierung von annähernd 100%. Wichtig ist also, das nach jedem Datenbankshrink der Index neu aufgebaut wird.