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
http://www.brentozar.com/blitz/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.

Ä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.