Serverkonfiguration:

sp_configure richtig nutzen — und was du besser nie anfasst

Die 20/80-Regel: Wenige Einstellungen, großer Unterschied

sp_configure listet über 70 Konfigurationsoptionen auf. Davon sind etwa 10 wirklich wichtig, 5 können bei falscher Konfiguration ernsthaften Schaden anrichten, und der Rest ist für 99% aller Installationen irrelevant. Dieses Kapitel zeigt dir, welche 20 Prozent der Einstellungen 80 Prozent des Unterschieds machen.

Die gute Nachricht: Die meisten SQL-Server-Defaults sind vernünftig. Jemand bei Microsoft hat sich dabei etwas gedacht. Die schlechte Nachricht: Zwei Defaults sind gefährlich — max server memory und cost threshold for parallelism. Und eine Einstellung, die nie jemand anfassen sollte, taucht in der Praxis mit erschreckender Regelmäßigkeit aktiviert auf: Priority Boost.

Als Querverweis: Die NUMA-Grundlagen aus Kapitel 1 sind Voraussetzung für das Verständnis von MAXDOP. Die Buffer-Pool-Internals aus Kapitel 4 erklären, warum max server memory so kritisch ist. Und die Auswirkungen dieser Einstellungen auf Ausführungspläne behandelt Kapitel 15. Wer verstehen will, wie SQL Server intern mit Threads und CPU-Schedulern umgeht, findet die Grundlagen in Kapitel 4 unter SQLOS und dem Scheduling-Modell.

Dieses Kapitel ist bewusst praxisorientiert: Für jede wichtige Einstellung gibt es eine klare Empfehlung, den Rechenweg dahinter, und wo nötig den Hinweis warum der Default falsch ist. Am Ende des Kapitels findest du eine Checkliste, die du bei jedem neuen Server oder Server-Audit durcharbeiten kannst — und ein Audit-Script, das dir auf einen Blick zeigt, wo ein Server vom Optimum abweicht.

 

Hinweis: sp_configure — Grundprinzip
Viele Einstellungen erfordern RECONFIGURE WITH OVERRIDE, andere wirken sofort. Einige erfordern einen SQL Server Neustart. Prüfe immer mit: SELECT name, value, value_in_use, is_dynamic FROM sys.configurations WHERE name = '<option>';
is_dynamic = 1 → wirkt ohne Neustart. is_dynamic = 0 → Neustart erforderlich.
Advanced-Optionen sichtbar machen: EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
value ist der konfigurierte Wert, value_in_use ist der aktuell aktive Wert. Bei is_dynamic = 0 weichen diese voneinander ab, bis der Dienst neu gestartet wird.

 

Max Server Memory: Die wichtigste Einstellung überhaupt

Der Default-Wert von max server memory ist 2.147.483.647 MB — also effektiv unbegrenzt. Das bedeutet: SQL Server darf den gesamten verfügbaren RAM beanspruchen, bis das Betriebssystem anfängt, Speicher auszupagingen. Das ist in Produktionsumgebungen schlicht gefährlich.

Was passiert ohne Limit: SQL Server wächst kontinuierlich in den verfügbaren RAM. Das OS und andere Prozesse bekommen immer weniger Speicher. Der OS-Speicher wird knapp, Windows beginnt zu pagen, der Server wird quälend langsam. Im schlimmsten Fall startet Windows Prozesse neu oder wird instabil. SQL Server selbst bekommt davon wenig mit — er verwendet gerade freudig denjenigen RAM, den das OS zum Atmen bräuchte.

Der Buffer Pool — das Herzstück von SQL Servers Speicherverwaltung, ausführlich in Kapitel 4 beschrieben — ist der primäre Speicherfresser. Er wächst dynamisch, so lange Arbeit vorhanden ist und RAM verfügbar ist. max server memory ist der einzige Mechanismus, der dieses Wachstum begrenzt. Ohne diesen Deckel ist der Buffer Pool wie ein Teenager mit elterlicher Kreditkarte: verbraucht alles, bis jemand nein sagt.

 

Abb. 5.2: RAM-Aufteilung: OS-Reserve, SQL Server Non-Buffer-Pool und Buffer Pool (Beispiel: 128 GB)

Die Berechnungsformel

Die Formel für max server memory ist nicht kompliziert, aber es gibt Fallstricke:

-- Formel für max server memory (in MB)

-- Schritt 1: Physischen RAM ermitteln

SELECT physical_memory_kb / 1024 AS ram_gesamt_mb

FROM sys.dm_os_sys_info;

 

-- Schritt 2: Abzüge berechnen (Beispiel: 128 GB Server)

-- OS-Reserve:              8.192 MB  (mind. 2 GB, bei > 64 GB mind. 8 GB)

-- SQL-Threadstacks:        ~512 MB   (bei 1024 Worker Threads à 512 KB)

-- CLR / SQLCLR:            ~256 MB   (wenn CLR genutzt wird)

-- Other SQL-Speicher:     ~1.024 MB  (Sicherheitspuffer)

-- Andere Instanzen/Apps:      0 MB   (falls vorhanden, addieren!)

 

-- Ergebnis: max server memory = 128.000 - 8.192 - 512 - 256 - 1.024

--                             = ~118.016 MB → auf 118.000 abrunden

 

-- Setzen:

EXEC sp_configure 'max server memory (MB)', 118000;

RECONFIGURE;

-- Kein Neustart nötig — wirkt sofort (is_dynamic = 1)

 

Physischer RAM	OS-Reserve (Minimum)	Empfohlenes max server memory
4 GB	1 GB	2.048 MB (2 GB)
8 GB	2 GB	5.120 MB (5 GB)
16 GB	2 GB	12.288 MB (12 GB)
32 GB	4 GB	26.000 MB (~25,4 GB)
64 GB	4 GB	57.344 MB (56 GB)
128 GB	8 GB	118.000 MB (~115 GB)
256 GB	16 GB	237.000 MB (~231 GB)

Tab. 5.1: Richtwerte für max server memory (ohne andere Instanzen oder RAM-intensive Dienste)

 

 

Praxisbeispiel: Sparfuchs & Partner: max server memory = 8192 bei 8 GB RAM
Der Server von Sparfuchs & Partner hat 8 GB physischen RAM. max server memory war auf 8.192 MB gesetzt — also exakt der gesamte physische RAM.
SQL Server durfte damit theoretisch alles beanspruchen. Das OS hatte keinen Puffer. Antivirus, Windows Update, Management-Agenten — alle kämpften gegen SQL Server um Speicher. Das Ergebnis: Sporadische Instabilität, gelegentliche OS-Paging-Events, und eine PLE (Page Life Expectancy), die regelmäßig unter 100 Sekunden fiel.
Fix: max server memory auf 5.120 MB setzen (8 GB minus 3 GB OS-Reserve — großzügig bei so wenig RAM). Sofortige Verbesserung der Systemstabilität. Zusammen mit Priority Boost, kaputtem Transaktionslog und 847 Autogrowth-Events ergibt sich ein beeindruckendes Gesamtbild — vollständige Analyse in Kapitel 33.

 

Ein wichtiger Punkt, der häufig übersehen wird: max server memory begrenzt nur den Buffer Pool und einige andere SQL-Server-interne Bereiche. Threadstacks, CLR-Speicher, Linked-Server-Provider und einige andere Bereiche fallen nicht darunter. Bei sehr vielen Worker Threads kann der tatsächliche SQL-Server-Verbrauch max server memory also übersteigen — in der Praxis selten, aber es passiert. Mehr dazu in Kapitel 11, das sich vollständig dem Memory Management widmet — inklusive Buffer Pool, NUMA-Awareness und PLE als zentraler Kennzahl.

