Azure SQL Database – Lasttest der TempDB

Auf Facebook wurde ich gefragt, wie leistungsstark denn die „TempDB“ einer Azure SQL Database wäre bzw sehr man diese belasten könne… also was liegt näher als die „TempDB“ mit einem Lasttest zu verproben. Also habe ich über die Suchmaschine meines Vertrauens eine einfache und schnelle Möglichkeit gesucht, wie man explizit die „TempDB“ belasten kann. Bei SQLServerCentral bin ich dann fündig geworden => Load Generator for TempDB

CREATE PROC EXERCISE_TEMPDB_SILLY AS
   SET STATISTICS TIME ON
   SET STATISTICS IO ON

   CREATE TABLE #temp(RecNo INT PRIMARY KEY CLUSTERED, Data varchar(80))

   INSERT INTO #temp SELECT -1, CAST(NewID() AS varchar(80))

   DECLARE @cnt AS INT
   SET @cnt = 0

   WHILE @cnt > -1
   BEGIN
      Print 'Loop ' + CAST(@cnt AS VARCHAR(9))
      
      INSERT INTO #temp 
      SELECT RecNo-Power(2,@cnt) , Right(Data + CAST(NewID() AS varchar(80)), 80)
      FROM #temp

      SET @cnt = @cnt+1
   END

Mit dieser Stored Procedure wird eine temporäre Tabelle in der „TempDB“ erstellt und diese mit einfachen Werten befüllt, damit diese Werte nich „stupide“ Hochzählen werden diese mit Rechenoperationen manipuliert. Da diese While-Schleife keinen Exist hat, läuft dieser Insert-Befehl in einer Endlos-Schleife mit dem Ziel die Grenze der TempDB zu ermitteln, da sich die Anzahl der einzufügenden Datenzeilen mit jedem Loop erhöht. Ein Kommentar in dem Forumsbeitrag deutet darauf, dass der Verprober auf seinem SQL Server 21 Loops geschafft hat, was ein erster Anhaltspunkt ist. Meine Azure VM mit SQL Server 2016 (Standard DS12 v2 (4 vCPUs, 28 GB memory)) schafft innerhalb von 2 Minuten 23 Loops…

Azure SQL Database - Lasttest

 

Lasttest-Bedingungen

Meine erste Tests zeigten, dass die Anzahl der Loops tatsächlich in einem gewissen Bereich liegen bzw eine gewisse Zeit benötigen, daraus machte ich einen einheitlichen Test von 2 Minuten… heißt das ich nach 2 Minuten Laufzeit auf den „STOP“-Knopf drücke, um somit die Ausführung der Stored Procedure zu unterbrechen bzw abzubrechen. Nachdem der SQL Server die Ausführung beendet hat, habe ich die Anzahl der durchlaufenen Loops ermittelt.

Loop 1 ergab 1 Insert
Loop 2 ergab 2 Inserts
Loop 3 ergab 3 Inserts
Loop 4 ergab 8 Inserts
[…]
Loop 10 ergab 512 Inserts
Loop 11 ergab 1024 Inserts
Loop 12 ergab 2048 Inserts
[…]
Loop 18 ergab 131072 Inserts
[…]
Loop 20 ergab 524288 Inserts
Loop 21 ergab 1048576 Inserts
Loop 22 ergab 2097152 Inserts
Loop 23 ergab 4194304 Inserts
Loop 24 ergab 8388608 Inserts

Wie man sehen kann hat die Datenbank bzw die TempDB ganz schön viel Daten zu verarbeiten, was auf deren Leistungsfähigkeit schließen lässt. Dieser Test soll erst einmal nur einen Anhaltspunkt für die Belastungsgrenzen aufzeigen… Die kleineren Leistungsklassen der Azure SQL Databases haben bei diesem Lasttest gezeigt, dass sie nach einer gewissen Anzahl Insert bzw Loops einfach am Ende sind und nicht mehr oder nur noch bedingt in der Lage sind Daten zu verarbeiten/aufzunehmen. Überrascht war ich in den höheren Leistungsklassen wie schnell die Inserts erfolgten und diese konnten sicherlich noch mehr Daten aufnehmen, hätte ich die Laufzeit des Testes erweitert (was ich sicherlich zu einem späteren Zeitpunkt nochmal machen könnte).

