Best Practices für SQL Server, Teil 1: Konfiguration

Best-Practice-Anleitungen für Microsoft SQL Server zu lesen kann ziemlich mühsam sein. Nachdem wir uns durch technische Anleitungen, Best-Practice-Guides, TechNet-Artikel und Blogtexte von SQL-Experten gekämpft haben, sind wir zu dem Schluss gekommen, dass ein leicht verständlicher Text zu diesem Thema hilfreich wäre.

Unser Ziel ist es dabei nicht, auf jede einzelne Einstellung einzugehen, sondern einige Dinge zu erklären, die bei der Einrichtung sowie bei Leistungsproblemen hilfreich sind.

Gemeinsam genutzte versus dedizierte Instanzen

Wenn eine Anwendung eine große Anzahl von Schemata/gespeicherten Prozeduren nutzt, kann das unter Umständen zu Leistungseinbußen bei den Anwendungen führen, die dieselbe Instanz nutzen. Die zur Verfügung stehenden Ressourcen werden möglicherweise aufgeteilt oder gesperrt. Das kann dazu führen, dass andere Datenbank-basierte Anwendungen auf der gemeinsam genutzten SQL Server-Instanz ausgebremst werden. Leistungsprobleme zu beseitigen, kann sehr aufwändig sein, denn dazu müssen Sie herausfinden, welche Instanz die Schwierigkeiten verursacht. Und das ist häufig nicht ganz einfach.

Wie Unternehmen diese Frage beantworten wird in der Regel gegen die Kosten von Betriebssystemen und SQL-Lizenzen abgewogen. Hat die Anwendungsleistung oberste Priorität, ist eine dedizierte Instanz durchaus empfehlenswert.

Microsoft verkauft SQL Server-Lizenzen für Server nicht pro Instanz, sondern pro Core. Aus Kostengründen neigen Administratoren deshalb dazu, so viele SQL Server-Instanzen zu installieren, wie ein Server gerade noch handhaben kann, was auf lange Sicht zu massiven Leistungsproblemen führen kann.

Wann immer es möglich ist, sollten Sie sich daher für dedizierte SQL-Instanzen entscheiden.

Aufteilen von SQL-Dateien auf unterschiedliche Datenträger

SQL Server verwendet sehr unterschiedliche E/A-Muster für das Lesen von Daten und Protokolldateien. Der Zugriff auf Datendateien erfolgt in der Regel wahlfrei, auf Transaktions-Log-Files sequenziell. Bei rotierenden Festplatten muss der Lesekopf für den wahlfreien E/A-Zugriff neu positioniert werden. Der sequenzielle Datenzugriff ist daher effizienter als der wahlfreie. Trennt man Dateien mit unterschiedlichen Zugriffsmustern minimiert man auch die Anzahl der Lesekopf-Bewegungen und optimiert so die Speicherleistung.

Verwenden Sie RAID-10-Systeme für Binärdateien, Daten, Protokolldateien und tempdb-Datenbanken, um die bestmögliche Leistung und Verfügbarkeit zu gewährleisten.

Größenbemessung von tempdb-Datenbanken

Setzen Sie die Dateigröße von tempdb-Datenbanken auf den maximalen Wert, um eine Datenträger-Fragmentierung zu vermeiden.

Auf GAM-, SGAM- und PFS-Seiten können Konflikte auftreten, wenn SQL auf spezielle Systemseiten schreiben muss, um neue Objekte zuzuordnen. Latches schützen (sperren) diese Seiten im Speicher. Auf einem überlasteten SQL-Server kann es lange dauern, bis ein Latch für eine Systemdatei in der tempdb-Datenbank vergeben wird, was zu verlängerten Abfragezeiten führt. Dieses Phänomen nennt man „Latch Contention“.

Faustregel für die Erstellung von tempdb-Datendateien:

• Für <= 8 Cores
  • tempdb-Datendateien = Anzahl der Cores
• Für > 8 Cores
  • 8 tempdb-Datendateien

Ab SQL Server 2016 wird die Anzahl der für das Betriebssystem sichtbaren CPU-Cores bei der Installation automatisch ermittelt. Darauf basierend errechnet und konfiguriert SQL Server die Anzahl der tempdb-Dateien, die für eine optimale Leistung erforderlich sind. Das ist im Vergleich zu früheren Versionen eine deutliche Verbesserung – großes Lob an Microsoft!