MAXDOP: Wie viele CPUs darf eine Abfrage nutzen?

MAXDOP (Maximum Degree of Parallelism) legt fest, auf wie viele Worker Threads eine einzelne parallele Abfrage maximal verteilt wird. Der Default ist 0 — was bedeutet, dass SQL Server alle verfügbaren logischen CPUs nutzen kann. Auf einem Server mit 64 Cores könnte eine einzelne schlecht optimierte Abfrage theoretisch alle 64 Kerne für sich allein beanspruchen und alles andere blockieren.

Das ist selten, was du willst. Aber es ist noch seltener, dass du MAXDOP = 1 willst — denn dann verzichtest du völlig auf Parallelismus, was bei analytischen Abfragen und Batch-Operationen dramatisch langsamer ist. Die Kunst liegt in der Mitte: genug Parallelismus für große Abfragen, ohne, dass eine einzelne Query den gesamten Server lahmlegt.

Wichtig: MAXDOP interagiert direkt mit der NUMA-Topologie (Kapitel 1 und 3). Wenn MAXDOP größer ist als die Anzahl der Kerne auf einem NUMA-Node, beginnt SQL Server Threads auf benachbarten NUMA-Nodes zu schedulen — mit Remote-RAM-Zugriffen als Nebeneffekt. Das ist teuer. Auf einer Maschine mit 4 NUMA-Nodes à 8 Kerne willst du MAXDOP ≤ 8, damit parallele Abfragen innerhalb eines NUMA-Nodes bleiben.

 

Abb. 5.1: MAXDOP-Berechnung mit NUMA-Topologie: Kerne pro NUMA-Node bestimmen den Ausgangswert

Die MAXDOP-Berechnungsformel

Microsofts offizielle Empfehlung hat sich über die Jahre gewandelt, aber das Grundprinzip ist stabil: MAXDOP sollte nicht über NUMA-Node-Grenzen gehen, und bei Hyperthreading sollte man physische statt logische Kerne zählen. Physische Kerne leisten echte Rechenarbeit — logische Kerne (durch Hyperthreading) teilen sich die Ausführungseinheiten und helfen hauptsächlich bei IO-lastigen Workloads, kaum bei CPU-intensiven Berechnungen.

-- Schritt 1: NUMA-Topologie ermitteln

SELECT

    node_id,

    node_state_desc,

    memory_node_id,

    online_scheduler_count,  -- Scheduler (= logische CPUs) auf diesem Node

    cpu_count                -- Logische CPUs auf diesem Node

FROM sys.dm_os_nodes

WHERE node_state_desc <> 'DAC';  -- Dedicated Admin Connection ausblenden

 

-- Schritt 2: Physische Kerne pro NUMA-Node zählen (nicht logische!)

-- Bei Hyperthreading: 2 logische Kerne = 1 physischer Kern

-- MAXDOP-Kandidat = physische Kerne pro NUMA-Node

 

-- Schritt 3: Obergrenze 8 anwenden (Microsoft-Empfehlung für OLTP)

-- Beispiel: 6 physische Kerne pro Node → MAXDOP = 6 (unter 8, also kein Cap)

-- Beispiel: 12 physische Kerne pro Node → MAXDOP = 8 (gecapped)

 

-- MAXDOP setzen:

EXEC sp_configure 'max degree of parallelism', 6;

RECONFIGURE;

-- Gilt für alle neuen Abfragen — laufende Queries nicht betroffen

 

Szenario	NUMA-Nodes	Physische Kerne/Node	Empfehlung MAXDOP
Kleiner Server	1	4	4
Standard OLTP	2	6	6
Großer Server	4	12	8 (gedeckelt)
DWH / Analytics	4	16	16 (höherer Wert akzeptabel)
VM mit vCPUs	variiert	unbekannt	Herstellerempfehlung prüfen, oft 4–8
Einzelner Core-Server	1	1	1 (kein Parallelismus sinnvoll)

Tab. 5.2: MAXDOP-Empfehlungen nach Szenario

 

 

Warnung: MAXDOP auf virtuellen Maschinen
Auf VMs ist MAXDOP besonders heikel. vCPUs können über mehrere physische NUMA-Nodes verteilt sein, ohne, dass SQL Server das sieht. Ein MAXDOP = 8 auf einer VM mit 8 vCPUs kann dazu führen, dass SQL Server NUMA-Node-Grenzen überschreitet und Remote-RAM-Zugriffe erzeugt — deutlich langsamer als lokaler RAM.
Kapitel 3 erklärt, wie man vNUMA korrekt konfiguriert und warum es in manchen VM-Szenarien sinnvoll ist, MAXDOP = 1 zu setzen. Das ist keine Schwäche — es ist Realismus.
CXPACKET-Waits als Diagnose-Signal: Steigt der CXPACKET-Wait-Anteil deutlich, läuft zu viel Parallelismus. Mehr dazu in Kapitel 9 (Wait Statistics) und Kapitel 15 (Ausführungspläne).

 

Cost Threshold for Parallelism: Der unterschätzte Parameter

SQL Server entscheidet nicht nur anhand von MAXDOP, ob eine Abfrage parallel läuft. Zuerst prüft der Optimizer, ob die geschätzten Kosten den Cost Threshold for Parallelism überschreiten. Default: 5. Was bedeutet "5"? Grob übersetzt: eine Abfrage, die auf einem Referenz-System von 1993 ca. 5 Sekunden dauern würde. Auf moderner Hardware sind das Millisekunden.

Das Ergebnis: Mit dem Default von 5 laufen fast alle Abfragen parallel — auch kurze, einfache Queries, bei denen der Parallelismus-Overhead größer ist als der Nutzen. Das führt zu unnötiger CPU-Last, erhöhter CXPACKET-Wait-Zeit und schlechterer Gesamtperformance bei kurzen OLTP-Abfragen. Der Overhead eines parallelen Plans — Thread-Synchronisation, Daten-Partitionierung, Merge-Operationen — kostet zwischen 1 und 5 ms pro Abfrage. Bei einer Abfrage, die eigentlich 2 ms brauchen würde, ist das eine Verdoppelung bis Verdreifachung der Laufzeit.

Die Empfehlung: Setze den Wert auf 25–50 für typische OLTP-Workloads. Für Reporting- oder DWH-Workloads kann ein niedrigerer Wert (10–20) sinnvoll sein. Teste immer mit deiner konkreten Workload — es gibt keine universelle Antwort. Der Weg dorthin: Baseline der CXPACKET-Waits erfassen (sys.dm_os_wait_stats), Cost Threshold erhöhen, nach 24–48 Stunden erneut messen. Sinkende CXPACKET-Waits bei gleichbleibender oder besser werdender Abfragelaufzeit: richtige Richtung.

-- Cost Threshold und MAXDOP immer zusammen anpassen

-- (einzeln anpassen bringt selten das gewünschte Ergebnis)

 

-- Cost Threshold erhöhen — weniger parallele Abfragen

EXEC sp_configure 'cost threshold for parallelism', 50;

RECONFIGURE;

 

-- Aktuellen Wert prüfen:

SELECT name, value_in_use

FROM sys.configurations

WHERE name IN ('max degree of parallelism',

               'cost threshold for parallelism');

 

-- Achtung: Bestehende gecachte Pläne werden NICHT invalidiert!

-- Änderung wirkt nur auf neue Compilierungen.