Lasttest-Ergebnis-Vergleich

Leistungsklasse Anzahl Loops Anzahl Inserts
Azure SQL Database S0 18 131072
Azure SQL Database S1 18 131072
Azure SQL Database S3 22 2097152
Azure SQL Database S9 25 16777216
Azure SQL Database P1 23 4194304
Azure SQL Database P6 24 8388608
Azure SQL Database P15 24 8388608
Azure SQL Database PR1 23 4194304
Azure SQL Database PR6 24 8388608
Azure SQL Server
(Standard DS12 v2 (4 vCPUs, 28 GB memory))
24 8388608
Azure SQL Server
(Standard DS15 v2 (20 vCPUs, 140 GB memory))
24 8388608

Wie man an der Ergebnis-Tabelle erkennen kann, sind die Unterschiede zwischen den höheren Leistungsklassen und den bewährten SQL Servern nicht sonderlich groß bzw gar nicht vorhanden. Was entweder heißt, man kann die Azure SQL Databases genau so stark belasten wie einen herkömmlichen SQL Server oder aber sie erreichen genauso schnell ihre Limits. Mein persönlicher Eindruck ist eher, dass die höheren Klassen bei diesem Lasttest noch mehr leisten können, wenn man ihnen mehr Zeit gibt. Anders herum würde ich sagen, dass man die Azure SQL Databases ohne zu zögern und ohne Bedenken mit einem „normalen“ SQL Server mit SSDs vergleichen kann.

Azure SQL Database - Lasttest - TOP Ergebnis

Fazit des Lasttests

Aus aktueller Sicht würde ich jedem die Nutzung von Azure SQL Databases im Vergleich zu herkömmlichen SQL Servern empfehlen, sofern die Solution und das Umfeld/der Kunde dies ermöglichen.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

SQL Server 2016 – IFI-Aktivierung und TempDB-Optimierung

Jetzt wo der SQL Server 2016 offiziell erschienen ist, kommen auch immer mehr Details ans Licht, so dass man sich so langsam auf die ersten „richtigen“ Installationen vorbereiten kann/muss. Was hat sich im Vergleich zum SQL Server 2014 im Rahmen der Installation alles geändert? Wie muss man die vorhandene Dokumentation anpassen?

Beim Lesen zahlreicher Tweets und Blogs bin ich über einen Tweet zum SQL Server 2016 von Thomas Larock gestolpert, hier hat er eine sehr schöne Übersicht über eine Vielzahl von Neuerungen im SQL Server 2016, mit denen man zum Beispiel die Performance des SQL Servers steigern kann, erstellt. Bisher musste man eine Vielzahl von Parametern und Einstellungen zur Performance Optimierung vor der Installation (z.B. Instant File Initialization) oder wie die Anzahl der TempDB-Files nach der Installation anpassen.

Ich möchte hier auf die Neuerungen im Rahmen der SQL Server 2016 Installation eingehen, über die ich mich persönlich sehr freue, weil sie einen Teil meiner Arbeit sehr vereinfachen.

Instant File Initialization

Bisher musste man umständlich VOR der Installation des SQL Servers immer über die lokale Sicherheitsrichtlinie über das System-Tool „secpol.msc“ dem SQL-Service-User die Berechtigungen für „Perform Volume Maintenance Tasks/Durchführen von Volumenwartungsaufgaben“ einräumen. Erst das Tool öffnen, dann durch einen Strukturbaum klicken und noch die richtige Berechtigung finden, um dann den User in der Userverwaltung zu suchen… ein wirklich aufwändiger Weg um das Vollschreiben neuer Dateien bzw Dateibereiche zu umgehen, um so z.B. den Autogrowth Event zu beschleunigen…

Jetzt mit dem neuen SQL Server 2016 geht es wesentlich einfacher, da der Installationsprozess die Arbeit für uns übernimmt. Hier hat Microsoft, die in der Community gängige Praxis optimal umgesetzt und im Rahmen der Installation die Möglichkeit geschaffen einfach nur einen Haken zu setzen… tada…

SQLServer2016 Instant File Initialization

