Best Practices für SQL-Serverinstanzen

Sie können verschiedene Best Practices anwenden, um Instanzen der Google Compute Engine zu optimieren, auf denen Microsoft SQL Server ausgeführt wird. Informationen zum Einrichten einer Hochleistungs-SQL Server-Instanz finden Sie unter Hochleistungs-SQL Server-Instanz erstellen.

Windows konfigurieren

Die Konfigurationsthemen in diesem Kapitel befassen sich mit der Optimierung des Betriebssystems Microsoft Windows für eine erhöhte Leistung mit SQL Server bei der Ausführung auf Compute Engine.

Windows-Firewall einrichten

Best Practice: Verwenden Sie die Firewall mit erweiterter Sicherheit unter Windows Server und geben Sie die IP-Adressen der Clientcomputer an.

Die Windows-Firewall mit erweiterter Sicherheit ist eine wichtige Sicherheitskomponente in Windows Server. Wenn Sie die SQL Server-Umgebung so einrichten, dass eine Verbindung von anderen Client-Rechnern zur Datenbank hergestellt werden kann, sehen Sie in der Konfiguration der Firewall eingehenden Datenverkehr vor:

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=[LOCAL_SUBNET]

Bei der Verwendung dieser Firewallregel hat es sich bewährt, die IP-Adressen der Clientrechner anzugeben. Geben Sie eine durch Komma getrennte Liste von IP-Adressen ohne Leerzeichen für den Parameter remoteip an der Stelle von [LOCAL_SUBNET] an. Beachten Sie außerdem, dass sich der Pfad für den Parameter program in Abhängigkeit von der verwendeten Version von SQL Server ändern kann.

Das SQL Server-Anwendungsimage enthält eine SQL Server-Regel für die Windows-Firewall. Diese Regel gilt nahezu uneingeschränkt. Es kann sich deshalb anbieten, sie zu deaktivieren, bevor Sie Ihr System für die Produktion freigeben.

Netzwerkverbindungen abstimmen

Best Practice: Verwenden Sie die Standardnetzwerkeinstellungen des Betriebssystems.

Unter den meisten Betriebssystemen sind die Netzwerkeinstellungen standardmäßig für Verbindungen kleiner Computer mit mäßig schnellen Netzwerken konfiguriert. In den meisten Fällen sind diese Einstellungen ausreichend. Die konservativen Standardeinstellungen sorgen außerdem dafür, dass der Netzwerkverkehr weder das Netzwerk noch die angeschlossenen Computer überlastet.

In Compute Engine sind die VM-Instanzen an ein von Google konzipiertes Netzwerk angeschlossen, das eine hohe Kapazität und Leistung bietet. Die physischen Server, auf denen Ihre Compute Engine-Instanzen ausgeführt werden, sind gezielt für die volle Nutzung dieser Netzwerkkapazität optimiert. Die virtuellen Netzwerktreiber in den Instanzen sind ebenso optimiert. Deshalb sind die Standardwerte für die meisten Fälle ausreichend.

Virenschutz installieren

Best Practice: Befolgen Sie die Empfehlungen von Microsoft für Virenschutzsoftware.

Wenn Sie Windows ausführen, sollten Sie eine Virenschutzsoftware verwenden. Malware und Softwareviren stellen eine beträchtliche Gefahr für jedes System in einem Netzwerk dar und Virenschutzsoftware ist eine einfache Maßnahme zur Schadensbegrenzung, die Sie zum Schutz Ihrer Daten einsetzen können. Wenn die Virenschutzsoftware jedoch nicht richtig konfiguriert ist, kann sie sich negativ auf die Datenbankleistung auswirken. Microsoft gibt Empfehlungen für die Wahl der passenden Virenschutzsoftware.

Leistung und Stabilität optimieren

In diesem Abschnitt erfahren Sie, wie Sie die Leistung von SQL Server in Compute Engine optimieren können und welche betrieblichen Aktivitäten eine problemlose Ausführung fördern.

Daten- und Log-Dateien auf eine neue Festplatte verschieben