-- Wenn du sofort alle Pläne neu kompilieren willst:

DBCC FREEPROCCACHE;  -- Vorsicht in Produktion — CPU-Spike möglich

-- Besser: Plan Cache über Nacht auslaufen lassen

 

Tipp: Die goldene Kombination
MAXDOP = Anzahl physischer Kerne pro NUMA-Node (max. 8 für OLTP)
Cost Threshold = 50 (für OLTP), 25 (für gemischte Workloads)
Beide Werte zusammen testen: Vorher Baseline der CXPACKET-Waits erfassen (sys.dm_os_wait_stats), Werte ändern, nach 24h erneut messen. Verbesserung sichtbar? Fertig.
Auf Datenbankebene oder Query-Ebene kann MAXDOP mit dem MAXDOP-Query-Hint überschrieben werden — nützlich für einzelne Ausreißer, kein Ersatz für korrekte Serverkonfiguration.

 

Priority Boost: Das absolute Verbot

Es gibt Konfigurationsoptionen, die man falsch einstellen kann und die dann suboptimal performen. Und es gibt Priority Boost. Priority Boost ist keine suboptimale Option — es ist eine gefährliche Option, die seit SQL Server 2008 offiziell deprecated ist und die du niemals aktivieren solltest.

Was es macht: Priority Boost erhöht die Windows-Thread-Priorität des SQL-Server-Prozesses von Normal (8) auf High (13). Die Idee dahinter klingt verlockend: SQL Server bekommt mehr CPU-Zeit, weil er höher priorisiert ist als andere Prozesse.

Was wirklich passiert: SQL Server verdrängt Windows-Systemthreads — einschließlich derjenigen, die für Netzwerk-IO, Storage-IO, Speicherverwaltung und Scheduling selbst verantwortlich sind. Das System wird instabil. Paging-Operationen verzögern sich. In extremen Fällen friert das gesamte System ein. Microsoft hat das so sehr bereut, dass sie die Option deprecated haben — aber sie ist immer noch da, weil alte Systeme vielleicht davon abhängen.

Die Ironie: Priority Boost macht SQL Server nicht schneller. Es macht SQL Server lauter auf Kosten aller anderen. Auf einem dedizierten SQL Server ohne andere nennenswerter Prozesse bringt es nahezu nichts (SQL Server dominiert ohnehin). Auf einem Server mit anderen Diensten raubt es diesen die CPU und destabilisiert das System. In jedem Fall ist es falsch.

 

Warnung: Priority Boost: Niemals aktivieren
Priority Boost = 1 (aktiviert) ist ein sofortiges Warnsignal bei jedem Server-Audit.
Symptome: Sporadische Systeminstabilität, unerklärliche IO-Pausen, gelegentliche Freezes, nicht reproduzierbare Timeouts.
Prüfen: SELECT value_in_use FROM sys.configurations WHERE name = 'priority boost';
Falls 1: Sofort auf 0 setzen und den SQL-Server-Dienst neu starten.
EXEC sp_configure 'priority boost', 0; RECONFIGURE;
Hinweis: Dieser Wert braucht einen Neustart! (is_dynamic = 0)
Sparfuchs & Partner hatte Priority Boost seit SQL 2008 aktiviert — und fragte sich, warum der Server manchmal einfriert. Zusammen mit 8 GB RAM ohne OS-Reserve und einem kaputten Log ein schönes Gesamtbild. Details in Kapitel 33.

 

Lightweight Pooling: Ebenfalls immer aus

Lightweight Pooling (auch Fiber Mode genannt) war einst ein Versuch, den SQLOS-Scheduling-Overhead zu reduzieren, indem Worker Threads durch Windows Fibers (sehr leichtgewichtige Threads) ersetzt werden. Klingt gut, funktioniert in der Praxis nicht.

Fiber Mode ist inkompatibel mit vielen SQL-Server-Features: CLR, distributed queries, bestimmte extended stored procedures. Microsoft empfiehlt ausdrücklich, es nicht zu aktivieren. Der Default (0 = aus) ist korrekt. Finger weg.

-- Prüfen ob Lightweight Pooling aktiviert ist (sollte 0 sein):

SELECT name, value_in_use FROM sys.configurations

WHERE name = 'lightweight pooling';

-- Erwartetes Ergebnis: value_in_use = 0

-- Falls 1: Auf 0 setzen, Neustart erforderlich

Affinity Mask: Wann sinnvoll, wann nicht

Affinity Mask erlaubt es, SQL-Server-Threads an bestimmte CPUs zu binden (CPU Affinity). Das klingt nach einem Eingriff in den SQLOS, der nur nötig sein sollte, wenn SQLOS sich falsch verhält — und genau das ist der Fall.

In modernen Umgebungen ist manuelles Affinity-Pinning selten nötig. SQL Server mit SQLOS und NUMA-Awareness macht das von allein richtig. Die NUMA-Grundlagen aus Kapitel 1 und die SQLOS-Internals aus Kapitel 4 zeigen, warum SQLOS normalerweise bessere Entscheidungen trifft als ein manuell gepflegter Affinity-Wert. Ausnahmen:

Mehrere SQL-Server-Instanzen auf einer physischen Maschine: CPU-Aufteilung durch Affinity sicherstellen

Bestimmte Workloads müssen isoliert werden (z.B. Reporting vs. OLTP auf gleicher Instanz)

Hyperkonvergente Infrastruktur mit spezifischen CPU-Zuteilungen

Für die meisten Installationen gilt: Default lassen. Wenn Affinity nötig ist, nutze ALTER SERVER CONFIGURATION statt der deprecated sp_configure-Option — das ist der aktuelle Weg seit SQL Server 2012.

Instant File Initialization (IFI): Der versteckte Performance-Faktor

Wenn SQL Server eine neue Datenbankdatei anlegt oder eine bestehende vergrößert, muss das Betriebssystem dafür Speicherplatz reservieren. Windows macht das standardmäßig auf eine ehrenwerte, aber langsame Weise: Es überschreibt den neu zugewiesenen Bereich mit Nullen — Byte für Byte, Seite für Seite. Zero Initialization heißt das, und es ist eine Sicherheitsmaßnahme: Kein nachfolgender Prozess soll zufällig die Daten sehen, die ein früherer Prozess auf diesem Speicherbereich hatte.

Das Problem: Zero Initialization kostet Zeit. Sehr viel Zeit, wenn die Datei groß ist. Eine 100-GB-Datendatei ohne IFI zu erstellen kann auf moderner NVMe-Hardware 30 bis 90 Sekunden dauern, auf herkömmlichen Festplatten mehrere Minuten. Auf einem Produktionssystem, das gerade eine Autogrowth-Operation durchführt, bedeutet das: Alle Abfragen, die auf diese Datei warten, stehen still. Nicht 5 Millisekunden. Nicht 50 Millisekunden. Sekunden.

Instant File Initialization (IFI) umgeht diese Zero Initialization für Datenbankdaten-Dateien (nicht für Log-Dateien!). SQL Server erhält vom OS sofort die angeforderten Blöcke, ohne, dass diese vorher genullt werden. Das funktioniert, weil SQL Server seine eigenen Seitenheader schreibt und leere Seiten intern als "nicht initialisiert" markiert — die Sicherheitsgarantie wird auf SQL-Server-Ebene gewährleistet. Log-Dateien werden weiterhin genullt, weil das Log-Format es erfordert. Das ist korrekt so.

IFI aktivieren: Die Sicherheitsrichtlinie