Nun kann man ganz einfach während der Installation einen Haken setzen und der Installationsprozess übernimmt für einen die Arbeit. Vielen Dank hierfür an Micorsoft!
Falls man den SQL Server 2016 von der Commandline installiert, hat man beim Aufrufen der „Setup.exe“ auch die Möglichkeit diesen Parameter entweder in der ConfigurationFile.ini mit anzugeben oder direkt als Option am Aufruf

/SQLSVCINSTANTFILEINIT=”True”

Automatic TEMPDB Configuration

Die Konfiguration der TempDB nach erfolgter Installation nahm immer ein wenig Zeit in Anspruch, natürlich konnte man das bisher auch skripten, aber so einfach wie jetzt war es noch nie bzw es bedeutete trotzdem einen gewissen Aufwand. Auch diese Konfiguration hat Microsoft in den Installationsprozess verlegt, so dass man nach der Installation nicht noch ein weiteres Skript aufrufen muss.

SQLServer2016 Config TempDB

In diesem neuen Tab unter Database Engine Configuration kann man die Anzahl der TempDB-Files, deren initiale Größe und Autogrowth-Wert festlegen. Man kann die Pfade beider Filetypen individuell anpassen, theoretisch könnte man auch mehrere Pfade für die TempDB-Datenfiles angeben.

Auch bei diesem neuen Konfigurations-Tab gibt es die Möglichkeit die Parameter über die ConfigurationFile.ini oder die Kommandozeile mitzugeben.

/SQLTEMPDBFILECOUNT=”8″ /SQLTEMPDBFILESIZE=”16″ /SQLTEMPDBFILEGROWTH=”256″ /SQLTEMPDBDIR=”C:\tempdb” “D:\tempdb” /SQLTEMPDBLOGFILESIZE=”256″ /SQLTEMPDBLOGFILEGROWTH=”0″ /SQLTEMPDBLOGDIR=”E:\tempdblog”

Diese oben genannten Optimierung (oder besser Vereinfachungen) machen die Installation und performance-orientierte Konfiguration des SQL Servers wesentlich komfortabler. Allerdings hat es auch einen kleinen Nachteil (*Augenzwinker*), die DBAs verlieren einen Teil ihres Einflusses, da nun jeder die Möglichkeit hat, diese Performance-Schrauben zu bedienen.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

SQL Server Backup – Performance Probleme durchs Backup ???

Ich habe einen SQL Server 2008 R2 in der Betreuung auf dem es immer wieder zu Engpässen oder besser gesagt „Performance-Löchern“ kommt, diese galt es zu finden und zu beheben. Dass das SQL Server Backup hierbei eine gewichtige Rolle spielt, habe ich zu diesem Zeitpunkt noch nicht gewußt… aber der Reihe nach.

Bei dem SQL Server handelt es sich um eine virtuelle Maschine mit 4 vCPUs und 32 GB RAM (aber 3 SQL Server Instanzen), das Betriebssystem ist ein Windows Server 2008 R2, der SQL Server 2008 R2 (10.50.6259) Standard Edition. Da wir den SQL Server damals nicht selber aufgesetzt haben, sondern aus der Betreuung anderer übernommen haben, mussten wir teilweise bei den Grundlagen der Best-Practices anfangen und konnte so mit der Umsetzung einer Vielzahl von Empfehlungen für einen stabilen und performanten Betrieb umgesetzen.

  • Max Memory angepasst
  • maxDOP und Threshold optimiert
  • lokale Sicherheits-Richtlinien angepasst
  • Backup Compression aktiviert
  • Network Packet Size erhöht
  • Optimize for ad hoc Workloads aktiviert

Aktuell sind wir dabei, die Datenbank-Files (welche bereits getrennt waren) auf neue (bessere) Platten zu verteilen, wobei wir auch auf die Blocksize bei der Formatierung geachtet haben. Bisher waren alle Platten mit dem „Default“ von einer 4k-Blocksize formatiert… nun haben wir dies für die Datenplatten auf 64k Blocksize geändert.

Vorarbeiten / Umsetzung der Best-Practices

Der Kunde ist schon zufriedener mit der Performance, wobei man sicherlich bei der Datenbank-Struktur, den Indexen und/oder den Abfragen noch eine ganze Menge Performance herausholen kann. Aber diese liegen nicht in unserer Verantwortung, so dass wir nur analysieren und Empfehlungen aussprechen können.

