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.

Ändern des Autogrowth Parameters aller SQL Server Datenbanken

Gestern stand ich mal wieder vor einem SQL Server mit der Aufgaben, “… es gab Störungen in der Anwendung, kannst du mal auf dem SQL Server schauen ob es vielleicht an der Datenbank lag…”

Was macht man in solch einem Fall?
Natürlich schaut man sich erstmal alle Logs an, das Windows Eventlog, das SQL Server Log…
Man führt zahlreiche Statements aus, um vielleicht einen Hinweis auf Probleme und/oder Engpässe zu finden…

So auch bei mir… der Blick in den Eventviewer und das SQL Log ergaben keinerlei Auffälligkeiten bezüglich eines Ausfalles des SQL Servers, irgendeines Problem mit einem Datenbank Backup oder überhaupt ein “Mucken”. War ja wieder klar 😉

Also tiefer schauen, vielleicht lassen sich aus dem Cache oder über DMVs noch relevante Daten herauslesen. Wie bereits in einem früheren Beitrag geschrieben, nutze ich gerne die großartigen Skripte von Brent Ozar, wie auch jetzt.

Also mal schnell mit der Stored Procedure “sp_blitz” über den SQL Server geschaut…

Das Ergebnis war “kilometerlang”, also erstmal die irrelevanten Punkte über die Parameter der Stored Procedure ausschalten, denn was interessiert mich in solch einem Fall ob es suboptimal ist zahlreiche Trigger in einer Datenbank zu haben oder nicht. Viel mehr geht es jetzt darum Anzeichen für Aussetzer oder Engpässe des SQL Servers und seiner Datenbanken zu ermitteln.

Aber auch hier konnte ich keine wirklichen Auffälligkeiten erkennen, nur das bei 174 von 300 SQL Datenbanken die Autogrowth Einstellung auf prozentuale anstatt auf fixen Werten standen.

100 Performance File growth set to percent AdventureWorks2008R2 http://brentozar.com/go/percentgrowth The [AdventureWorks2008R2] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100 Performance File growth set to percent AdventureWorksDW2008R2 http://brentozar.com/go/percentgrowth The [AdventureWorksDW2008R2] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100 Performance File growth set to percent master http://brentozar.com/go/percentgrowth The [master] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100 Performance File growth set to percent model http://brentozar.com/go/percentgrowth The [model] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100 Performance File growth set to percent msdb http://brentozar.com/go/percentgrowth The [msdb] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100 Performance File growth set to percent tempdb http://brentozar.com/go/percentgrowth The [tempdb] database is using percent filegrowth settings. This can lead to out of control filegrowth.

beispielhaft

SQL Datenbank Eigenschaften Autogrowth

Da diese Einstellung schnell zu Problemen mit dem vorhandenen Plattenplatz führen können, empfehlen wir (wie zahlreiche andere Seiten/Dienstleister) diese Werte entsprechend anzupassen.
Aber wie, wenn man nicht 174 SQL Datenbanken händisch anpassen möchte?! Na klar, per TSQL.

Brainstorming zum SQL Statement

– ich brauche das ALTER Statement
– einen SELECT das alle betroffenen Datenbanken ermittelt
– einen Loop der durch das Ergebnis läuft
– Datenbank-Name, den logischen Dateiname für das ALTER-Statement,  FileGrowth Eigenschaften der Datenbanken

Und schon das Management Studio geöffnet und los gehts…

Wo bekomme ich die passenden Informationen her? Richtig aus der sys.master_files, also ein SELECT-Statement welches nur den Datenbank-Namen, den logischen Dateinamen und die Information ob prozentualer Autogrowth aktiviert ist liefert.

SELECT DB_NAME(files.database_id),
name,
physical_name
FROM sys.master_files files
WHERE files.type IN (0,
1)
AND files.growth != 0
AND is_percent_growth = 1
AND files.database_id > 4

Das Ergebnis dieser SQL Abfrage schreibe ich der Einfachheit halber in eine temporäre Tabelle, damit lässt es sich besser arbeiten.

Create Table ##temp ( DatabaseName sysname, logical_name sysname, physical_name nvarchar(500) )

Temporäre Tabelle und SELECT Statement werden entsprechend mittels INSERT … INTO befüllt miteinander kombiniert:

INSERT INTO ##temp (DatabaseName, logical_name, physical_name)
SELECT DB_NAME(files.database_id),
name,
physical_name
FROM sys.master_files files
WHERE files.type IN (0,1)
AND files.growth != 0
AND is_percent_growth = 1
AND files.database_id > 4

Nun kommt der Schleife, anhand derer ich jede Zeile in der temporären Tabelle ermittel und explizit für diese dann ein SQL Statement zusammensetze, welches ich mir erstmal nur ausgeben lasse.
Ich lasse mir üblicherweise erstmal alle Statements als Text ausgeben, dann kann man nochmal schnell einen Blick drüber werfen und notfalls einzelne Zeilen optimieren.

So wird dann ein ganzes Skript mit Deklarationen und Aufräumen daraus:

DECLARE @DatabaseName nvarchar(100)
DECLARE @Logical_Name nvarchar(100)
DECLARE @SQL nvarchar(4000)
 
-- Erstelle einen Temp-Table
Create Table ##temp (
DatabaseName sysname,
logical_name sysname,
physical_name nvarchar(500)
)
 
-- Ermitteln aller relevanten Datenbanken
Insert Into ##temp (DatabaseName, logical_name, physical_name)
select DB_NAME(files.database_id), name, physical_name from sys.master_files files where files.type in (0,1) and files.growth != 0 and is_percent_growth = 1 and files.database_id > 4
 
-- Durch das Ergebnis loopen
while exists (Select * From ##temp)
begin
Set NOCOUNT ON
select top 1 @DatabaseName = DatabaseName, @Logical_Name = logical_name from ##temp order by DatabaseName asc
 
SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = N''' + @Logical_Name + ''', FILEGROWTH = 512MB ) GO'
print @SQL
 
delete ##temp where DatabaseName = @DatabaseName
 
end
 
drop table ##temp

Jetzt nur noch das Ergebnis (siehe unten) in ein neues Query Fenster kopieren und ausführen.

ALTER DATABASE [AdventureWorks2008R2] MODIFY FILE ( NAME = N'AdventureWorks2008R2_Log', FILEGROWTH = 512MB ) GO
ALTER DATABASE [AdventureWorksDW2008R2] MODIFY FILE ( NAME = N'AdventureWorksDW2008R2', FILEGROWTH = 512MB ) GO
ALTER DATABASE [AM_Reporting_SQL] MODIFY FILE ( NAME = N'AM_Reporting_SQL_Log', FILEGROWTH = 512MB ) GO

Fertig ist das Skript zum Ändern des Autogrowth Parameters aller SQL Server Datenbanken.

Im Zuge dieser Optimierung bin ich noch über eine Ungereimtheit auf diesem SQL Server gestoßen dazu später mehr…

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.