IFI wird aktiviert, indem dem SQL-Server-Dienstkonto das Windows-Recht "Volumenwartungsaufgaben ausführen" (SeManageVolumePrivilege) erteilt wird. Das ist keine sp_configure-Option — es ist eine Windows-Sicherheitsrichtlinie. Ohne dieses Recht: Zero Initialization bei jedem Dateiwachstum. Mit diesem Recht: sofortige Zuweisung.

-- IFI-Status prüfen — direkt über SQL Server (ab SQL 2016)

-- Zeigt ob IFI für den SQL-Server-Prozess verfügbar ist

SELECT instant_file_initialization_enabled

FROM sys.dm_server_services

WHERE servicename LIKE 'SQL Server%'

  AND servicename NOT LIKE '%Agent%';

-- Y = IFI aktiv, N = IFI nicht aktiv (Zero Initialization)

 

-- Alternativ: Startuplog prüfen

-- Im SQL Server Error Log steht beim Start:

-- "Database Instant File Initialization: enabled" oder "disabled"

EXEC sp_readerrorlog 0, 1, 'instant file initialization';

 

-- Aktivierung: Windows-Sicherheitsrichtlinien (secpol.msc)

-- → Lokale Richtlinien → Zuweisen von Benutzerrechten

-- → "Volumenwartungsaufgaben ausführen" → SQL-Server-Dienstkonto hinzufügen

-- SQL Server Dienst neu starten — danach ist IFI aktiv

Die Auswirkung ist messbar und dramatisch: Eine 10-GB-Datenbankdatei anlegen dauert ohne IFI zwischen 15 und 40 Sekunden (je nach Storage), mit IFI unter 100 Millisekunden. Das ist kein kleiner Unterschied — das ist ein Faktor 150 bis 400. Bei Autogrowth-Ereignissen macht sich das direkt als IO-Wait in der Wartestatistik bemerkbar.

 

Praxisbeispiel: Sparfuchs & Partner: 847 Autogrowths ohne IFI
In der Analyse von Sparfuchs & Partner (Kapitel 33) wurden in einem 120-Minuten-Fenster 847 Autogrowth-Events protokolliert — à 1 MB pro Wachstumsschritt. Das ergibt 847 Zero-Initialization-Vorgänge, jeder davon mit einer kleinen aber messbaren Pause für wartende Abfragen.
Ohne IFI und mit 1-MB-Schritten: jedes Autogrowth-Event verursacht eine kurze Blockade. 847 davon in 2 Stunden bedeutet im Schnitt alle 8,5 Sekunden ein Wachstumsereignis — die Applikation lief entsprechend zäh.
Doppelter Fix: IFI aktivieren UND Autogrowth-Schrittweite auf 512 MB oder 10% setzen, damit das Wachstum seltener ausgelöst wird. Kapitel 10 (IO-Performance) erklärt Autogrowth-Konfiguration im Detail.

 

 

Hinweis: IFI und Sicherheitsbedenken
Manche Sicherheitsabteilungen sehen SeManageVolumePrivilege kritisch, weil damit theoretisch Daten früherer Prozesse gelesen werden könnten. In der Praxis ist das kein reales Risiko für SQL Server: Neue Datenbankseiten werden beim ersten Schreibzugriff immer vollständig mit gültigen Daten befüllt.
Die Alternative — Zero Initialization — kostet Performance ohne merkbaren Sicherheitsgewinn in einer dedizierten Datenbankumgebung. Trotzdem: Dokumentiere die Aktivierung von IFI in deinem Security Runbook, damit der Auditor nicht erschrickt.

 

Lock Pages in Memory (LPIM): Wann wirklich nötig

Lock Pages in Memory (LPIM) ist, vereinfacht gesagt, das Gegenteil von Max Server Memory: Statt zu begrenzen wie viel RAM SQL Server verwenden darf, verhindert LPIM, dass Windows SQL-Server-Speicherseiten auf die Auslagerungsdatei (Pagefile) auslagert.

Warum kann das nötig sein? Windows hat einen eigenen Speichermanager, der bei Bedarf Seiten aus dem RAM verschiedener Prozesse auf den Pagefile auslagert — auch von SQL Server. Normalerweise reguliert max server memory diesen Druck, weil ein korrekt eingestelltes Memory-Limit SQL Server dazu bringt, selbst Speicher freizugeben, bevor Windows eingreift. Auf sehr großen Servern mit viel RAM und aggressiver Windows-Speicherverwaltung kann es dennoch vorkommen, dass Windows Buffer-Pool-Seiten auslagert — mit katastrophalen Folgen für die IO-Latenz, weil ausgelagerte Datenbankseiten erst vom Pagefile zurückgelesen werden müssen.

LPIM verhindert genau das: Seiten, die als "locked" markiert sind, werden von Windows nicht ausgelagert. Das klingt gut, hat aber einen Haken: SQL Server gibt diesen Speicher auch dann nicht frei, wenn das OS in Speichernot gerät. Während SQL Server ohne LPIM auf Windows-Druck reagiert und Speicher freigibt, ignoriert er mit LPIM diese Signale. Das OS kann in Bedrängnis geraten, wenn der RAM knapp wird.

Empfehlung: LPIM nur aktivieren, wenn du ein beobachtbares Problem mit Windows-seitigem Paging von SQL-Server-Speicher hast. Das diagnostizierst du über sys.dm_os_process_memory (Spalte page_fault_count und locked_page_allocations_kb) und über Windows Performance Monitor (Memory: Pages/sec). Ohne messbares Problem: LPIM aus.

 

Hinweis: LPIM und Lock Pages in Memory
LPIM wird über das Windows-Recht "Sperren von Seiten im Speicher" (SeLockMemoryPrivilege) aktiviert — ebenfalls in secpol.msc unter "Zuweisen von Benutzerrechten".
Ab SQL Server 2012 SP1 benötigt LPIM mit Standard Edition keinen Enterprise-Lizenzschlüssel mehr.
Auf Systemen mit Lock Pages in Memory empfiehlt Microsoft, AUCH max server memory korrekt zu setzen — LPIM ohne Speicherlimit ist ein Rezept für OS-Instabilität.
Auf virtuellen Maschinen mit Memory Ballooning (VMware, Hyper-V) ist LPIM besonders heikel, weil der Hypervisor dann keinen RAM zurückfordern kann. Kapitel 3 (Virtualisierung) behandelt das ausführlich.

 

TempDB-Erstkonfiguration: Die Grundlage für Kapitel 13

TempDB ist die meistgenutzte und meistignorierte Datenbank in SQL Server. Sie ist die gemeinsam genutzte Ablage für temporäre Objekte, Sortieroperationen, Spills, Row-Versioning und vieles mehr. Und sie hat eine Besonderheit, die viele Administratoren erst dann kennenlernen, wenn es brennt: TempDB wird bei jedem SQL-Server-Start neu erstellt. Das ist keine Schwäche — es ist eine Designentscheidung. Aber es bedeutet auch, dass TempDB-Konfigurationsprobleme erst im laufenden Betrieb sichtbar werden.

Das zentrale Problem von TempDB ist Contention — mehrere Sessions kämpfen gleichzeitig um die gleichen Allokationsseiten (PFS, GAM, SGAM). Dieser Kampf manifestiert sich als PAGELATCH-Waits auf TempDB-Seiten und kann einen an sich schnellen Server in ein zähes Biest verwandeln. Kapitel 13 widmet sich TempDB vollständig und erklärt die Mechanismen hinter der Contention. Hier geht es um die richtige Ausgangskonfiguration — bevor das Problem überhaupt entstehen kann.