Vor einigen Tagen habe ich eine Video von Brent Ozar (ich glaube es kam von ihm) gesehen, in diesem Video ging es in der Hauptsache um Storage unter anderem darum, wie moderne Storage-Systeme Daten intern verteilen und welchen Einfluß dieser Algorithmus auf die Performance des SQL Servers haben kann. Unter anderem auch auf die SQL Server Backup Performance. High Level IO kommt auf Highspeed Discs und weniger wichtige Daten werden auf langsamer drehende Platten „ausgelagert“…

Was aber tun, wenn alle Daten dieser Vielzahl von Datenbanken mindestens einmal am Tag „angefasst“ werden… dann ist das Storage-System der Meinung alle Daten sind wichtig und werden ständig gebraucht… also müssen alle Daten auf Highspeed-Platten… aber nicht alle Daten passen vielleicht in eben diesen Bereich, also müssen einige Daten des SQL Servers doch wieder „ausgelagert“ werden. In meinem Fall hatten wir (auf Kundenwunsch und von uns vielleicht ein wenig blauäugig) einen House-Keeping Job eingerichtet, der einmal täglich alle (!) Datenbanken bzw deren Indexe reorganisert hat.

Dieser tägliche Rebuild/Reorg führte nicht nur dazu, dass das Storage-System ständig seinen Algorithmus für die Datenverteilung anpassen musste, sondern auch das Backup alle diese „geänderten“ Daten erneut und wiederholt sichern musste. Wie ich bereits anfangs erwähnt habe, handelt es sich bei etwa 2/3 der Datenbanken um Audit- und Archiv-Datenbanken, müssen diese tatsächlich täglich reorganisiert und somit gesichert werden?

Nun werden nur noch alle relevanten Datenbanken täglich reorgansiert, das zuständige Wartungsskript haben wir auch gegen die Maintenance-Solution von Ola Hallengren ausgetauscht, diese Skript ist wesentlich ausgereifter und intelligenter als unsere alte Lösung.

Jetzt sind wir an einem Punkt (den wir in diesem Fall als DBAs direkt beeinflussen können), an dem man eigentlich nichts mehr zusätzlich ändern kann… durch das Video stolperte ich auch noch über weitere Beiträge von Brent Ozar zum Thema Backup, die mich erneut zum Nachdenken bzw Überdenken der Situation brachten.

Optimierung und Anpassung der SQL Agent Jobs

Also habe ich mir den SQL Server nochmals angeschaut, wann welche Belastung auftritt und welche SQL Agent Jobs zu welchem Zeitpunkt laufen. Dabei fiel mir auf, dass der SQL Server den ganzen Tag über ständig irgendwelche Backups (hier TransactionLog-Backups) macht.

Visualize the timeline of your SQL jobs using Google graph and email
Visualize the timeline of your SQL jobs using Google graph and email
http://www.sqlservercentral.com/articles/Jobs/127346/

Ok, gegen die Häufigkeit kann ich in diesem Fall nichts tun, da wir vertraglich zugesichert haben eine „Maximale Datenverlust Zeit“ von 20 Minuten zu garantieren. (siehe hierzu auch meinen Beitrag über SQL Server Backup Grundlagen). Aber warum dauern diese Backups so lange, so groß können die verarbeiteten Datenmengen (auf diesem SQL Server) nicht sein…

Nach einem Studium der Beiträge von Brent Ozar zum Thema MSDB und was passiert mit der Backup-Performance, wenn man die Daten der Backup-Historie in der msdb nicht regelmäßig löscht, konnte ich zumindest für mich festhalten… „daran lag es zum Glück nicht!“. Ein anderes Skript von Brent für mich dann auf eine heiße Spur…

Wir sichern diesen Server (und die meisten der won uns betreuten SQL Server) mittels Online Backup mittels Legato Networker (Module for Applications oder den SQL Client) direkt auf einem Backup-Server. Die Anbindung an diesen Backup Server erfolgt auf jedem Server mit einer zusätzlichen Netzwerkkarte, welche eine Verbindung ins dedizierte Backup-LAN hat. Diese Netzwerkkarte hat in der Regel (zumindest bei diesem Server) eine angegebene Geschwindigkeit von 10GBit. Also sollte doch zumindest die Anbindung schnell genug sein…