Speicherkonfiguration

• Min. Serverarbeitsspeicher
• Max. Serverarbeitsspeicher
• Max. Anzahl von Arbeits-Threads
• Speicher für die Indexerstellung
• Min. Arbeitsspeicher pro Abfrage

Min. Serverarbeitsspeicher

Mit der Option „Min. Serverarbeitsspeicher“ lässt sich die Mindestmenge an Arbeitsspeicher für die SQL Server-Instanz bestimmen. Da SQL Server ein echter Speicherfresser ist, der jeden verfügbaren RAM-Speicher nutzt, wird diese Einstellung in der Regel angewendet, wenn das Betriebssystem zu viel Arbeitsspeicher von SQL Server fordert. Durch Virtualisierungstechnologien gewinnt diese Einstellung aber an Bedeutung.

Max. Serverarbeitsspeicher

Mit der Option „Max. Serverarbeitsspeicher“ wird der maximale Arbeitsspeicher für die SQL Server-Instanz festgelegt. Sie ist vor allem dann wichtig, wenn neben SQL Server noch andere Anwendungen laufen und Sie sicherstellen wollen, dass dafür ausreichend Arbeitsspeicher zur Verfügung steht.

Manche Anwendungen nutzen einfach den beim Start verfügbaren Arbeitsspeicher und fordern nicht mehr RAM an. Selbst dann nicht, wenn es erforderlich wäre. Hier kommt die Option „Max. Serverarbeitsspeicher“ ins Spiel.

In einem SQL Server-Cluster oder einer SQL Server-Farm kann es vorkommen, dass mehrere SQL Server-Instanzen um Ressourcen konkurrieren. Wenn Sie ein Arbeitsspeicherlimit für jede SQL Server-Instanz festlegen, vermeiden Sie diese Konkurrenz um den RAM-Speicher und stellen eine optimale Leistung sicher.

Denken Sie daran, mindestens 4-6 GB RAM für das Betriebssystem zu belassen, um Leistungsproblemen vorzubeugen.

Max. Anzahl von Arbeits-Threads

Diese Option dient dazu die Leistung zu optimieren, wenn eine große Anzahl von Clients mit dem SQL-Server verbunden ist. Normalerweise wird für jede Abfrageanforderung ein separater Betriebssystem-Thread erstellt. Wenn jedoch bei Hunderten von Verbindungen mit dem Server ein Thread pro Abfrageanforderung verwendet wird, werden unter Umständen große Systemressourcen verbraucht. Die Option „Max. Anzahl von Arbeits-Threads“ hilft, die Leistung zu verbessern, da man mit SQL Server Pools von Arbeits-Threads erstellen kann, die eine größere Anzahl von Abfrageanforderungen bewältigen.

Der Standardwert ist 0, denn so kann SQL Server die Anzahl der Arbeits-Threads beim Starten automatisch konfigurieren. Diese Einstellung eignet sich für die meisten Systeme. „Max. Anzahl von Arbeits-Threads“ ist eine erweiterte Option und sollte nur in Zusammenarbeit mit einem erfahrenen Datenbankadministrator geändert werden.

Wann sollten Sie SQL Server so konfigurieren, dass mehr Arbeits-Threads verwendet werden? Beträgt die durchschnittliche Warteschlangenlänge je Zeitplanungsmodul mehr als 1, kann es sinnvoll sein, die Anzahl der Threads zu erhöhen – allerdings nur, wenn die Last nicht CPU-gebunden ist oder es ansonsten lange Wartezeiten gibt. Trifft eines von beiden zu, nützt es nichts, weitere Threads hinzuzufügen, denn sie würden ebenfalls in der Warteschlange enden.

Speicher für Indexerstellung

Auch das ist eine erweiterte Option, die in der Regel nicht geändert werden sollte. Damit steuert man die Höchstmenge von RAM, die für die Erstellung von Indizes zugewiesen wird. Der Standardwert für diese Option beträgt 0, was bedeutet, dass SQL Server diese Einstellung automatisch konfiguriert. Wenn Sie jedoch Schwierigkeiten bei der Indexerstellung haben, können Sie diesen Wert erhöhen.

Min. Arbeitsspeicher pro Abfrage

