Best Practices für SQL Server-Instanzen

Sie können verschiedene Best Practices anwenden, um Instanzen der Compute Engine zu optimieren, auf denen Microsoft SQL Server ausgeführt wird. Informationen zum Einrichten einer leistungsfähigen SQL Server-Instanz finden Sie unter Leistungsfähige 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 für den Parameter remoteip an der Stelle von [LOCAL_SUBNET] eine durch Kommas getrennte Liste von IP-Adressen ohne Leerzeichen 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-Anwendungs-Image enthält eine SQL Server-Regel für die Windows-Firewall. Diese Regel enthält nur wenige Einschränkungen. Möglicherweise sollten Sie sie 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 Netzwerktraffic weder das Netzwerk noch die verbundenen 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.

Der flüchtige Charakter lokaler SSD-Technologie macht sie zu einem schlechten Kandidaten zur Verwendung für wichtige 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 nichtflüchtigen SSD-Speicher um eine beträchtliche Anzahl von Ein-/Ausgabevorgängen entlastet. Weitere Informationen zur Einrichtung dieses Szenarios finden Sie unter TempDB einrichten.

Parallele Abfrageverarbeitung

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

Es empfiehlt sich, die Anzahl der Server-CPUs als Standardeinstellung für max degree of parallelism 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, den Standardwert zu ändern, doch lohnt es sich, ihn zu beobachten und wenn nötig beim Belastungstest schrittweise um 5 zu erhöhen. 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 Datenbank einzeln fest.

Verschiedene Datenbanken können unterschiedliche Anforderungen im Hinblick auf Parallelität haben. Sie können diese Einstellungen global festlegen und Max DOP für jede Datenbank einzeln anpassen. 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.

Das ungeplante Wachstum des Transaktionslogs stellt eine der am häufigsten übersehenen Ursachen für Leistungsverluste und zeitweilige Verlangsamungen dar. Wenn die Datenbank für die Verwendung des Wiederherstellungsmodells Full konfiguriert ist, können Sie ihren Zustand an einem beliebigen Zeitpunkt wiederherstellen, aber die Transaktionslogs werden schneller gefüllt. Wenn das Transaktionslog voll ist, wird die Datei standardmäßig von SQL Server vergrößert, um freien Platz zum Schreiben weiterer Transaktionen zu schaffen. Bis dieser Vorgang abgeschlossen ist, wird jegliche Aktivität in der Datenbank blockiert. SQL Server vergrößert jede Logdatei auf Grundlage der Einstellungen MAXSIZE (Maximale Dateigröße) und FILEGROWTH (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 FILEGROWTH (Dateiwachstum) beträgt standardmäßig 10 % der aktuellen Größe der Logdatei. 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 ein regelmäßiges Back-up des Transaktionslogs.

Unabhängig von den Einstellungen für maximale Größe und Wachstum sollten regelmäßige Back-ups des Transaktionslogs geplant werden. Dabei werden standardmäßig alte Logeinträ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 Logdatei 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 aus 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.

Back-ups machen

Best Practice: Stellen Sie einen Plan für Back-ups auf und führen Sie sie regelmäßig durch.

Ola Hallengrens Website bietet gute Informationen zum Einstieg in die Implementierung eines zuverlässigen Back-up- 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.

Monitoring

Best Practice: Verwenden Sie Stackdriver Monitoring.

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

Sie können die Informationen, die Sie überwachen möchten, mithilfe von Datenerfassungsfunktionen im Detail festlegen und an das integrierte Management 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 für das Staging und die Transformation 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-Vorgänge auf dem Laufwerk und den CPU-Verbrauch durch das Laden im Bulk zu reduzieren und gleichzeitig die Ausführung von Batchjobs 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 des im Bulk zu ladenden Datasets durchzuführen, bevor Sie es in die Produktionsdatenbank einfügen. Falls genügend Platz vorhanden ist, können Sie diese neue Datenbank auch auf einem lokalen SSD-Laufwerk ablegen. 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 Back-up-Job für die Produktionsdaten nicht die riesige Menge an Bulk-Operationen im Transaktionslog berücksichtigen muss und deshalb kleiner ausfällt und schneller ausgeführt wird.

Einrichtung überprüfen

Best Practice: Testen Sie Ihre Konfiguration, um zu prüfen, ob sie die erwartete Leistung erbringt.

Wenn Sie ein neues System einrichten, sollten Sie immer 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 zu verwenden und im Gegenzug die zusätzlichen Laufwerk-E/A-Vorgänge, die zum Lesen und Schreiben der größeren Blöcke nötig wären, zu eliminieren. Als Faustregel gilt, je weniger Laufwerk-E/A-Vorgänge durchgeführt werden, desto schneller läuft das System. Anweisungen zum Einschä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 nicht modifizierte 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 modifizierten Seite, die zum Speichern der Änderungen auf das Laufwerk geleert werden muss. Ist die Datenbank größer als der verfügbare Arbeitsspeicher, kann die starke Belastung des Pufferpools dazu führen, dass nicht modifizierte Seiten verworfen werden. Ist dies der Fall, muss das System, wenn das nächste Mal auf die verworfenen Daten zugegriffen werden muss, diese vom Laufwerk lesen.

Mit der Pufferpoolerweiterung können Sie nicht modifizierte 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 nicht modifizierten Seiten auf der lokalen SSD zu, was schneller als das Abrufen der Daten auf dem normalen Laufwerk 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