Best Practice: Verwenden Sie für Log- und Datendateien einen separaten nichtflüchtigen SSD-Speicher.

Standardmäßig ist beim vorkonfigurierten Image für SQL Server alles auf dem nichtflüchtigen Bootspeicher installiert, der als Laufwerk "C:\" bereitgestellt wird. Prüfen Sie das Hinzufügen eines sekundären nichtflüchtigen SSD-Speichers für das Verschieben der Log- und Datendateien auf ein neues Laufwerk.

Lokale SSD zur Steigerung der IOPS einsetzen

Best Practice: Erstellen Sie neue SQL Server-Instanzen mit einer oder mehreren lokalen SSDs für das Speichern der tempdb- und Windows-Auslagerungsdateien.

Die Kurzlebigkeit der lokalen SSD-Technologie macht sie zu einem schlechten Kandidaten zur Verwendung für Ihre wichtigen Datenbanken und Dateien. Die tempdb- und die Windows-Auslagerungsdatei sind jedoch temporäre Dateien. Dadurch sind sie hervorragende Kandidaten für die Verschiebung auf eine lokale SSD. Auf diese Weise werden die persistenten SSD-Festplatten um eine beträchtliche Anzahl von Ein-/Ausgabe-Vorgängen entlastet. Weitere Informationen zur Einrichtung dieses Szenarios finden Sie hier.

Parallele Abfrageverarbeitung

Best Practice: Legen Sie für max degree of parallelism den Wert 8 fest.

Es empfiehlt sich, als Standardeinstellung für max degree of parallelism die Anzahl der CPUs auf dem Server zu verwenden. Ab einem gewissen Punkt beansprucht es jedoch sehr viel mehr Zeit, eine Abfrage in 16 oder 32 Blöcke aufzuteilen, jede auf einer anderen vCPU auszuführen und dann ein konsolidiertes Ergebnis zu erzeugen, als diese Abfrage auf nur einer vCPU auszuführen. In der Praxis erweist sich 8 als guter Standardwert.

Best Practice: Achten Sie auf CXPACKET-Wartevorgänge und erhöhen Sie cost threshold for parallelism schrittweise.

Diese Einstellung geht Hand in Hand mit max degree of parallelism. Jede Einheit stellt eine Kombination aus CPU- und E/A-Arbeit dar, die erforderlich ist, um eine Abfrage mit einem seriellen Ausführungsplan durchzuführen, bevor diese für einen parallelen Ausführungsplan infrage kommt. Der Standardwert ist 5. Wir geben zwar keine spezifische Empfehlung zum Ändern des Standardwerts, doch lohnt es sich, ihn zu beobachten und wenn nötig beim Belastungstest eine schrittweise Erhöhung um 5 durchzuführen. Ein wichtiger Hinweis darauf, dass dieser Wert zu niedrig eingestellt sein könnte, sind Wartezeiten vom Typ CXPACKET. Das Vorhandensein von Wartezeiten des Typs CXPACKET bedeutet zwar nicht an sich, dass diese Einstellung geändert werden muss, ist aber ein guter Ausgangspunkt.

Best Practice: Achten Sie auf Wartezeiten unterschiedlicher Typen und passen Sie die globalen Einstellungen für die parallele Verarbeitung an oder legen Sie diese für jede einzelne Datenbank fest.

Verschiedene Datenbanken können unterschiedliche Erfordernisse im Hinblick auf Parallelität haben. Sie können diese Einstellungen global festlegen und Max DOP für jede Datenbank einzeln setzen. Sie sollten Ihre spezifischen Arbeitslasten beobachten, auf Wartezeiten achten und die Werte dann entsprechend anpassen.

Auf der Website SQLskills finden Sie einen hilfreichen Leitfaden zum Thema Leistung, der Wartezeitstatistiken innerhalb der Datenbank behandelt. Mithilfe des Leitfadens können Sie ermitteln, wo Wartezeiten entstehen und wie die Verzögerungen im Zaum gehalten werden können.

Umgang mit Transaktions-Logs