Wenn eine Abfrage ausgeführt wird, versucht SQL Server, die optimale Menge an Arbeitsspeicher dafür zuzuweisen. Standardmäßig ist ein Mindestwert von 1.024 KB für jede Abfrage festgelegt. Es empfiehlt sich, die Standardeinstellung bei 0 zu belassen, damit SQL Server den zugewiesenen Arbeitsspeicher für die Indexerstellung dynamisch verwalten kann. Steht SQL Server jedoch mehr RAM zur Verfügung als für eine effiziente Programmausführung erforderlich kann die Leistung einiger Abfragen durch einen höheren Wert gesteigert werden. Solange auf dem Server freier Arbeitsspeicher verfügbar ist, der nicht von SQL Server, anderen Anwendungen oder dem Betriebssystem verwendet wird, können Sie die Gesamtleistung von SQL Server durch einen höheren Wert möglicherweise optimieren. Steht allerdings kein freier RAM zur Verfügung wirkt sich die Aktion eher negativ auf die Gesamtleistung aus.

Prozessorkonfiguration

Hyperthreading

Hyperthreading ist eine spezielle Implementierung von simultanem Multithreading (SMT) in Intel-Prozessoren, um die Parallelisierung von Berechnungen (Multitasking) in x86-Mikroprozessoren zu verbessern. Durch Hardware, die Hyperthreading einsetzt, erscheinen logische Hyperthreading-CPUs gegenüber dem Betriebssystem wie physische CPUs. SQL Server erkennt dann die physischen Prozessoren, die das Betriebssystem anzeigt. Auf diese Weise können Sie die Hypherthreading-Prozessoren nutzen.

Der einzige Haken dabei ist, dass jede Version von SQL Server ihre eigene Rechenkapazitätsgrenze hat.

https://msdn.microsoft.com/de-de/library/ms143760.aspx

NUMA (Non-Uniform Memory Access, nicht einheitlicher Speicherzugriff)

NUMA ist eine Methode um den Speicherzugriff zu optimieren. Und mit ihrer Hilfe lässt sich die Prozessorgeschwindigkeit steigern, ohne die Auslastung des Prozessorbusses zu erhöhen. SQL Server unterstützt NUMA und funktioniert gut auf NUMA-Hardware, ohne dass dazu eine besondere Konfiguration erforderlich ist.

Prozessorzugehörigkeit

Falls Sie keine Leistungsprobleme haben, ist es unwahrscheinlich, dass Sie jemals die Standardeinstellung der Prozessorzugehörigkeit ändern müssen. Dennoch lohnt es sich, mehr darüber zu wissen.

SQL Server unterstützt die Prozessoraffinität durch zwei Maskenoptionen:

• Affinity Mask (auch als CPU-Affinitätsmaske bezeichnet)
• E/A-Affinität

SQL Server nutzt sämtliche im Betriebssystem verfügbare CPUs. Dabei werden Zeitplanungsmodule für alle CPUs erstellt, um die Ressourcen optimal zu nutzen. Beim Multitasking können das Betriebssystem oder andere Anwendungen auf dem SQL-Server Prozess-Threads zwischen den Prozessoren verschieben. Da SQL Server große Mengen an Ressourcen braucht, kann die Leistung dadurch beeinträchtigt werden. Um diesen Effekt zu minimieren, können die Prozessoren so konfiguriert werden, dass die SQL Server-Last einer zuvor ausgewählten Prozessorgruppe zugeordnet wird. Das ist über die CPU-Affinitätsmaske möglich.

Die Option „E/A-Affinität“ bindet die SQL Server-Datenträger-E/A an eine bestimmte Teilmenge der CPUs. In OLTP-Umgebungen (Online Transactional Processing) kann diese Erweiterung die Leistung von SQL Server-Threads, die E/A-Vorgänge verursachen, verbessern.

Hinweis: Die Hardwareaffinität für einzelne Datenträger oder Datenträgercontroller wird nicht unterstützt.

Max. Grad an Parallelität (Max Degree of Parallelism, MAXDOP)