Eine genauere Analyse der vorhandenen Daten aus der msdb-Datenbank und einem sehr nützlichen Skript zur Ermittlung der SQL Server Backup Leistung zeigte dann doch erstaunliche Werte.

miserabler Backup Durchsatz trotz

Durch diese neue Erkenntnis kann/muss ich nun, in Zusammenarbeit mit den Netzwerk- und Backup-Kollegen, eine Analyse dieser schlechten Backup-Performance erstellen. Liegt es am Netzwerk Richtung Backup Server oder am Backup Server selber oder am eigentlichen Backup, dass hier in der Parametrisierung etwas optimiert werden kann.

Ich werde euch auf dem Laufenden halten, wie das Thema weiter geht und ob ich den SQL Server entlasten konnte.

weitere Links und Quellen zur obigen Analyse von SQL Server Backup Problemen:

SQL Sysadmin : Clear Backup History (nibble delete)
http://sqlsolace.blogspot.com/2007/05/sql-sysadmin-clear-backup-history.html

Brent’s Backup Bottleneck: MSDB

Brent’s Backup Bottleneck: MSDB

Blitz Result: MSDB History Not Purged

MSDB History Not Purged

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

PASS Essential: Analyse eines Microsoft SQL Server auf Performanceprobleme

Aus dem Januar Newsletter der SQL PASS Deutschland:

Dauer: 1 Tag
Ort: Karlsruhe, inovex GmbH
Datum: 18. April 2016, 09.00-16.30 Uhr
Teilnehmeranzahl: mindestens 4, maximal 12
Preis: Mitglieder PASS Deutschland e.V. 299€ (inkl. MwSt.), Nicht-Mitglieder 475€ (inkl. MwSt.)
Schulungszertifikat: Ja
Anmeldung: E-Mail an registrierung@sqlpass.de
Voraussetzungen:

Sprecher:
Uwe Ricken (Twitter | Blog) verfügt über mehr als 6 Jahre Praxiserfahrung als DBA und mehr als 14 Jahre Erfahrungen als Entwickler von Datenbankmodellen.
Im Mai 2013 wurde diese Erfahrung mit der Zertifizierung zum 7. deutschen „Microsoft Certified
Master – SQL Server 2008“ gekrönt. Im Juli 2013 wurde ihm zum ersten Mal der MVP-Award von Microsoft für
seine Unterstützung der Microsoft SQL Server Community verliehen. Damit war Uwe Ricken der erste MCM +
MVP in Deutschland. Uwe Ricken ist als Sprecher zu den Themen „Database Engine Internals“, „Query Optimizing“
und „Entwicklung“ auf vielen Konferenzen in ganz Europa anzutreffen.

Inhalt
Nach Abschluss des Workshops haben Sie fundierte Kenntnisse in diesen Themen

  • „Problemzonen“ einer Installation von Microsoft SQL Server, die auftreten, wenn eine „Standardinstallation“ implementiert
    wird.
  • an Hand eines Stufenplans kann Schritt für Schritt die Analyse eines Systems durchführt werden.
  • Ergebnisse der Messungen werden in einer Bewertungsmatrix auf Schwachpunkte untersucht.

Teilnehmer Voraussetzungen:

  • Grundkenntnisse über Microsoft SQL Server
  • Umgang mit Microsoft SQL Server Management Studio

Kursinhalte:

  • Engpässe im Betriebssystem, die einen Microsoft SQL Server ausbremsen
  • Konfigurationseinstellungen einer Instanz von Microsoft SQL Server
  • TEMPDB – falsche Konfiguration und ihre Auswirkungen auf die Performance
  • PLE – Analyse und Bewertung von „Page Life Expectancy“ im Zusammenhang mit verwendeten Datenbanken
  • Analyse der Belegung des Buffer Pool pro Datenbank!
  • PFS-Contention, Locking, Blocking, Deadlocks – welche Möglichkeiten gibt es für die Beseitigung
  • Korrekte Datenbankeinstellungen (Datenbankdateien, Protokolldateien)
  • PERFMON – Einblicke in die Arbeitsweise des Microsoft SQL Server zur Performancebewertung
  • Analyse von Wait Stats zur Bewertung von vorhandenen Engpässen
  • Anforderungen an eine Kundendokumentation
    •  Gliederung der Dokumentation
    • Präsentation der Analyseergebnisse & Handlungsempfehlungen
  • An konkreten Beispielen, die mit dem eigenen Laptop (mitgebracht incl. installierter Software) ebenfalls simuliert werden
    können, werden verschiedene Engpässe demonstriert, die es zu erkennen gilt. Wenn es um die Bewertung von Analyseergebnissen
    mittels Wait Stats geht, so können solche Ergebnisse für jeden EIGENEN Server simultan im Workshop ausgewertet
    werden.

