Max Degree eines SQL-Servers berechnen und setzen

Details
Kategorie: T-SQL

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).';

 

Main Menu