Best Practice: Überwachen Sie das Wachstum des Transaktions-Logs auf Ihrem System. Es kann sich anbieten, die automatische Vergrößerung zu deaktivieren und die Protokolldatei auf eine feste Größe auf der Grundlage des durchschnittlichen täglichen Log-Wachstums zu setzen.

Die ungeplante Vergrößerung des Transaktions-Logs stellt eine der am häufigsten übersehenen Ursachen für Leistungsverlust und zeitweilige Verlangsamungen dar. Wenn die Datenbank für die Verwendung des Wiederherstellungsmodells Full konfiguriert ist, kann ihr Zustand zu einem beliebigen Zeitpunkt wiederhergestellt werden, aber die Transaktions-Logs füllen sich schneller. Wenn das Transaktions-Log voll ist, wird die Datei standardmäßig von SQL Server um freien Platz zum Schreiben weiterer Transaktionen vergrößert. Bis dieser Vorgang abgeschlossen ist, wird jegliche Aktivität in der Datenbank blockiert. SQL Server vergrößert jede Log-Datei auf der Grundlage der Einstellungen Maximum File Size (Maximale Dateigröße) und File Growth (Dateiwachstum).

Wenn die Datei ihre maximal zulässige Größe erreicht hat und nicht weiter wachsen kann, wird vom System der Fehler 9002 ausgegeben und die Datenbank in den schreibgeschützten Modus geschaltet. Wenn die Datei wachsen kann, wird die Dateigröße von SQL Server erweitert und der freie Platz ausgenullt. Die Einstellung für File Growth (Dateiwachstum) beträgt standardmäßig 10 % der aktuellen Größe der Log-Datei. Im Sinne der Leistung ist dies keine gute Standardeinstellung, da die benötigte Zeit für das Erstellen des neuen, freien Platzes mit dem Dateiwachstum zunimmt.

Best Practice: Planen Sie eine regelmäßige Sicherung des Transaktions-Logs ein.

Unabhängig von den Einstellungen für maximale Größe und Wachstum sollten regelmäßige Sicherungen des Transaktions-Logs eingeplant werden. Dabei werden standardmäßig alte Log-Einträge gelöscht, damit das System diesen Platz in der Datei wieder nutzen kann. Diese einfache Wartungsmaßnahme kann Leistungseinbrüche zu Spitzenbelastungszeiten verhindern.

Virtuelle Log-Dateien optimieren

Best Practice: Überwachen Sie die Vergrößerung der virtuellen Log-Dateien und ergreifen Sie Maßnahmen zum Schutz vor einer Fragmentierung der Log-Dateien.

Die physische Transaktions-Log-Datei ist in virtuelle Log-Dateien (VLF) gegliedert. Neue VLFs werden immer dann erzeugt, wenn die physische Transaktions-Log-Datei wachsen muss. Wenn Sie die automatische Vergrößerung nicht deaktiviert haben und die Dateien zu häufig vergrößert werden, entstehen zu viele VLFs. Diese Aktivität kann zu einer Fragmentierung der Log-Dateien führen, die mit der Festplattenfragmentierung vergleichbar ist und sich negativ auf die Leistung auswirken kann.

Mit SQL Server 2014 wurde ein Algorithmus eingeführt, mit dem sich auf effizientere Weise bestimmen lässt, wie viele VLFs während der automatischen Vergrößerung erzeugt werden. Im Allgemeinen erzeugt SQL Server bei einem Wachstum von weniger als 1/8 der Größe der aktuellen Log-Datei eine VLF innerhalb des neuen Segments. Zuvor galt, dass bei einem Wachstum zwischen 64 MB und 1 GB 8 VLFs und bei einem Wachstum von mehr als 1 GB 16 VLFs erzeugt wurden. Mit dem nachfolgenden TSQL-Skript können Sie überprüfen, wie viele VLFs derzeit für eine Datenbank bestehen. Wenn mehrere Tausend Dateien vorhanden sind, empfiehlt es sich, die Log-Datei manuell zu verkleinern und die Größe neu zu bestimmen.

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