PASS Essentials werden vom PASS Deutschland e.V. veranstaltet: http://www.sqlpass.de
Allgemeine Geschäftsbedingungen (AGB): http://www.sqlpass.de/Events/AllgemeineGeschäftsbedingungenAGB.aspx

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.

Instant File Initialization – Performancegewinn möglich

Im Rahmen der wiederkehrenden Diskussionen über „Wie setzt man einen SQL Server am besten auf?“ stolpere ich regelmäßig über die (vielleicht oft unterschätzte) Möglichkeit der „Instant File Initialization“. Mir war diese bis vor 1-2 Jahren gar nicht bekannt, aber mittlerweile gehört sie bei uns zum Standard, wie wir SQL Server installieren.

Mir ist hierzu noch ein Gespräch mit einem meiner DB2-Kollegen im Kopf, als dieser gerade eine DB2-Server-Migration plante bzw testete…
Er wollte eine neue Datenbank erstellen und musste warten bis die jeweiligen Datenfiles angelegt waren… das dauerte bei 100GB großen Datenfiles nun einmal entsprechend… (oder auch nicht 😉 )

MS SQL macht hier keine Unterschied zu IBM DB2, beide Datenbank-Systeme überschreiben den neu zu belegenden Plattenplatz der neuen Datendateien oder die Erweiterungen der Datendateien im Filesystem erstmal mit Nullen, um so sicherzustellen, dass alte Fragmente von bereits gelöschten Dateien tatsächlich überschrieben wurden.
Die Datenbereiche werden somit bereinigt, in diesem Fall initialisiert.

In Sachen SQL Server hatte ich mir hierzu bis zu dem Zeitpunkt keinerlei Gedanken gemacht, aber wie der Alttag es so möchte, kommt irgendwann immer „das erste Mal“.

Wir haben auf einem SQL-2012-Cluster eine Instanz für ein Online-CMS, dieses Online-CMS legt seine Datenbanken selber an, wenn die Redakteure ein neues „Projekt“ starten.
Ursprünglich (das Online-CMS läuft schon einige Jahre) gab es bisher nie Probleme mit dem Anlegen neuer Projekte, aber anscheinend in einer neuen Kombination aus SQL Version (Umzug aufs SQL-Cluster) und neuer CMS-Version kam es nun zu Problemen, die Redakteure beschwerten sich darüber, dass keine neuen Projekte angelegt werden könnten…

Die Überprüfungen ergaben dann auf Seiten des SQL-Servers nie eine Meldung im SQL-ErrorLog, keine Meldungen über SQL-Probleme in den Logfiles des Online-CMS, auch der Eventviewer half nicht bei der Suche. Die Anlage einer leeren Datenbank für eben dieses CMS über das SQL Management Studio zeigte auch keinerlei Abweichungen vom Standard-Verhalten, alles war wie erwartet. Die manuelle Anlage half aber nichts, weil das CMS damit nichts anfangen konnte (keine interne GUID, keine Tabellen und sonstige internen Anpassungen).
Eine Analyse mit dem Blitz-Skript von Brent Ozar ergab dann einen Hinweis auf eine verringerte Plattenperformance, diesem bin ich erstmal nachgegangen.
Die Storage-Kollegen waren behilflich und haben mir zusätzlich neue SSD-Platten angehängt bzw virtuelle Platten aus dem SSD-Plattenbereich der Storagebox zugewiesen, aber auch mit diesen schnelleren SSD-Platten war es nicht möglich ein neues Projekt anzulegen.
Mit dem Software-Herstellersupport wurde ebenfalls sämtliche Appliaktionsparameter durchgegangen, die irgendwie die Projektanlage beeinflussen könnten… insbesondere Timeouts für die Funktionen der Projekterstellung und die Parameter der Datenbankanbindung.
Dann habe ich mich weiter und intensiver mit dem Thema auseinander gesetzt und bin auf die Möglichkeit(en) der Instant File Initialization gekommen. Meine ersten manuellen Test auf dem Test-Cluster bestätigten meine Vermutungen und ließen mich hoffen, die manuelle Neuanlage von Datenbanken mit unterschiedlichen Datenbank-Größen zwischen 256MB und 5GB geschah um Vergleich zu vorher gerade zu gigantisch schnell.

