T-SQL

Max Degree eines SQL-Servers berechnen und setzen

1. Aktivierung der erweiterten Konfigurationsoptionen

Korrekte Verwendung von sp_configure 'show advanced options', 1 (Plural, vollständiger Name).
RECONFIGURE übernimmt die Änderung.

2. Ermittlung der Hardwareparameter

@CoreCount – Anzahl der logischen CPUs (inkl. Hyper-Threading) aus sys.dm_os_sys_info.
@NumaNodes – Anzahl der NUMA-Knoten durch Zählen der unterschiedlichen parent_node_id in sys.dm_os_schedulers. Diese Methode ist zuverlässiger als die ursprüngliche Abfrage über memory_node_id.
Falls keine NUMA-Knoten erkannt werden (z. B. bei einer VM), wird @NumaNodes = 1 gesetzt, um Division durch Null zu vermeiden.

3. Berechnung des MAXDOP nach etablierten Empfehlungen

Die Logik folgt den Microsoft-Richtlinien (siehe KB2806535):

Bei mehr als 4 logischen Kernen:
- @CoresPerNuma = @CoreCount / @NumaNodes – das ist die Anzahl der Kerne pro NUMA-Knoten.
- MAXDOP wird auf diesen Wert gesetzt, jedoch auf maximal 8 begrenzt (typischer Wert für OLTP-Systeme; für Data Warehousing könnte man auf 16 erhöhen).
- Anschließend wird der Wert auf die nächste gerade Zahl abgerundet (z. B. 7 → 6), was bei einigen Workloads die Effizienz verbessern kann.
- Der Wert wird niemals kleiner als 1.
Bei 4 oder weniger Kernen:
- MAXDOP = 0 (d. h. SQL Server verwendet alle verfügbaren Kerne, was bei geringer Kernanzahl unkritisch ist).
- Diese Entscheidung ist besser als der ursprüngliche ELSE-Zweig, der die Konfiguration komplett unterließ.

4. Setzen der Konfiguration

Dynamisches SQL (sicher über sp_executesql) wird verwendet, um sp_configure mit dem berechneten Wert aufzurufen.
RECONFIGURE ohne WITH OVERRIDE ist ausreichend, da es sich um eine normale, nicht kritische Option handelt.
Wichtig: Das Setzen erfolgt sowohl im THEN- als auch im ELSE-Zweig – der ursprüngliche Fehler wurde behoben.

5. Setzen des Cost Threshold for Parallelism

Der Wert wird auf 50 gestzt.
Anmerkung: Der Standardwert ist 5. Ein Wert von 50 bedeutet, dass ein Abfrageplan nur dann parallel ausgeführt wird, wenn seine geschätzten Kosten über 50 liegen.

6. Ausgabe der Ergebnisse

Eine abschließende SELECT-Abfrage zeigt die ermittelten Hardwareparameter und den tatsächlich gesetzten MAXDOP an.
Zusätzlich werden informative PRINT-Meldungen ausgegeben.

Hinweise zur Verwendung

Das Skript sollte mit ausreichenden Rechten (sysadmin-Rolle) ausgeführt werden.
Die Änderungen von MAXDOP und Cost Threshold treten sofort in Kraft, ohne dass ein Neustart des SQL Server-Dienstes erforderlich ist.

-- Skript zur automatischen MAXDOP-Konfiguration
SET NOCOUNT ON;
GO

-- 1. Erweiterte Konfigurationsoptionen aktivieren (korrekter Name)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- 2. Aktuellen MAXDOP anzeigen (optional)
EXEC sp_configure 'max degree of parallelism';
GO

-- 3. Hardware-Informationen ermitteln
DECLARE @CoreCount INT = (SELECT cpu_count FROM sys.dm_os_sys_info);                     -- logische Kerne
DECLARE @NumaNodes INT = (SELECT COUNT(DISTINCT parent_node_id) 
                          FROM sys.dm_os_schedulers 
                          WHERE status = 'VISIBLE ONLINE' AND parent_node_id < 64);      -- NUMA-Knoten

-- Sicherstellen, dass @NumaNodes mindestens 1 ist
IF @NumaNodes IS NULL OR @NumaNodes = 0 SET @NumaNodes = 1;

DECLARE @MaxDOP INT;

-- 4. MAXDOP nach bewährten Regeln berechnen
IF @CoreCount > 4 
BEGIN
    -- Empfehlung: MAXDOP = Kerne pro NUMA-Knoten (abgerundet)
    DECLARE @CoresPerNuma INT = @CoreCount / @NumaNodes;
    
    -- MAXDOP auf maximal 8 für OLTP-Systeme begrenzen (bei Data Warehouse ggf. anpassen)
    SET @MaxDOP = @CoresPerNuma;
    IF @MaxDOP > 8 SET @MaxDOP = 8;
    
    -- Optional: MAXDOP auf nächste gerade Zahl abrunden (kann bei manchen Workloads helfen)
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);
    IF @MaxDOP < 1 SET @MaxDOP = 1;
    
    PRINT 'Empfohlener MAXDOP (basierend auf Kernen pro NUMA-Knoten, max. 8): ' + CAST(@MaxDOP AS VARCHAR);
END
ELSE
BEGIN
    -- Bei 4 oder weniger Kernen ist Parallelisierung oft nicht vorteilhaft
    SET @MaxDOP = 0;   -- 0 = SQL Server entscheidet (max. alle Kerne)
    PRINT 'Weniger als 5 logische Kerne: MAXDOP = 0 (keine Limitierung empfohlen)';
END

PRINT '----------------------------------------------------------------------------------------';

-- 5. MAXDOP tatsächlich setzen (sowohl im THEN- als auch im ELSE-Zweig)
DECLARE @SqlCmd NVARCHAR(500);
SET @SqlCmd = N'EXEC sp_configure ''max degree of parallelism'', ' + CAST(@MaxDOP AS NVARCHAR);
EXEC sp_executesql @SqlCmd;
RECONFIGURE;

-- 6. Cost Threshold for Parallelism auf 50 setzen
PRINT 'Setze cost threshold for parallelism auf 50 (Standard = 5).';
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE WITH OVERRIDE;   -- WITH OVERRIDE wird hier nicht benötigt, ist aber unschädlich

-- 7. Ergebnis anzeigen
SELECT 
    @CoreCount AS [Logische_CPU_Kerne],
    @NumaNodes AS [NUMA_Knoten],
    @CoreCount / @NumaNodes AS [Kerne_pro_Knoten],
    @MaxDOP AS [Gesetzter_MAXDOP];

PRINT '----------------------------------------------------------------------------------------';
PRINT 'Hinweis: Die Änderungen treten sofort in Kraft (kein Neustart nötig).';

{phocadownload view=file|id=36|text=Max Degree eines SQL-Servers berechnen und setzen|target=s}