dtcSoftware - powerShellDba

📌 Was ist das Kompatibilitätslevel?

GRUNDLAGEN

Das Kompatibilitätslevel (compatibility level) einer Datenbank definiert, welche Version des Abfrageoptimierers SQL Server verwendet und welche sprachlichen Features sowie Syntaxregeln aktiv sind. Es ist nicht die SQL Server-Version der Instanz, sondern eine datenbankbezogene Einstellung. Typische Werte: 100 (SQL Server 2008), 110 (2012), 120 (2014), 130 (2016), 140 (2017), 150 (2019), 160 (2022), 170 (2025).

Nach einem Upgrade der SQL Server-Instanz bleibt das Kompatibilitätslevel zunächst unverändert. Das ist ein Sicherheitsmechanismus: Sie können die Vorteile der neuen Version nutzen, müssen aber nicht sofort den neuen Optimierer aktivieren.

💡 Wichtiger Punkt: Durch das Erhöhen des Levels ändern Sie kein Datenbankschema und keine Daten. Allerdings können sich Ausführungspläne drastisch ändern – das ist die größte Risikoquelle.

🔍 Aktuelles Kompatibilitätslevel ermitteln

DIAGNOSE

-- Für eine bestimmte Datenbank
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'IhreDatenbank';

-- Alle Datenbanken mit Level & Version
SELECT 
  name,
  compatibility_level,
  CASE compatibility_level
    WHEN 100 THEN 'SQL Server 2008'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
    WHEN 130 THEN 'SQL Server 2016'
    WHEN 140 THEN 'SQL Server 2017'
    WHEN 150 THEN 'SQL Server 2019'
    WHEN 160 THEN 'SQL Server 2022'
    WHEN 170 THEN 'SQL Server 2025'
    ELSE 'Älter oder benutzerdefiniert'
  END AS SQL_Version
FROM sys.databases
ORDER BY name;

✍️ Kompatibilitätslevel ändern – Der Befehl

SYNTAX

Die Änderung erfolgt mit ALTER DATABASE ... SET COMPATIBILITY_LEVEL. Sie benötigt die Mitgliedschaft in der Rolle db_owner oder sysadmin.

-- Beispiel: Datenbank auf SQL Server 2022 Level (160) stellen
ALTER DATABASE [MeineDatenbank] SET COMPATIBILITY_LEVEL = 160;

-- Wieder auf 150 (SQL Server 2019) zurücksetzen
ALTER DATABASE [MeineDatenbank] SET COMPATIBILITY_LEVEL = 150;

⚠️ Wichtiger Hinweis: Die Änderung wirkt sofort (kein Neustart nötig). Bereits im Cache vorhandene Ausführungspläne bleiben erhalten? – sie werden bei erneuter Ausführung neu kompiliert. Dennoch kann es zu plötzlichen Performance-Einbrüchen kommen.

⚠️ Typische Risiken und bekannte Probleme

FALLSTRICKE

Planregressionen: Der neue Optimierer wählt für bestimmte Abfragen schlechtere Pläne – häufig aufgrund veralteter Statistiken oder geänderter Kardinalitätsschätzungen (z. B. die neue CE ab Level 120).
Nicht unterstützte Syntax: Wenn Sie das Level senken (z. B. von 150 auf 130), könnten Abfragen fehlschlagen, die neue Schlüsselwörter (z. B. TRIM, STRING_AGG) verwenden.
Veränderte Sortierreihenfolge bei einigen Unicode-Operationen – selten, aber möglich.
Unterschiedliche Ergebnisse bei Aggregatsfunktionen mit DISTINCT oder ORDER BY – extrem selten, aber dokumentiert.
Probleme mit Always On Availability Groups: Wenn primäres und sekundäres Replikat unterschiedliche Level haben, kann dies zu Fehlern bei automatischen Upgrades führen. In der Praxis sollten alle Replikate dasselbe Level haben.
Query Store Fehler: Wenn der Query Store aktiv ist, kann ein neues Level zu veralteten Plänen führen – aber nicht direkt zu Fehlern.

🚨 Häufigstes Problem: Ein Wechsel von Level 100/110/120 auf 130+ aktiviert die neue Kardinalitätsschätzung (Cardinality Estimator, CE). Diese kann bei manchen Abfragen zu massiven Unterschätzungen oder Überschätzungen führen – mit entsprechenden Spills oder Zeitüberschreitungen.

🛡️ Best Practices für den Level-Wechsel

EMPFEHLUNGEN

Query Store aktivieren – Bevor Sie das Level ändern, stellen Sie sicher, dass der Query Store auf der Datenbank eingeschaltet ist (SET QUERY_STORE = ON). Er zeichnet alle Pläne und Laufzeitstatistiken auf.
Test in einer nicht‑produktiven Umgebung – Kopieren Sie die Datenbank auf eine Testinstanz mit gleicher Version und führen Sie eine repräsentative Workload aus.
Statistiken aktualisieren – Veraltete Statistiken verschlimmern Planregressionen. Führen Sie EXEC sp_updatestats oder UPDATE STATISTICS WITH FULLSCAN durch.
Schrittweises Vorgehen – Gehen Sie nicht direkt von 100 auf 160, sondern über Zwischenschritte (z. B. 100→110→120→130). So können Sie isolieren, ab welchem Level Probleme auftreten.
Verwenden Sie ALTER DATABASE SCOPED CONFIGURATION – Sie können für einzelne Abfragen oder für die gesamte Datenbank den alten CE (LEGACY_CARDINALITY_ESTIMATION) erzwingen, ohne das Level zu senken.
Beobachten Sie kritische Abfragen – Nutzen Sie den Query Store, um die Top‑10 der teuersten Abfragen vor und nach dem Wechsel zu vergleichen.
Planen Sie ein Wartungsfenster – Auch wenn die Änderung online ist, sollten Sie einen Zeitpunkt mit geringer Last wählen, um bei Regressionen schnell zurückzusetzen.