Weitere Informationen über VLFs finden Sie auf Brent Ozars Website.

Indexfragmentierung vermeiden

Best Practice: Führen Sie eine regelmäßige Defragmentierung der Indexe für die Tabellen aus, die am häufigsten geändert werden.

Fragmentierte Indexe in Ihren Tabellen können zu einer schlechten Leistung aller Abfragen führen, die diese Indexe verwenden. Ein regelmäßiger Wartungsplan sollte die Reorganisierung der Indexe Ihrer am häufigsten modifizierten Tabellen vorsehen. Sie können das folgende T-SQL-Skript für eine Datenbank ausführen, um deren Indexe und Fragmentierung in Prozent auszugeben. Im Beispielergebnis sehen Sie, dass der Index PK_STOCK zu 95 % fragmentiert ist. Ersetzen Sie in der folgenden "SELECT"-Anweisung "[YOUR_DB]" durch den Namen Ihrer Datenbank:

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[YOUR_DB]'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

Wenn Indexe zu stark fragmentiert sind, können Sie diese mit einem einfachen ALTER-Skript neu organisieren. Das folgende Beispielskript gibt die ALTER-Anweisungen aus, die für die einzelnen Indexe Ihrer Tabellen ausgeführt werden können.

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

Wählen Sie aus dem Ergebnis die Tabellen mit der höchsten Fragmentierung und führen Sie diese Anweisungen dann schrittweise aus. Es kann empfehlenswert sein, dieses oder ein anderes Skript als Aufgabe Ihres regelmäßigen Wartungsplans vorzusehen.

Sicherungen durchführen

Best Practice: Stellen Sie einen Plan für Sicherungen auf und führen Sie regelmäßig Sicherungen durch.

Ola Hallengrens Website bietet gute Informationen zum Einstieg in die Implementierung eines zuverlässigen Sicherungs- und Wartungsplans.

Achten Sie bei der Durchführung regelmäßiger Datenbanksicherungen darauf, nicht zu viele persistente Festplatten-IOPS zu verbrauchen. Verwenden Sie die lokale SSD, um die Sicherungen durchzuführen, und übertragen Sie diese dann mittels Push in einen Cloud Storage-Bucket.

Überwachung

Best Practice: Verwenden Sie Stackdriver Monitoring.

Sie können den Stackdriver Monitoring-Agent für Microsoft Windows installieren, um mehrere Datenpunkte für die Überwachung in das Stackdriver-System zu senden.

Sie können die Informationen, die Sie überwachen möchten, mithilfe von Datenerfassungsfunktionen im Detail festlegen und an das integrierte Verwaltungs-Data Warehouse senden. Die Managementdatenbank kann auf dem überwachten Server ausgeführt werden. Anderenfalls können die Daten an eine weitere SQL Serverinstanz gestreamt werden, auf der die Datenbank läuft.

Daten im Bulk laden

Best Practice: Verwenden Sie eine separate Datenbank zum Bereitstellen und Transformieren von Bulk-Daten, bevor Sie diese auf Produktionsserver verschieben.

Wahrscheinlich müssen Sie regelmäßig oder zumindest einmalig große Datenmengen in Ihr System laden. Dies ist ein ressourcenhungriger Vorgang und beim Laden im Bulk kann der Grenzwert für persistente Laufwerk-IOPS erreicht werden.

Es gibt eine einfache Möglichkeit, die Anzahl der E/A-Prozesse auf dem Laufwerk und die CPU-Belastung durch das Laden im Bulk zu reduzieren und gleichzeitig die Ausführung von Stapelaufträgen zu beschleunigen. Die Lösung besteht darin, eine vollkommen separate Datenbank zu erstellen, die das Wiederherstellungsmodell Simple verwendet, und dann mit dieser Datenbank das Staging und die Transformation der im Bulk zu ladenden Daten durchzuführen, bevor Sie diese in die Produktionsdatenbank einfügen. Falls genügend Platz vorhanden ist, können Sie diese neue Datenbank auch auf einem lokalen SSD-Laufwerk betreiben. Dadurch werden der Ressourcenverbrauch der Bulk-Operationen verringert und die Zeit für die Durchführung der Jobs verkürzt. Ein weiterer Vorteil besteht darin, dass der Sicherungsjob für die Produktionsdaten nicht die riesige Menge an Bulk-Operationen im Transaktionsprotokoll berücksichtigen muss und deshalb kleiner ausfällt und schneller ausgeführt wird.