Anzahl der TempDB-Datendateien

Die wichtigste TempDB-Einstellung: die Anzahl der Datendateien. Seit SQL Server 2016 schlägt der Setup-Assistent bereits einen vernünftigen Wert vor — für ältere Installationen oder solche, die ohne Setup-Empfehlung durchgeführt wurden, lautet die Antwort oft: 1 Datei. Das ist fast immer falsch.

Die Faustformel: eine TempDB-Datendatei pro logischem CPU-Kern, maximal 8. Ein Server mit 4 Cores: 4 Dateien. Ein Server mit 16 Cores: 8 Dateien (gedeckelt). Ein Server mit 32 Cores: immer noch 8 Dateien — mehr Dateien bringen ab einem gewissen Punkt keinen Mehrwert, weil der Algorithmus, der Allokationen auf TempDB-Dateien verteilt (Proportional Fill), danach aufgehört hat, sich zu verbessern.

Kritisch: Alle TempDB-Datendateien müssen gleich groß sein und die gleiche Autogrowth-Schrittweite haben. Wenn Datei 1 größer ist als Datei 2, bevorzugt der Proportional-Fill-Algorithmus Datei 1 stärker — der Lastausgleich bricht zusammen, und du hast de facto eine dominante Datei mit Contention-Risiko.

-- Aktuelle TempDB-Dateikonfiguration prüfen

SELECT

    file_id,

    name,

    type_desc,

    size * 8 / 1024      AS groesse_mb,

    growth * 8 / 1024    AS wachstum_mb,

    is_percent_growth,

    physical_name

FROM tempdb.sys.database_files

ORDER BY file_id;

-- Erwartetes Ergebnis: mehrere ROWS, alle mit gleicher groesse_mb

-- und identischem wachstum_mb (kein prozentuales Wachstum!)

 

-- TempDB auf 8 gleich große Datendateien konfigurieren (Beispiel)

-- Erst bestehende Datei 1 anpassen:

ALTER DATABASE tempdb

MODIFY FILE (

    NAME = tempdev,        -- Standardname der ersten TempDB-Datei

    SIZE = 4096MB,         -- 4 GB als Startgröße

    FILEGROWTH = 512MB     -- Wachstum in MB, nicht in Prozent!

);

 

-- Neue Dateien hinzufügen (Dateien 2 bis 8)

-- Alle gleich groß, gleiche Wachstumsrate, gleicher Pfad wie Datei 1

ALTER DATABASE tempdb

ADD FILE (

    NAME = tempdev2,

    FILENAME = 'D:MSSQLDATAempdb2.ndf',

    SIZE = 4096MB,

    FILEGROWTH = 512MB

);

-- Für Dateien 3 bis 8 entsprechend wiederholen

 

-- Wichtig: Änderungen werden erst nach SQL-Server-Neustart aktiv!

-- TempDB wird beim Start neu erstellt — mit der neuen Konfiguration

 

CPU-Kerne (logisch)	Empfohlene TempDB-Datendateien	Begründung
1–4	4	Mindestens 4, auch, wenn Kerne < 4
5–8	Anzahl der logischen Kerne	1:1-Verhältnis bis zum Cap
9–16	8	Microsoft-Obergrenze für OLTP
> 16	8 (Standard) oder mehr bei nachgewiesenem Bedarf	Über 8 nur mit nachgewiesener Contention

Tab. 5.3: TempDB-Dateianzahl nach CPU-Konfiguration

 

TempDB-Log-Datei: Der vergessene Bruder

Während die Datendateien das meiste Augenmerk bekommen, wird die TempDB-Log-Datei oft vernachlässigt. Die Log-Datei von TempDB wächst bei intensiver Nutzung (viele große temporäre Tabellen, lange Transaktionen, Row-Versioning durch RCSI oder Snapshot Isolation) erheblich. Ohne vorausschauende Konfiguration läuft sie in Autogrowth — mit all den damit verbundenen Problemen.

Empfehlung: Die TempDB-Log-Datei auf einem separaten Volume ablegen (wenn möglich), auf eine vernünftige Startgröße konfigurieren, und Autogrowth in fixen MB-Schritten (nicht prozentual). Wie groß "vernünftig" ist, hängt von der Workload ab — aber 1 GB als Startpunkt für kleine bis mittlere Systeme ist selten falsch. Kapitel 13 erklärt die Zusammenhänge zwischen Row-Versioning, RCSI und TempDB-Log-Nutzung.

 

Tipp: TempDB auf eigenem Volume
Das beste, was du für TempDB tun kannst: Sie auf ein separates, schnelles Volume legen. TempDB-IO ist oft die heiße IO-Achse auf einem SQL Server — Spills, temporäre Tabellen, Hash-Joins, Sort-Operationen. Teilt sich TempDB das Volume mit Nutzerdatenbanken, konkurriert sie direkt mit diesen um IO-Bandbreite.
Ideal: NVMe oder schnelle SSD, dediziert für TempDB. Die Investition in ein separates Volume amortisiert sich schnell, wenn Spills und Sort-Operationen nicht mehr die primären Datenbank-IOs verlangsamen.

 

Backup Compression Default: CPU gegen IO — wer gewinnt?

Backup Compression Default steuert, ob SQL Server Backups standardmäßig komprimiert schreibt. Der Default ist 0 (aus). Die Empfehlung: 1 (immer an). Das ist eine der Einstellungen, bei denen es eigentlich keine ernsthafte Gegendiskussion gibt.

Warum? Backup-Kompression kostet CPU, spart aber IO. Auf modernen Servern sind Backup-Operationen fast immer IO-limitiert, nicht CPU-limitiert. Ein typischer SQL-Server mit 8 bis 32 Cores hat in der Regel genug CPU-Reserven für die Kompression — gerade zu Zeiten, wenn Backups laufen (Nacht, Wartungsfenster). Dafür ist die IO-Entlastung erheblich.

Typische Kompressionsraten liegen zwischen 40 und 70 Prozent — je nach Datentypen und Füllgrad. Eine 100-GB-Datenbank produziert mit Kompression ein 40- bis 60-GB-Backup. Das bedeutet nicht nur weniger Storage, sondern auch schnellere Backups (weniger Bytes schreiben), schnellere Übertragungen ins Backup-Ziel, und schnellere Restores. Ein dreifacher Gewinn für den Preis von etwas CPU-Last, die meist ohnehin verfügbar ist.

Ausnahme: Wenn die Daten bereits komprimiert sind (PDF-Archive, Bilder, bereits gezippte Dateien in VARBINARY-Spalten), bringt SQL-Server-Backup-Kompression wenig bis nichts — und kostet trotzdem CPU. In solchen Spezialfällen lohnt es sich, die Kompressionsrate zu messen.

-- Backup Compression aktivieren (kein Neustart nötig)

EXEC sp_configure 'backup compression default', 1;

RECONFIGURE;

 

-- Aktuelle Kompressionsrate letzter Backups messen

SELECT TOP 10

    database_name,

    backup_finish_date,

    backup_size / 1048576         AS backup_groesse_mb,

    compressed_backup_size / 1048576 AS komprimiert_mb,

    CAST(100.0 - (compressed_backup_size * 100.0 / backup_size)

         AS DECIMAL(5,1))          AS einsparung_prozent

FROM msdb.dbo.backupset

WHERE type = 'D'                   -- D = Full Backup

  AND compressed_backup_size > 0   -- nur komprimierte Backups

ORDER BY backup_finish_date DESC;

Min Memory per Query und Memory Grant