💡 Pro‑Tipp: Nutzen Sie den Query Store‑Bericht „Regressionsabfragen“ – er zeigt automatisch Abfragen an, die nach einer Änderung des Kompatibilitätslevels deutlich langsamer wurden. Sie können dann einzelne Pläne mit FORCE PLAN korrigieren.

🔧 Fallback: Alten Kardinalitätsschätzer verwenden

KOMPATIBILITÄT

Wenn nach dem Level-Wechsel nur die Kardinalitätsschätzung Probleme macht (nicht die Syntax), können Sie ohne Zurücksetzen des gesamten Levels den alten CE (vor SQL Server 2014) für die gesamte Datenbank oder einzelne Abfragen aktivieren.

-- Für die gesamte Datenbank: Legacy CE erzwingen
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Für eine einzelne Abfrage (Query-Hint)
SELECT ... OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

-- Wieder auf Standard (neuen CE) setzen
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;

📌 Hinweis: Die datenbankweite Einstellung ist eine kluge Übergangslösung, bis Sie die Ursache der Regression behoben haben (z. B. durch Indexänderungen oder Statistikupdates).

📊 Query Store – Ihr bester Freund beim Level-Wechsel

ÜBERWACHUNG

Der Query Store speichert Ausführungspläne und Laufzeitstatistiken über Zeiträume hinweg. Nach einer Kompatibilitätslevel-Änderung können Sie mit wenigen Klicks sehen, welche Abfragen sich verschlechtert haben.

-- Query Store aktivieren (falls nicht bereits geschehen)
ALTER DATABASE [MeineDB] SET QUERY_STORE = ON; 
ALTER DATABASE [MeineDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- Abfragen mit Leistungsrückgang nach einem bestimmten Zeitpunkt finden
SELECT 
  qt.query_sql_text,
  qsrs.avg_duration,
  qsrs.avg_cpu_time,
  qsrs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrs ON rs.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
WHERE qsrs.start_time > '2025-01-01' -- Datum des Level-Wechsels
ORDER BY qsrs.avg_duration DESC;

💡 Kurz gesagt: Der Query Store liefert Ihnen harte Fakten, bevor Ihre Benutzer sich über langsame Abfragen beschweren. Richten Sie Alerts für dramatische Verschlechterungen ein.

📋 Kompatibilitätslevel – Zuordnung zu SQL Server Versionen

REFERENZ

Kompatibilitätslevel	SQL Server Version	Wichtige Optimierungen
160	SQL Server 2022	Parameter‑Sensitive Plan Optimierung, Intelligente QDS‑Bereinigung
150	SQL Server 2019	Intelligente Leistungsoptimierung, Tabellen‑Variablen verzögerte Kompilierung
140	SQL Server 2017	Adaptive Joins, automatische Plankorrektur
130	SQL Server 2016	Neue Kardinalitätsschätzung, In-Memory OLTP Verbesserungen
120	SQL Server 2014	In-Memory OLTP (Hekaton), Sicherungs‑Komprimierung
110	SQL Server 2012	Columnstore Indizes, Always On
100	SQL Server 2008 / 2008 R2	Plan Guides, Change Tracking

💡 Merke: Jedes neue Level bringt neue Optimiererfunktionen. Nicht alle sind immer vorteilhaft für jede Arbeitslast.

✅ Checkliste für einen sicheren Level-Wechsel

PRAXIS

Query Store aktivieren – mindestens 7 Tage vor der Änderung laufen lassen.
Vollständiges Backup der Datenbank – für den Fall, dass Sie das Level wieder zurücknehmen müssen (auch wenn die Änderung nicht in der Backup-Struktur gespeichert wird, dient es zur Absicherung).
Statistiken aktualisieren – EXEC sp_updatestats.
Test in Staging – mit einer Kopie der Produktionsdatenbank.
Wechsel während Wartungsfenster durchführen – geringe Last.
Nach dem Wechsel: Query Store auswerten – speziell auf „Regressed Queries“ prüfen.
Bei Problemen sofort zurücksetzen – auf das alte Level und ggf. Legacy CE aktivieren.
Dokumentation – Notieren Sie das neue Level und eventuelle manuelle Plan‑Forcings.

⚠️ Häufige Fehler und ihre Lösung

TROUBLESHOOTING

Fehler: „Database cannot be set to compatibility level … because it contains a table with a columnstore index that was created in an earlier version.“
→ Erstellen Sie den Columnstore Index neu oder heben Sie das Level schrittweise an.
Fehler: „The compatibility level is not supported by this version of SQL Server“
→ Sie versuchen ein Level zu setzen, das höher ist als die aktuelle SQL Server-Instanzversion (z. B. 160 auf einem 2019er Server).
Plötzliche Timeouts nach Level-Wechsel
→ Meist Planregression. Nutzen Sie Query Store, um die betroffene Abfrage zu identifizieren und einen alten Plan zu erzwingen oder den CE umzustellen.
Keine Veränderung trotz Level-Wechsel?
→ Überprüfen Sie mit SELECT compatibility_level FROM sys.databases WHERE name = ..., ob die Änderung wirklich angekommen ist.

🚀 Fazit: Das Ändern des Kompatibilitätslevels ist ein mächtiges Werkzeug, um Performanceverbesserungen neuerer SQL Server-Versionen zu nutzen. Mit Query Store, gründlichen Tests und einem schrittweisen Vorgehen minimieren Sie das Risiko von Regressionen erheblich.