Einrichtung überprüfen

Best Practice: Testen Sie Ihre Konfiguration, um sich zu vergewissern, dass sie die erwartete Leistung erbringt.

Wenn Sie ein neues System einrichten, sollten Sie stets eine Überprüfung der Konfiguration einplanen und verschiedene Leistungstests durchführen. Diese gespeicherte Prozedur ist eine ausgezeichnete Ressource für die Beurteilung Ihrer SQL Server-Konfiguration. Nehmen Sie sich später ein wenig Zeit, um sich über die Konfigurations-Flags zu informieren, und führen Sie die Prozedur aus.

SQL Server Enterprise Edition optimieren

Die Liste der zusätzlichen Fähigkeiten von SQL Server Enterprise Edition im Vergleich zur Standard Edition ist lang. Falls Sie eine vorhandene Lizenz zu GCP migrieren, könnte die Implementierung bestimmter Leistungsoptionen für Sie von Interesse sein.

Komprimierte Tabellen verwenden

Best Practice: Aktivieren Sie die Tabellen- und Indexkomprimierung.

Es mag unlogisch klingen, dass komprimierte Tabellen die Systemleistung erhöhen können, doch genau das ist meistens der Fall. Der Kompromiss besteht darin, eine kleine Menge von CPU-Zyklen auf die Komprimierung der Daten und Beseitigung der zusätzlichen Laufwerk-E/A-Prozesse zu verwenden, die zum Lesen und Schreiben der größeren Blöcke erforderlich sind. Als Faustregel gilt, je weniger Laufwerk-E/A, desto schneller läuft das System. Anweisungen zum Schätzen und Aktivieren der Tabellen- und Indexkomprimierung finden Sie auf der MSDN-Website.

Pufferpoolerweiterung aktivieren

Best Practice: Beschleunigen Sie den Datenzugriff mithilfe der Pufferpoolerweiterung.

Der Pufferpool ist der Bereich auf dem System, in dem saubere Seiten gespeichert werden. Das heißt in einfachen Worten, dass Kopien der Daten gespeichert werden, die den Zustand auf der Festplatte widerspiegeln. Wenn sich die Daten im Speicher ändern, spricht man von einer schmutzigen Seite, die zum Speichern der Änderungen auf die Festplatte geleert werden muss. Ist die Datenbank größer als der verfügbare Arbeitsspeicher, kann die starke Belastung des Pufferpools dazu führen, dass saubere Seiten verworfen werden. Ist dies der Fall, muss das System von der Festplatte lesen, wenn das nächste Mal auf die verworfenen Daten zugegriffen werden muss.

Mit der Pufferpoolerweiterung können Sie saubere Seiten per Push auf eine lokale SSD verschieben, anstatt sie zu verwerfen. Dies funktioniert nach demselben Prinzip wie bei virtuellem Arbeitsspeicher, nämlich durch Auslagerung. Dabei greifen Sie auf die sauberen Seiten auf der lokalen SSD zu, was schneller als das Abrufen der Daten auf der normalen Festplatte ist.

Diese Technik ist zwar nicht annähernd so schnell wie ein System mit ausreichendem Arbeitsspeicher, kann aber für eine geringe Steigerung des Datendurchsatzes sorgen, wenn nicht genügend Arbeitsspeicher zur Verfügung steht. Weitere Informationen zu Pufferpoolerweiterungen sowie verschiedene Benchmarkingergebnisse finden Sie auf Brent Ozars Website.

Weitere Informationen

Hat Ihnen diese Seite weitergeholfen? Teilen Sie uns Ihr Feedback mit:

Feedback geben zu...

Compute Engine-Dokumentation