min memory per query legt fest, wie viel Speicher (in KB) einer Abfrage mindestens für Sort- und Hash-Operationen gewährt wird. Der Default ist 1.024 KB (1 MB). Für die meisten Workloads ist das vernünftig.

Der Zusammenhang mit Memory Grants ist wichtig: SQL Server schätzt vor der Ausführung, wie viel Speicher eine Abfrage braucht. Wenn diese Schätzung falsch ist (wegen schlechter Statistiken), kann die Abfrage entweder zu viel Speicher reservieren (andere Abfragen warten) oder zu wenig bekommen und auf TempDB spillen. Kapitel 12 behandelt Memory Grants und Spills ausführlich — mitsamt den Diagnose-DMVs und den typischen Ursachen für übermäßige Grants.

Max Worker Threads: Wann der Threadpool ausgeht

max worker threads legt fest, wie viele Worker Threads SQL Server maximal erzeugen darf. Der Default ist 0 — was bedeutet, dass SQL Server die Zahl automatisch berechnet, basierend auf der Anzahl der logischen CPUs. Auf einem Server mit 8 logischen CPUs ergibt sich automatisch ein Wert von 288 Worker Threads, bei 32 CPUs sind es 448, bei 64 CPUs 640. Diese Formel skaliert bewusst sublinear: mehr Kerne brauchen nicht proportional mehr Threads.

Was passiert, wenn der Threadpool erschöpft ist? SQL Server kann keine neuen Sessions annehmen. Neue Verbindungsversuche werden mit dem Fehler "There are not enough worker threads available in the pool to complete this request" abgewiesen — oder hängen in der Warteschlange bis ein Thread frei wird. Das ist ein ernsthafter Ausfall: bestehende Sessions laufen weiter, aber neue Arbeit kommt nicht mehr rein.

Wie erkennst du das Problem? THREADPOOL ist der Wait Type, der anschlägt, wenn Threads knapp werden. Eine signifikant steigende THREADPOOL-Wait-Zeit in sys.dm_os_wait_stats ist ein klares Signal. Wir behandeln THREADPOOL ausführlich in Kapitel 9 (Wait Statistics) als Teil der CPU-Diagnoseachse — zusammen mit SOS_SCHEDULER_YIELD und CXPACKET als Trio der CPU-bezogenen Wait Types.

-- Aktuelle Worker Thread Nutzung prüfen

SELECT

    max_workers_count,       -- Konfiguriertes Maximum (0 = auto)

    (SELECT COUNT(*) FROM sys.dm_os_workers) AS aktive_workers,

    (SELECT COUNT(*) FROM sys.dm_os_workers

     WHERE state = 'RUNNING') AS laufende_workers

FROM sys.dm_os_sys_info;

 

-- Konfiguriertes Maximum aus sp_configure holen

SELECT

    name,

    value_in_use,

    CASE

        -- Automatische Berechnung simulieren (vereinfacht)

        WHEN value_in_use = 0 THEN

            CASE

                WHEN (SELECT cpu_count FROM sys.dm_os_sys_info) <= 4  THEN 256

                WHEN (SELECT cpu_count FROM sys.dm_os_sys_info) <= 8  THEN 288

                WHEN (SELECT cpu_count FROM sys.dm_os_sys_info) <= 32 THEN 448

                ELSE 640

            END

        ELSE value_in_use

    END AS effektiver_wert

FROM sys.configurations

WHERE name = 'max worker threads';

 

-- THREADPOOL-Wait als Frühwarnsignal

SELECT

    wait_type,

    waiting_tasks_count,

    wait_time_ms / 1000.0 AS wait_sekunden

FROM sys.dm_os_wait_stats

WHERE wait_type = 'THREADPOOL'

  AND waiting_tasks_count > 0;

-- waiting_tasks_count > 0 deutet auf akuten Threadpool-Druck hin

Wann manuell anpassen? Fast nie. Der Auto-Wert von SQL Server ist in der Praxis für die überwiegende Mehrheit aller Installationen korrekt. Anpassung ist sinnvoll bei: sehr vielen gleichzeitigen Kurzverbindungen (mehr als 200–300 Sessions gleichzeitig auf einem 8-Core-Server), oder bei speziellen Workloads mit vielen parallelen Backups oder DBCC-Operationen, die viele parallele Threads erzeugen. Wenn du max worker threads anhebst, tue es schrittweise — und immer mit Monitoring. Zu viele Threads verursachen erhöhten Speicherverbrauch (jeder Thread benötigt ca. 0,5 MB Stack-Speicher auf 64-Bit) und können den Scheduler unter Druck setzen.

 

Warnung: Max Worker Threads: Keine Wunderkur
Auf einem System mit THREADPOOL-Waits ist die Ursache fast nie "zu wenig Threads". Die Ursache ist fast immer "zu viele blockierende oder lange laufende Sessions, die Threads festhalten".
Mehr Threads erhöhen nur die Kapazität — wenn zu viele Sessions blockieren, werden auch mehr Threads blockiert. Die richtige Antwort auf THREADPOOL-Waits ist Blocking-Analyse (Kapitel 14), nicht Thread-Erhöhung.
THREADPOOL-Waits ohne gleichzeitige Blocking-Probleme können auch auf schlechte Connection-Pool-Konfiguration in der Applikation hinweisen — zu viele Connections, die offen gehalten werden ohne aktive Arbeit. Das ist ein Applikationsproblem, kein SQL-Server-Problem.

 

Remote Query Timeout: Der vergessene Parameter

remote query timeout legt fest, wie lange eine Remote-Query (über Linked Server) warten darf, bevor sie abbricht. Default: 600 Sekunden (10 Minuten). In vielen Umgebungen mit Linked Servers ist dieser Wert entweder viel zu hoch (Verbindungen hängen 10 Minuten) oder wurde auf 0 gesetzt — was bedeutet: kein Timeout, warte ewig.

0 ist hier tatsächlich einer der schlechteren Defaults. Wenn ein Linked Server nicht antwortet, hängt deine Abfrage ewig und blockiert Ressourcen. Setze einen vernünftigen Wert — 30–60 Sekunden für interaktive Operationen, höher nur für bekannte Long-Runner.

Optimize for Ad-hoc Workloads: Fast immer ein Ja

"Optimize for Ad-hoc Workloads" ist eine der seltenen Einstellungen, die fast universell empfehlenswert ist und keinen negativen Nebeneffekt hat. Was sie macht: Statt einen vollständigen Ausführungsplan für eine Ad-hoc-Abfrage beim ersten Ausführen zu cachen, speichert SQL Server zunächst nur einen "Stub" — einen kleinen Platzhalter.

Erst, wenn dieselbe Abfrage ein zweites Mal ausgeführt wird, wird der vollständige Plan gecached. Das spart erheblich RAM bei Workloads mit vielen einmaligen Ad-hoc-Abfragen — also bei fast allen Anwendungen, die keine konsequente Parametrisierung nutzen. Der Zusammenhang mit Plan Cache und Parameter Sniffing ist eng: Kapitel 18 behandelt, wie Plan-Cache-Druck, Ad-hoc-Workloads und Parameter Sniffing zusammenspielen. Wer jetzt schon neugierig ist, kann vorblättern — aber der vollständige Kontext kommt in Kapitel 18.

-- Sofort aktivieren — kein Neustart nötig, kein Risiko

-- Prüfen ob bereits aktiv:

SELECT name, value_in_use FROM sys.configurations

WHERE name = 'optimize for ad hoc workloads';

 

-- Aktivieren:

EXEC sp_configure 'optimize for ad hoc workloads', 1;

RECONFIGURE;

 

-- Effekt prüfen: Plan Cache vor und nach vergleichen

-- (Anteil der Adhoc-Einträge mit usecounts = 1 sollte sinken)

SELECT

    objtype,

    COUNT(*)                   AS eintraege,

    SUM(usecounts)             AS gesamt_verwendungen,

    SUM(size_in_bytes) / 1048576 AS gesamt_mb

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY gesamt_mb DESC;

-- Adhoc-Einträge mit objtype = 'Adhoc' und usecounts = 1 sind Plan-Cache-Verschwendung

sp_configure Referenz: Die wichtigsten Einstellungen im Überblick

Die folgende Tabelle fasst alle wesentlichen sp_configure-Optionen zusammen, die in diesem Kapitel behandelt wurden — plus einige weitere, die zwar selten geändert werden müssen, aber dennoch bekannt sein sollten. Der vollständige Anhang C enthält alle Optionen mit Standardwerten und Empfehlungen.

 

Option	Default	Empfehlung	Begründung
max server memory (MB)	2147483647	RAM – OS-Reserve – Overhead	Default lässt SQL Server unbegrenzt wachsen
min server memory (MB)	0	0 lassen	SQL gibt Speicher frei, wenn OS ihn braucht
max degree of parallelism	0	Kerne/NUMA-Node, max. 8	Verhindert einzelne Abfragen mit 100% CPU
cost threshold for parallelism	5	25–50 (OLTP)	Default-5 ist für moderne Hardware viel zu niedrig
optimize for ad hoc workloads	0	1 (immer aktivieren)	Spart Plan-Cache-RAM ohne Nebeneffekte
priority boost	0	0 — niemals ändern!	Deprecated, destabilisiert das OS
lightweight pooling	0	0 — niemals ändern!	Inkompatibel mit CLR und anderen Features
max worker threads	0 (auto)	0 lassen	SQL berechnet korrekt basierend auf CPU-Anzahl
remote query timeout (s)	600	30–60 für interaktive	600s = 10 Minuten Hänger möglich
backup compression default	0	1 empfohlen	Spart Backup-IO und -Zeit deutlich, kaum CPU-Last
fill factor (%)	0	0 lassen	0 = 100% Füllung, Fragmentierung über Index-Maintenance
network packet size (B)	4096	4096 lassen	Anpassen nur bei spezifischen Netzwerkproblemen
min memory per query (KB)	1024	1024 lassen	Nur bei sehr spezifischen Memory-Grant-Problemen
xp_cmdshell	0	0 — nur, wenn zwingend nötig	Sicherheitsrisiko; dokumentieren, wenn aktiviert
clr enabled	0	0, wenn CLR nicht genutzt	Aktivieren nur, wenn CLR-Objekte vorhanden

Tab. 5.4: Wichtige sp_configure-Optionen mit Empfehlungen

 

Server Configuration Best-Practice-Checkliste

Diese Checkliste ist dein erster Halt bei jedem neuen Server, jeder neuen Installation und jedem Server-Audit. Geh sie durch, dokumentiere die Ist-Werte, und passe an wo nötig. Ein Punkt, der besonders häufig vergessen wird: IFI und LPIM sind keine sp_configure-Optionen — sie müssen über Windows-Sicherheitsrichtlinien gesetzt werden.

 

Einstellung	Prüfabfrage / Prüfmethode	Soll-Zustand
max server memory	SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)'	Nicht 2147483647 (Default)
MAXDOP	SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism'	Nicht 0 auf Mehrcore-Systemen
Cost Threshold	SELECT value_in_use FROM sys.configurations WHERE name = 'cost threshold for parallelism'	Nicht 5 (Default)
Priority Boost	SELECT value_in_use FROM sys.configurations WHERE name = 'priority boost'	Muss 0 sein!
Lightweight Pooling	SELECT value_in_use FROM sys.configurations WHERE name = 'lightweight pooling'	Muss 0 sein!
Ad-hoc Workloads	SELECT value_in_use FROM sys.configurations WHERE name = 'optimize for ad hoc workloads'	Sollte 1 sein
Backup Compression	SELECT value_in_use FROM sys.configurations WHERE name = 'backup compression default'	Sollte 1 sein
Instant File Initialization	SELECT instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server%'	Sollte Y sein
Lock Pages in Memory	sys.dm_os_process_memory: locked_page_allocations_kb	Dokumentiert, wenn aktiviert; nicht standard
TempDB-Dateien	SELECT COUNT(*) FROM tempdb.sys.database_files WHERE type = 0	Mindestens so viele wie CPU-Kerne (max. 8)
TempDB gleiche Größen	SELECT size * 8 / 1024 AS mb FROM tempdb.sys.database_files WHERE type = 0	Alle Datendateien gleich groß
NUMA-Konfiguration	SELECT * FROM sys.dm_os_nodes WHERE node_state_desc <> 'DAC'	NUMA-Nodes sichtbar und korrekt?

Tab. 5.5: Server Configuration Checkliste

 

Das sp_configure-Audit-Script

Das folgende Script gibt auf einen Blick alle Einstellungen aus, die vom empfohlenen Wert abweichen. Ideal als erstes Script bei jedem Server-Audit — in 30 Sekunden weißt du, wo du anfangen musst.

-- Schnell-Audit der wichtigsten Einstellungen

-- Gibt alle Einstellungen aus, die vom Optimum abweichen

SELECT

    name,

    value_in_use,

    CASE name

        WHEN 'priority boost'               THEN CASE WHEN value_in_use = 0 THEN 'OK' ELSE 'KRITISCH!' END

        WHEN 'lightweight pooling'           THEN CASE WHEN value_in_use = 0 THEN 'OK' ELSE 'KRITISCH!' END

        WHEN 'optimize for ad hoc workloads' THEN CASE WHEN value_in_use = 1 THEN 'OK' ELSE 'Empfehlung: 1' END

        WHEN 'backup compression default'    THEN CASE WHEN value_in_use = 1 THEN 'OK' ELSE 'Empfehlung: 1' END

        WHEN 'max server memory (MB)'        THEN CASE WHEN value_in_use < 2000000000 THEN 'OK' ELSE 'Warnung: Limit setzen!' END

        WHEN 'max degree of parallelism'     THEN CASE WHEN value_in_use > 0 THEN 'OK' ELSE 'Empfehlung: > 0' END

        WHEN 'cost threshold for parallelism' THEN CASE WHEN value_in_use >= 25 THEN 'OK' ELSE 'Empfehlung: 25+' END

        ELSE 'Prüfen'

    END AS bewertung

FROM sys.configurations

WHERE name IN (

    'priority boost',

    'lightweight pooling',

    'optimize for ad hoc workloads',

    'backup compression default',

    'max server memory (MB)',

    'max degree of parallelism',

    'cost threshold for parallelism'

)

ORDER BY name;

 

-- IFI-Status prüfen (separater Check, kein sp_configure)

SELECT

    servicename,

    instant_file_initialization_enabled AS ifi_aktiv

FROM sys.dm_server_services

WHERE servicename LIKE 'SQL Server%'

  AND servicename NOT LIKE '%Agent%';

 

-- TempDB-Dateien: Anzahl und Größenkonsistenz

SELECT

    COUNT(*) AS anzahl_datendateien,

    MAX(size) - MIN(size) AS groessen_differenz_pages,  -- Sollte 0 sein!

    MIN(size) * 8 / 1024 AS kleinste_datei_mb,

    MAX(size) * 8 / 1024 AS groesste_datei_mb