Also einen neuen Termin mit dem Projektteam, den Redakteuren vereinbart. Den Parameter bzw die Berechtigung entsprechend gesetzt, die SQL Server Ressource auf dem SQL Cluster durchgestartet und die Redakteure gebeten erneut die Anlage eines Projektes zu versuchen. Und siehe da, das Projekt ließ sich tatsächlich anlegen. Die Datenbank samt aller Strukturen wurde angelegt und war im Online-CMS als neues Projekt verfügbar.

Zahlen, die genau meine Vermutungen und Versuche bestätigen, fand ich in einem Blogbeitrag von Uwe Ricken, der sich ebenfalls intensiver mit dem Thema zu Instant File Initialization auseinander gesetzt hatte.

Auf den MSDN-Seiten und im Blog von Uwe Ricken fand ich dann auch Einschränkungen und weitergehende Erläuterungen dazu.

Dadurch dass man den Service-User des SQL Server Dienstes das lokale Recht „Perform volume maintenance tasks“ aktiviert man die „Instant File Initialization“, dies muss im Cluster auf jedem einzelnen Knoten durchgeführt werden.

Durch das Aktivieren (Berechtigung dem SQL Service User erteilen) der Instant File Initialization wird verhindert, dass der SQL Server erst einmal den zu allokorierenden Datenbereich mit Nullen voll schreiben muss, denn es nur ein HighwaterMark gesetzt.
Für das Setzen der HighwaterMark (SetFileValidData) wird das Recht SE_MANAGE_VOLUME_NAME benötigt, daher muss der SQL Service User dieses Recht erhalten, der SQL Server zum Abschluss einmal durchgestartet werden.

Das Füllen der zu allokierenden Datenbereiche mit Nullen im Rahmen der Instant File Initialization bedeutet, dass jedes Byte des neuen zugewiesenen Platzes mit Nullwerten (0x0) überschrieben wird. Dieses Verhalten verhindert so Probleme mit alten Daten, die zuvor in den gleichen physikalischen NTFS Clustern gespeichert wurde. Das Füllen der Daten geschieht somit aus Gründen der Sicherheit, Stabilität und Kontinuität.

Leider ist diese „Beschleunigung“ nur bei den folgenden Datenbank-Operationen zulässig und nutzbar:

  • Anlegen einer Datenbank (Create Database)
  • Autogrowth einer Datendatei einer Datenbank
  • Restore eines Datenbank Backups
  • die Datenbank darf nicht mittels TDE verschlüsselt sein

Wie Sie selber sehen können, nützt die Funktion bei TransaktionsLogs leider nichts, denn TransaktionsLogs müssen immer erst mit Nullen vollgeschrieben werden. Ohne die Null-Initialisierung der Transaktions-Logdatei, würde der Crash-Recovery-Prozess keine Ahnung haben, wo er zu stoppen, wenn die Protokolldatei abgearbeitet wurde. Der Crash Recovery Prozess stoppt, wenn er feststellt, dass Null-Werte in der Kopfzeile des nächsten Protokollsatz verarbeitet werden.

Desweiteren benötigt man (oder der SQL Server) im Rahmen eines Restores keine leeren Datenbereiche, da diese ja schon so groß dimensioniert, dass sie nur so groß sind, wie tatsächlich auch Daten in der Datenbank vorhanden sind. Daher benötigt der Restore kein vorheriges Befüllen/Überschreiben des Datenbereiches.

Überprüfen, ob Instant File Initialization aktiviert ist:

  • Aktivieren des Trace Flags 3004 (DBCC TRACEON(3004,-1);)
  • Aktivieren des Trace Flags 3605 (DBCC TRACEON(3605,-1);)
  • Anlegen einer Testdatenbank und dann
  • im SQL Server Error Log nachschauen.

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.