Standardmäßig nutzt SQL Server alle verfügbaren Prozessoren um Abfragen auszuführen. Das ist für umfangreiche Abfragen zwar vorteilhaft, kann jedoch die Leistung und Parallelität beeinträchtigen. Ein besserer Ansatz ist es, die Parallelität auf die Anzahl physischer Cores in einem Prozessorsockel zu beschränken. So sollte MAXDOP auf einem SQL-Server mit zwei physischen Prozessorsockeln mit je vier Cores unabhängig von der Hyperthreading-Funktion auf 4 gesetzt werden. Mit MAXDOP kann nicht bestimmt werden, welcher Prozessor verwendet wird. Vielmehr wird damit die maximale Anzahl von Prozessoren eingeschränkt, die für eine einzelne Abfrage verwendet werden.

Kostenschwellenwert für Parallelität

Der Standardwert für diese Option beträgt 5. Der Abfrageoptimierer bestimmt anhand des Kostenschwellenwerts, ob es sinnvoll ist, parallele Pläne für Abfragen zu erstellen. 5 ist ein sehr niedriger Wert, der sich nur für reine OLTP-Anwendungen eignet (dazu gehört übrigens DatAdvantage).

Für Nicht-OLTP-Systeme empfehlen wir, den Wert zunächst auf etwa 50 zu setzen und ihn je nach Bedarf anzupassen. Für kritische Abfragen innerhalb einer Anwendung sollten Sie den Wert unbedingt anpassen.

Andere wichtige Einstellungen

Sofortige Dateiinitialisierung

Wenn Sie SQL Server die Windows-Berechtigung „Durchführen von Volume-Wartungsaufgaben“ erteilen, erzielen Sie bessere Leistung beim Vergrößern von Datendateien.

Normalerweise schreibt Windows eine Vielzahl von Nullen, sobald ein Benutzer Speicherplatz benötigt. Wenn man eine Datei mit 1 MB erstellt, schreibt Windows 1 MB Nullen auf den Datenträger, um die Datei zu initialisieren. Verfügt SQL Server über die Berechtigung „Durchführen von Volume-Wartungsaufgaben“, fordert es Windows auf, den benötigten Speicherplatz als belegt zu markieren und ihn sofort an SQL Server zurückzugeben. So lassen sich Dateien schneller vergrößern.

Sicherungskomprimierung

Ab SQL Server 2008r2 kann die Sicherungskomprimierung über ein Kontrollkästchen aktiviert werden.

Backups erfordern dadurch weniger Speicherplatz, nehmen weniger Zeit in Anspruch und lassen sich sogar schneller wiederherstellen. Diese Einstellung sollten Sie auf jeden Fall verwenden.

Dedizierte Remote Administratorverbindung

Diese Einstellung benötigen Sie nur, wenn mit SQL Server etwas nicht stimmt.

Wenn Sie über eine dedizierte Administratorverbindung (Dedicated Administrator Connection, DAC) zugreifen, stellt SQL Server eine dedizierte Verbindung, ein CPU-Zeitplanungsmodul und Arbeitsspeicher zur Verfügung. Die Remote-Fehlerbehebung bei einer SQL Server-Instanz, die konstant bei 100 % CPU-Auslastung ist, gestaltet sich deutlich einfacher, wenn man über dedizierte Ressourcen verfügt! Sie müssen entweder physisch über die Konsole oder per Fernzugriff über RDP mit SQL Server verbunden sein, um eine Remote-DAC nutzen zu können. Auch diese Einstellung sollten Sie auf jeden Fall nutzen. Einmal aktiviert kann man sie dann gleich wieder vergessen!

Fazit

SQL Server stellt die erforderliche Leistung und Skalierbarkeit bereit, um Produktivdatenbank-Anwendungen zu unterstützen, sofern man sich an Best Practices orientiert.
In unserem nächsten Blogbeitrag beschäftigen wir uns mit Best Practices für SQL Server in virtualisierten Umgebungen.

Quellen:
https://msdn.microsoft.com/en-us/library/mt590198(v=sql.1).aspx
https://social.technet.microsoft.com/Forums/sqlserver/en-us/home?category=sqlserver
https://technet.microsoft.com/en-us/library/mt590198(v=sql.1).aspx
https://blogs.technet.microsoft.com/dataplatforminsider/
https://blogs.msdn.microsoft.com/sqlserverstorageengine/
http://blogs.msdn.com/sqlperf/default.aspx
https://blogs.msdn.microsoft.com/sqltips/
https://www.brentozar.com/