FROM tempdb.sys.database_files

WHERE type = 0;  -- 0 = Datendateien, 1 = Log

Diagnose: Fehlkonfigurationen erkennen

 

Warnung: Symptome: Fehlkonfigurierter Server
• Hohe CPU-Auslastung bei kurzen OLTP-Abfragen: Cost Threshold zu niedrig, zu viel Parallelismus
• Sporadische Systeminstabilität, unerklärliche IO-Pausen: Priority Boost aktiviert
• Plan Cache mit vielen Single-Use Plans (> 50% aller Einträge): Ad-hoc-Workloads nicht optimiert
• OS pagt aus (Windows Event Log: Speicher niedrig): max server memory nicht gesetzt
• Abfragen mit MAXDOP = 64 auf einem 64-Core-Server: MAXDOP auf 0 (Default), eine Abfrage blockiert alles
• Autogrowth-Events in Häufung (> 5 pro Stunde): IFI nicht aktiv oder Wachstumsschritte zu klein
• THREADPOOL-Waits in sys.dm_os_wait_stats: Worker-Thread-Engpass oder exzessives Blocking
• Backups dauern unerwartet lang und belegen viel Storage: Backup Compression nicht aktiviert

 

 

Tipp: So misst du das
— Vollständige Konfigurationsübersicht mit Abweichungsmarkierung:
SELECT name, value, value_in_use, description, is_dynamic
FROM sys.configurations
ORDER BY name;
— Tatsächlichen SQL-Server-Speicherverbrauch prüfen:
SELECT
physical_memory_in_use_kb / 1024 AS sql_verbrauch_mb,
page_fault_count,
memory_utilization_percentage
FROM sys.dm_os_process_memory;
— CXPACKET-Waits prüfen (Parallelismus-Overhead):
SELECT wait_type, waiting_tasks_count, wait_time_ms,
wait_time_ms / 1000.0 / 60 AS wait_minuten
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET', 'CXCONSUMER', 'THREADPOOL')
ORDER BY wait_time_ms DESC;

 

 

Hintergrund: Typische Fehlinterpretationen
• "MAXDOP = 1 ist immer sicher": Nein. Bei analytischen Abfragen und Batch-Jobs kann MAXDOP = 1 die Laufzeit verdreifachen. Sicher, aber nicht performant.
• "Hoher Plan Cache = Problem": Der Plan Cache nutzt verfügbaren RAM sinnvoll. Erst, wenn der Cache aggressiv verdrängt wird (niedrige PLE) oder voll mit Single-Use-Plans, ist es ein Problem.
• "Priority Boost macht SQL schneller": Auf einzelnen Testmaschinen vielleicht kurzfristig messbar — in Produktion destabilisiert es das gesamte System. Der Schuss geht nach hinten.
• "min server memory anheben spart Zeit": min server memory verhindert, dass SQL Server Speicher freigibt. Das klingt gut, bedeutet aber, dass anderen Prozessen (OS!) kein Speicher mehr abgegeben wird. Selten eine gute Idee.
• "Mehr Worker Threads lösen THREADPOOL-Waits": Fast nie. THREADPOOL-Waits entstehen durch zu viele blockierte Sessions, nicht durch zu wenige Threads. Die Wurzel liegt bei Blocking-Problemen.
• "IFI ist ein Sicherheitsrisiko": Nur theoretisch. In der Praxis auf einem dedizierten SQL-Server-System ohne Sicherheitsbedenken bzgl. früherer Prozessdaten: IFI aktivieren.

 

 

Tipp: Erste Gegenmaßnahmen
• max server memory nicht gesetzt → Sofort setzen (wirkt ohne Neustart): EXEC sp_configure 'max server memory (MB)', <Wert>; RECONFIGURE;
• Priority Boost aktiviert → Auf 0 setzen + Neustart planen: EXEC sp_configure 'priority boost', 0; RECONFIGURE;
• Viele CXPACKET-Waits → Cost Threshold erhöhen (25–50), Plan Cache leeren (DBCC FREEPROCCACHE — mit Bedacht!)
• Viele Single-Use Plans → optimize for ad hoc workloads = 1 (sofort, kein Neustart)
• IFI nicht aktiv → Windows-Sicherheitsrichtlinie setzen (secpol.msc), SQL-Dienst neu starten
• TempDB nur 1 Datei → Bei nächstem Wartungsfenster auf CPU-Kern-Anzahl (max. 8) erweitern
• Über alle Änderungen: Baseline vorher nehmen (sys.dm_os_wait_stats, sys.dm_os_performance_counters), Änderung dokumentieren, 24h beobachten

 

Zusammenfassung

sp_configure ist kein Spielzeug — aber auch keine Geheimwissenschaft. Die wirklich wichtigen Einstellungen lassen sich an zwei Händen abzählen: max server memory setzen (immer!), MAXDOP und Cost Threshold gemeinsam konfigurieren, Ad-hoc-Workloads optimieren, Backup Compression aktivieren, und Priority Boost sowie Lightweight Pooling niemals aktivieren.

Jenseits von sp_configure gibt es zwei weitere Konfigurationsschrauben, die mindestens ebenso wichtig sind und häufig vergessen werden: Instant File Initialization (IFI) spart bei jedem Dateiwachstum Zeit und verhindert, dass Autogrowth-Events zu spürbaren Pausen werden. TempDB-Erstkonfiguration — Anzahl und Gleichgewicht der Datendateien — legt den Grundstein dafür, dass TempDB-Contention kein Thema wird.

Die wichtigsten Erkenntnisse dieses Kapitels:

max server memory: Default (unbegrenzt) ist gefährlich — immer explizit setzen, OS-Reserve einkalkulieren

MAXDOP: Kerne pro NUMA-Node, max. 8 für OLTP — nie auf 0 lassen auf Mehrcore-Systemen

Cost Threshold: 5 ist für moderne Hardware viel zu niedrig — 25 bis 50 ist realistisch

Priority Boost: Deprecated, gefährlich, niemals aktivieren — falls aktiviert, sofort deaktivieren

Optimize for Ad-hoc Workloads: Fast immer eine gute Idee, kein Risiko

Backup Compression: An. Immer. Ausnahmen bestätigen die Regel, sind aber selten.

Instant File Initialization: SeManageVolumePrivilege setzen, Neustart, fertig — dramatischer Effekt bei Autogrowth

TempDB: Mindestens so viele Datendateien wie CPU-Kerne (max. 8), alle gleich groß

Max Worker Threads: Auto-Berechnung vertrauen; THREADPOOL-Waits deuten auf Blocking, nicht auf Threads

Änderungen immer dokumentieren und mit Baseline-Vergleich validieren

Ausblick auf Kapitel 6

Serverkonfiguration auf Instanzebene ist ein Teil der Geschichte. Der andere Teil spielt sich auf Datenbankebene ab: Auto-Close, Auto-Shrink, Recovery Model, Kompatibilitätslevel und VLF-Konfiguration. Kapitel 6 zeigt, welche Datenbankeinstellungen dich still und leise Performance kosten — und warum Auto-Shrink eines der schlechtesten Features ist, das je in SQL Server eingebaut wurde. Es defragmentiert zuverlässig alle Indizes, während es den freien Speicherplatz zurückgibt — und wurde vermutlich von jemandem entwickelt, der danach das Unternehmen verlassen hat. Spoiler: Es ist in SQL Server immer noch da. Und manche Leute haben es aktiviert.

 

Kapitel 6