Query Store:
Das Gedächtnis des Optimizers — endlich persistent
Was der Query Store ist — und warum du ihn heute noch aktivieren solltest
Stell dir folgendes Szenario vor: Es ist Montagmorgen, der Controller ruft an, der Monatsabschluss läuft seit Freitagabend auf Hochtouren — und seit heute Nacht plötzlich dreimal so langsam wie letzte Woche. Du öffnest SSMS, schaust in den Plan Cache: Pläne sehen vernünftig aus. Statistiken: aktuell. Indexe: nicht fragmentiert. Was hat sich geändert? Du weißt es nicht. Du hast keinen Recorder gehabt.
Genau das ist das Problem, das der Query Store löst. Er ist der "Flight Recorder" für SQL Server-Abfragen — das Äquivalent zur Blackbox im Flugzeug. Seit SQL Server 2016 speichert er persistent in der Datenbank selbst, welche Abfragen ausgeführt wurden, welche Ausführungspläne dabei entstanden sind, und wie schnell oder langsam sie jeweils liefen. Nicht nur heute — sondern für die letzten 30, 60 oder 90 Tage. Über Neustarts hinweg. Auch, wenn der Plan Cache längst geleert wurde.
Vor dem Query Store war Performance-Analyse nach einem Neustart oft Archäologie mit bloßen Händen: Der Plan Cache war weg, Wait Statistics resettet, keine historischen Abfragedaten. Mit Query Store hast du die komplette Zeitachse: Wann hat sich der Plan geändert? Welcher Plan war besser? Wie war die Performance vor dem Update, verglichen mit danach? All das beantwortet der Query Store aus dem Stand — ohne, dass du vorab irgendetwas vorbereiten musstest, solange er aktiviert war.
Ab SQL Server 2022 ist der Query Store standardmäßig aktiv für neue Datenbanken. Für SQL Server 2016 bis 2019 muss er pro Datenbank manuell aktiviert werden — was viele Administratoren vergessen oder hinauszögern. Das ist ein Fehler. Wenn der Query Store nicht aktiv ist und ein Performance-Problem auftritt, hast du keine historischen Daten. Aktiviere ihn jetzt, nicht nach dem nächsten Vorfall.
|
Hinweis: Querverweise zu anderen Kapiteln |
|---|
|
Plan Regression — die häufigste Anwendung des Query Stores — haben wir in Kapitel 16 (Statistiken, Cardinality Estimator) konzeptionell eingeführt. Parameter Sniffing, das zweithäufigste Szenario, haben wir in Kapitel 18 ausführlich behandelt. Dieses Kapitel erklärt das Werkzeug, mit dem du beides diagnostizierst und behebst. Die Einbettung in eine vollständige Analyse-Methodik folgt in Kapitel 31. |
Query Store aktivieren und richtig konfigurieren
Die Aktivierung ist trivial — die richtige Konfiguration ist es nicht. Vor allem die Standard-Werte sind für Produktionssysteme oft ungeeignet: 100 MB Speicher reichen für eine aktive Datenbank vielleicht zwei Wochen, dann wechselt der Query Store automatisch in READ_ONLY und schreibt keine neuen Daten mehr — lautlos, ohne Fehlermeldung, ohne Alarm. Genau dann, wenn es brennt, fehlen dir die Daten.
-- Query Store aktivieren — auf der Zieldatenbank ausführen, nicht auf master
-- READ_WRITE = aktiv aufzeichnen; READ_ONLY = nur lesen; OFF = deaktiviert
ALTER DATABASE MeineProduktionsdatenbank
SET QUERY_STORE = ON
(
-- Betriebsmodus: READ_WRITE ist das Ziel — alles andere ist suboptimal
OPERATION_MODE = READ_WRITE,
-- Maximale Speichergröße: Standard 100 MB ist viel zu wenig für Produktion
-- Faustregel: 1 % der Datenbankgröße, mindestens 512 MB
MAX_STORAGE_SIZE_MB = 2048,
-- Aggregationsintervall: Alle 60 Minuten werden Statistiken zusammengefasst
-- Kürzere Intervalle = mehr Granularität, mehr Speicherbedarf
INTERVAL_LENGTH_MINUTES = 60,
-- Aufbewahrungsdauer: 30 Tage für Plan Regression mehr als ausreichend
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
-- AUTO: Nur Abfragen mit signifikantem Ressourcenverbrauch erfassen
-- ALL = alles, auch einmalige Ad-hoc-Queries; kann sehr groß werden
QUERY_CAPTURE_MODE = AUTO,
-- Automatisch bereinigen, wenn der Speicher knapp wird (empfohlen)
SIZE_BASED_CLEANUP_MODE = AUTO,
-- Wait Statistics pro Plan erfassen (SQL Server 2017+)
WAIT_STATS_CAPTURE_MODE = ON
);
-- Aktuellen Status prüfen — actual_state_desc sollte READ_WRITE sein
SELECT
actual_state_desc,
current_storage_size_mb,
max_storage_size_mb,
CAST(current_storage_size_mb AS FLOAT)
/ NULLIF(max_storage_size_mb, 0) * 100 AS auslastung_pct,
query_capture_mode_desc,
size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
|
Parameter |
Standard |
Empfehlung Produktion |
Kommentar |
|---|---|---|---|
|
OPERATION_MODE |
READ_WRITE |
READ_WRITE |
Bei Vollspeicher wechselt er automatisch zu READ_ONLY |
|
MAX_STORAGE_SIZE_MB |
100 |
1.000–5.000 |
Standard ist für Produktion meist zu klein |
|
INTERVAL_LENGTH_MINUTES |
60 |
15–60 |
Kürzer = mehr Granularität, mehr Speicher |
|
STALE_QUERY_THRESHOLD_DAYS |
30 |
30–90 |
30 Tage für Regression, 90 für Trendanalyse |
|
QUERY_CAPTURE_MODE |
AUTO |
AUTO |
ALL erfasst zu viel; NONE erfasst gar nichts |
|
SIZE_BASED_CLEANUP_MODE |
AUTO |
AUTO |
Älteste Daten entfernen, wenn Limit erreicht |
|
WAIT_STATS_CAPTURE_MODE |
ON |
ON |
SQL 2017+: Wait Stats pro Plan — wertvoll |
Tabelle 19.1: Query Store Konfigurationsparameter mit Produktionsempfehlungen
|
Warnung: READ_ONLY ist der stille Tod des Query Stores |
|---|
|
Wenn der Query Store sein MAX_STORAGE_SIZE_MB-Limit erreicht, wechselt er automatisch in den READ_ONLY Modus. Er schreibt dann keine neuen Daten mehr — aber er wirft keinen Fehler, keinen Alert, keinen Eintrag im Error Log. Alles sieht normal aus, nur die Zeitstempel der Daten werden älter und älter. Füge eine Monitoring-Abfrage auf actual_state_desc = 'READ_ONLY' in dein Alerting ein. Wenn er in READ_ONLY ist: MAX_STORAGE_SIZE_MB erhöhen und mit ALTER DATABASE … SET QUERY_STORE CLEAR alte Daten bereinigen. |
Das Query Store Datenmodell: Wer speichert was
Der Query Store besteht aus fünf zentralen System-Views, die zusammen das vollständige Bild einer Abfrage über die Zeit ergeben. Das Verständnis der Struktur erspart dir endlose Versuche, die richtigen JOINs zu finden.
|
View |
Inhalt |
Verbindung zu |
|---|---|---|
|
sys.query_store_query_text |
Eigentlicher SQL-Text der Abfrage |
→ query_store_query via query_text_id |
|
sys.query_store_query |
Query-Metadaten, query_hash, object_id |
→ query_store_plan via query_id |
|
sys.query_store_plan |
Ausführungsplan als XML, Kompilierzeit, is_forced_plan |
→ query_store_runtime_stats via plan_id |
|
sys.query_store_runtime_stats |
CPU, Duration, Reads, Writes pro Intervall |
→ runtime_stats_interval via interval_id |
|
sys.query_store_runtime_stats_interval |
Zeitfenster: start_time, end_time |
Anker für alle Zeitabfragen |
|
sys.query_store_wait_stats |
Wait Types pro Plan pro Intervall (SQL 2017+) |
→ query_store_plan via plan_id |
Tabelle 19.2: Query Store System-Views und ihre Beziehungen
Die wichtigste JOIN-Kette für die meisten Abfragen: query_store_query_text → query_store_query → query_store_plan → query_store_runtime_stats → query_store_runtime_stats_interval. Das ist der Standard-Pfad durch das Datenmodell — präge ihn dir ein, dann funktionieren alle folgenden Abfragen intuitiv.
Top-10 CPU-intensivste Abfragen der letzten 24 Stunden
-- Top-10 CPU-Verursacher der letzten 24 Stunden
-- Aufsteigend nach Gesamt-CPU-Verbrauch (Häufigkeit × durchschn. CPU)
SELECT TOP 10
q.query_id,
-- Gesamtverbrauch: Häufigkeit mal durchschnittliche CPU (in ms)
SUM(rs.count_executions * rs.avg_cpu_time) / 1000.0 AS total_cpu_ms,
AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
AVG(rs.avg_duration) / 1000.0 AS avg_dauer_ms,
AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
SUM(rs.count_executions) AS ausfuehrungen,
COUNT(DISTINCT p.plan_id) AS anzahl_plaene,
SUBSTRING(qt.query_sql_text, 1, 200) AS abfragetext
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs
ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval ri
ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
-- Zeitfenster: letzte 24 Stunden (UTC — Query Store arbeitet immer mit UTC)
WHERE ri.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;
Plan Regression erkennen: Wann hat sich was verschlechtert?
Plan Regression ist das klassische Szenario, für das der Query Store entwickelt wurde: Eine Abfrage lief wochenlang mit 80 ms, dann plötzlich mit 4.200 ms. Irgendetwas hat dazu geführt, dass der Optimizer einen anderen — schlechteren — Ausführungsplan gewählt hat. Wie wir in Kapitel 16 gesehen haben, sind die häufigsten Auslöser: Statistik-Update, Index-Rebuild, SQL Server Upgrade, oder ein Kompatibilitätslevel-Wechsel.
Der Query Store macht Regression diagnostizierbar. Er hat den alten Plan, den neuen Plan, und die Laufzeitdaten für beide. Du musst nur die richtige Abfrage stellen.
Fallstudie Musterwerk GmbH: Plan Regression nach SQL Server Update
|
Praxisbeispiel: Musterwerk GmbH (MWSQL01): Update auf SQL Server 2019 |
|---|
|
Nach dem Update von SQL Server 2017 auf 2019 wurde die Stored Procedure usp_GetAuftragsliste plötzlich auffällig langsam — von durchschnittlich 45 ms auf 4.200 ms. Der Query Store auf MWSQL01 war bereits aktiviert und hatte Daten aus den letzten sechs Wochen. Die Analyse zeigte sofort: Plan 1 (alt, 45 ms avg) vs. Plan 2 (neu, 4.200 ms avg). Plan 2 verwendete einen Nested Loop Join statt eines Hash Joins für eine große Ergebnismenge — vermutlich, weil der neue Cardinality Estimator in SQL 2019 die Selektivität falsch schätzte. Sofortmaßnahme: Plan Forcing auf Plan 1. Dauerhafte Lösung: UPDATE STATISTICS für die betroffenen Tabellen, danach war Plan 2 ebenfalls gut. Wir kommen in Kapitel 32 auf das vollständige Diagnosebild von Musterwerk zurück. |
Plan Regression via DMV finden
-- Plan Regression suchen: Abfragen mit mehreren Plänen,
-- bei denen ein neuerer Plan deutlich schlechter ist als ein älterer
WITH planstatistiken AS (
SELECT
q.query_id,
p.plan_id,
p.last_compile_start_time,
p.is_forced_plan,
-- Durchschnittliche Laufzeit in ms für diesen Plan
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
SUM(rs.count_executions) AS ausfuehrungen,
SUBSTRING(qt.query_sql_text, 1, 150) AS abfragetext
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs
ON rs.plan_id = p.plan_id
GROUP BY q.query_id, p.plan_id, p.last_compile_start_time,
p.is_forced_plan, qt.query_sql_text
)
SELECT
query_id,
-- Wieviele verschiedene Pläne gibt es für diese Abfrage?
COUNT(*) OVER (PARTITION BY query_id) AS anzahl_plaene,
plan_id,
last_compile_start_time,
avg_duration_ms,
ausfuehrungen,
is_forced_plan,
abfragetext
FROM planstatistiken
WHERE query_id IN (
-- Nur Abfragen mit mehr als einem Plan interessieren uns
SELECT query_id
FROM planstatistiken
GROUP BY query_id
HAVING COUNT(*) > 1
)
ORDER BY query_id, last_compile_start_time;
Das Ergebnis zeigt für jede betroffene Abfrage alle Pläne mit ihrer durchschnittlichen Laufzeit. Wenn Plan 2 (neuerer Zeitstempel) dramatisch schlechter ist als Plan 1: Plan Regression. Der nächste Schritt ist Plan Forcing.
Plan Forcing: Den guten Plan einfrieren
Du hast die Plan Regression identifiziert. Der alte Plan mit plan_id = 42 lief in 45 ms, der neue Plan mit plan_id = 87 braucht 4.200 ms. Plan Forcing weist SQL Server an, immer Plan 42 zu verwenden — unabhängig davon, welchen Plan der Optimizer heute auswählen würde. Kein Anwendungscode ändern, kein Neustart, kein Hint. Einfach einen gespeicherten guten Plan erzwingen.
-- Schritt 1: Gute und schlechte plan_id ermitteln
SELECT
p.plan_id,
p.last_compile_start_time,
p.is_forced_plan,
p.force_failure_count,
AVG(rs.avg_duration) / 1000.0 AS avg_dauer_ms,
SUM(rs.count_executions) AS ausfuehrungen
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs
ON rs.plan_id = p.plan_id
WHERE p.query_id = 123 -- query_id aus vorheriger Abfrage
GROUP BY p.plan_id, p.last_compile_start_time, p.is_forced_plan, p.force_failure_count
ORDER BY p.last_compile_start_time;
-- Schritt 2: Guten Plan forcieren
-- SQL Server wird ab jetzt immer plan_id 42 für query_id 123 verwenden
EXEC sp_query_store_force_plan
@query_id = 123,
@plan_id = 42;
-- Schritt 3: Bestätigung — is_forced_plan sollte jetzt 1 sein
SELECT plan_id, is_forced_plan, force_failure_count
FROM sys.query_store_plan
WHERE query_id = 123;
-- Schritt 4: Nach dauerhafter Lösung — Plan Forcing aufheben
-- (wenn Statistiken aktualisiert oder Index erstellt wurde)
EXEC sp_query_store_unforce_plan
@query_id = 123,
@plan_id = 42;
|
Warnung: Plan Forcing ist ein Pflaster, keine Lösung |
|---|
|
Ein forcierter Plan ist eine Sofortmaßnahme — er kauft dir Zeit für die eigentliche Ursachenanalyse. Die Ursache (veraltete Statistiken, fehlender Index, geändertes Datenvolumen) bleibt bestehen und muss behoben werden. Forcierte Pläne können nach Schema-Änderungen oder Upgrades ungültig werden — SQL Server deaktiviert sie dann automatisch (force_failure_count steigt). Überwache forcierte Pläne nach jedem Upgrade. Wenn force_failure_count > 0: Der Plan konnte nicht angewendet werden, SQL Server hat einen anderen Plan verwendet — du weißt möglicherweise nicht welchen. |
Forcierte Pläne überwachen
-- Alle forcierten Pläne und ihren Gesundheitszustand
-- force_failure_count > 0 bedeutet: Plan konnte nicht angewendet werden
SELECT
q.query_id,
p.plan_id,
p.is_forced_plan,
p.force_failure_count,
-- Warum ist der forcierte Plan fehlgeschlagen?
p.last_force_failure_reason_desc,
SUBSTRING(qt.query_sql_text, 1, 100) AS abfragetext
FROM sys.query_store_plan p
JOIN sys.query_store_query q
ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
WHERE p.is_forced_plan = 1
ORDER BY p.force_failure_count DESC;
Automatic Plan Correction: SQL Server korrigiert sich selbst (SQL 2017+)
Wer manuelles Plan Forcing verstanden hat und auf einer stabilen Workload sitzt, kann einen Schritt weitergehen: Automatic Plan Correction. SQL Server 2017 erweiterte den Query Store um die Fähigkeit, Plan Regressionen selbständig zu erkennen und zu beheben — ohne, dass du eingreifen musst.
Das Prinzip: Nach jedem Plan-Wechsel beobachtet SQL Server die Laufzeit der neuen Version für eine statistisch relevante Anzahl von Ausführungen. Ist die neue Duration signifikant schlechter als die letzte bekannte gute Duration (Schwellenwert: ca. 10% über mehrere Ausführungen), forciert SQL Server automatisch den letzten guten Plan. Die Entscheidung und die Begründung werden in sys.dm_db_tuning_recommendations dokumentiert.
-- Automatic Plan Correction aktivieren (SQL Server 2017+)
-- Empfehlung: Erst mit manuell Plan Forcing vertraut sein, dann automatisieren
ALTER DATABASE MeineProduktionsdatenbank
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
-- Status abfragen: desired_state_desc und actual_state_desc sollten übereinstimmen
SELECT
name,
desired_state_desc,
actual_state_desc,
reason_desc -- warum weicht actual von desired ab?
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN';
-- Was hat Automatic Tuning bisher automatisch getan?
-- score: 0-100, höher = stärkere Empfehlung
SELECT
r.reason,
r.score,
r.state_transition_reason,
-- Details als JSON: enthält query_id, plan_id_forcing, plan_id_regressed
r.details,
r.recommended_action_name,
r.execute_action_start_time
FROM sys.dm_db_tuning_recommendations r
ORDER BY r.execute_action_start_time DESC;
|
Hintergrund: Wann Automatic Plan Correction einschalten — wann nicht |
|---|
|
Geeignet: Stabile Produktionsdatenbanken mit einer überschaubaren, gut definierten Workload, bei der Plan Regressionen gelegentlich auftreten. Der automatische Eingriff ist konservativ — er wartet auf statistisch signifikante Verschlechterung. |
|
Weniger geeignet: Volatile Workloads mit stark schwankenden Datenmengen, bei denen der "letzte gute Plan" für die aktuelle Datenmenge tatsächlich falsch sein könnte. Oder Umgebungen mit vielen Ad-hoc-Abfragen und ständig wechselnden Parametern. |
|
Immer überwachen: sys.dm_db_tuning_recommendations nach Deployments und Upgrades prüfen — was hat Automatic Tuning automatisch geändert? |
Query Store als Analyse-Werkzeug: Über Plan Forcing hinaus
Query Store ist kein reines Plan-Forcing-Werkzeug. Er ist primär ein Diagnosewerkzeug — Plan Forcing ist nur eine Konsequenz davon. Die folgenden Abfragen decken die wichtigsten Analyse-Szenarien ab, die über die Plan-Regression-Diagnose hinausgehen.
Queries mit Plan-Änderungen in den letzten 7 Tagen
-- Abfragen die in den letzten 7 Tagen einen neuen Ausführungsplan erhalten haben
-- Das sind die Kandidaten für Performance-Verschiebungen
SELECT
q.query_id,
p.plan_id,
p.last_compile_start_time AS plan_erstellt,
-- Wie war die Performance kurz nach der Plan-Erstellung?
AVG(rs.avg_duration) / 1000.0 AS avg_dauer_ms,
SUM(rs.count_executions) AS ausfuehrungen_seitdem,
p.is_forced_plan,
SUBSTRING(qt.query_sql_text, 1, 150) AS abfragetext
FROM sys.query_store_plan p
JOIN sys.query_store_query q
ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
LEFT JOIN sys.query_store_runtime_stats rs
ON rs.plan_id = p.plan_id
WHERE p.last_compile_start_time >= DATEADD(DAY, -7, GETUTCDATE())
GROUP BY q.query_id, p.plan_id, p.last_compile_start_time,
p.is_forced_plan, qt.query_sql_text
ORDER BY p.last_compile_start_time DESC;
Parameter-Sniffing-Kandidaten: Abfragen mit großem Laufzeit-Spread
Parameter Sniffing — ausführlich in Kapitel 18 beschrieben — zeigt sich im Query Store durch Abfragen, die mit einem einzigen Plan sehr unterschiedliche Laufzeiten haben. Oder durch Abfragen, die mehrere Pläne haben, weil verschiedene Parameter verschiedene Pläne ausgelöst haben.
-- Sniffing-Kandidaten: Abfragen mit einem Plan aber stark schwankender Laufzeit
-- Hoher max/avg Quotient = manche Ausführungen viel schlechter als im Schnitt
SELECT
q.query_id,
p.plan_id,
SUM(rs.count_executions) AS ausfuehrungen,
AVG(rs.avg_duration) / 1000.0 AS avg_dauer_ms,
MAX(rs.max_duration) / 1000.0 AS max_dauer_ms,
MIN(rs.min_duration) / 1000.0 AS min_dauer_ms,
-- Verhältnis Max zu Avg: > 10 ist auffällig, > 100 ist ein Problem
MAX(rs.max_duration)
/ NULLIF(AVG(rs.avg_duration), 0) AS max_avg_quotient,
SUBSTRING(qt.query_sql_text, 1, 150) AS abfragetext
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs
ON rs.plan_id = p.plan_id
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
HAVING AVG(rs.avg_duration) > 10000 -- nur Abfragen > 10 ms im Schnitt
AND MAX(rs.max_duration) / NULLIF(AVG(rs.avg_duration), 0) > 10
ORDER BY max_avg_quotient DESC;
Performance-Vergleich vor und nach einem Deployment
-- Vorher/Nachher-Vergleich: Wie hat sich die Performance durch ein Deployment verändert?
-- Vorher-Zeitraum und Nachher-Zeitraum als Variablen definieren
DECLARE @deployment_zeitpunkt DATETIME2 = '2024-03-15 22:00:00';
DECLARE @vergleichsfenster_stunden INT = 24; -- 24h vor und nach dem Deployment
SELECT
q.query_id,
-- Durchschnittliche Duration VOR dem Deployment
AVG(CASE WHEN ri.start_time < @deployment_zeitpunkt
THEN rs.avg_duration END) / 1000.0 AS avg_ms_vorher,
-- Durchschnittliche Duration NACH dem Deployment
AVG(CASE WHEN ri.start_time >= @deployment_zeitpunkt
THEN rs.avg_duration END) / 1000.0 AS avg_ms_nachher,
-- Veränderungsfaktor: > 2.0 = doppelt so langsam = prüfenswert
AVG(CASE WHEN ri.start_time >= @deployment_zeitpunkt
THEN rs.avg_duration END)
/ NULLIF(AVG(CASE WHEN ri.start_time < @deployment_zeitpunkt
THEN rs.avg_duration END), 0) AS faktor,
SUBSTRING(qt.query_sql_text, 1, 150) AS abfragetext
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval ri
ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE ri.start_time >= DATEADD(HOUR, -@vergleichsfenster_stunden, @deployment_zeitpunkt)
AND ri.start_time < DATEADD(HOUR, +@vergleichsfenster_stunden, @deployment_zeitpunkt)
GROUP BY q.query_id, qt.query_sql_text
-- Nur Abfragen die in beiden Zeiträumen gelaufen sind
HAVING COUNT(CASE WHEN ri.start_time < @deployment_zeitpunkt THEN 1 END) > 0
AND COUNT(CASE WHEN ri.start_time >= @deployment_zeitpunkt THEN 1 END) > 0
ORDER BY faktor DESC;
Adaptive und Intelligent Query Processing: SQL Server denkt mit
SQL Server 2017 und 2019 brachten eine Reihe von Features unter dem Sammelbegriff "Adaptive Query Processing" (SQL 2017) und "Intelligent Query Processing" (SQL 2019), die eng mit dem Query Store verzahnt sind — oder zumindest denselben Kompatibilitätslevel-Voraussetzungen folgen. Alle Features erfordern Kompatibilitätslevel 140 (SQL 2017) oder 150 (SQL 2019).
|
Feature |
Ab Version |
Kompatibilitätslevel |
Was es tut |
|---|---|---|---|
|
Memory Grant Feedback |
SQL 2017 |
140 |
Passt Speicherzuteilung bei Spills an (Kap 12) |
|
Adaptive Joins |
SQL 2017 |
140 |
Wechselt zur Laufzeit zwischen Hash und Nested Loop |
|
Interleaved Execution |
SQL 2017 |
140 |
Bessere Schätzungen für Multi-Statement TVFs |
|
Batch Mode on Row Store |
SQL 2019 |
150 |
Batch-Verarbeitung auch ohne Columnstore Index |
|
Scalar UDF Inlining |
SQL 2019 |
150 |
Skalare Funktionen werden inliniert (Kap 26) |
|
Table Variable Deferred Compilation |
SQL 2019 |
150 |
Tabellenvariablen mit korrekten Kardinalitätsschätzungen |
|
APPROX_COUNT_DISTINCT |
SQL 2019 |
150 |
Approximative Aggregation für Big Data |
Tabelle 19.3: Adaptive und Intelligent Query Processing Features
Das wichtigste Feature für den Alltag ist Memory Grant Feedback. Wie wir in Kapitel 12 gesehen haben, sind falsche Speicherzuweisungen ein häufiger Performance-Killer: Zu wenig Speicher führt zu Spills auf TempDB, zu viel Speicher blockiert andere Abfragen. Memory Grant Feedback beobachtet nach der Ausführung ob der Speicher wirklich gebraucht wurde, und korrigiert die Schätzung für die nächste Ausführung — bis zu einem Gleichgewicht. Das funktioniert über den Query Store: Die korrigierten Schätzungen werden persistent gespeichert und überstehen Neustarts.
Adaptive Joins sind eleganter als sie klingen: Der Optimizer kann zur Kompilierzeit nicht immer wissen ob ein Hash Join oder ein Nested Loop Join besser ist — das hängt von der tatsächlichen Anzahl der Zeilen ab, die erst zur Laufzeit bekannt ist. Mit Adaptive Joins wird die Entscheidung auf den ersten Ausführungszeitpunkt verschoben: SQL Server startet mit einer Vorannahme, misst nach dem ersten Input-Scan die tatsächliche Zeilenzahl, und wechselt dann dynamisch in den besseren Join-Algorithmus. Das ist kein Zaubertrick — es hat Overhead. Aber bei Abfragen mit unzuverlässigen Kardinalitätsschätzungen (ein häufiges Symptom — Kapitel 16) kann es erhebliche Verbesserungen bringen.
Scalar UDF Inlining verdient eine besondere Erwähnung, weil skalare benutzerdefinierte Funktionen in SQL Server historisch katastrophal für die Performance sind: Sie zwingen den Optimizer, für jede Zeile einen separaten Ausführungskontext zu öffnen, und verhindern Parallelismus. SQL 2019 kann qualifizierende UDFs direkt in die aufrufende Query inlinen — als ob du den Funktionskörper selbst geschrieben hättest. Kapitel 26 geht tiefer in dieses Thema. Denke bei Trendforge an die skalaren Funktionen im ORM-generierten Code — das ist genau das Szenario.
|
Hinweis: Kompatibilitätslevel ist die Stellschraube |
|---|
|
Alle IQP-Features erfordern den passenden Kompatibilitätslevel — nicht die SQL Server Version. Eine SQL Server 2019 Instanz mit einer Datenbank auf Kompatibilitätslevel 110 (SQL 2012-Modus) bekommt keines dieser Features. Das ist oft der Grund warum ein Upgrade keine messbare Verbesserung bringt: Die Version stimmt, aber der Kompatibilitätslevel wurde nicht angepasst. Anhang C listet alle sp_configure-Einstellungen; Kompatibilitätslevel findest du über sys.databases. |
Query Store Overhead und Wartung: Was es kostet
Der häufigste Einwand gegen Query Store lautet: "Macht der nicht den Server langsamer?" Die Antwort ist: Ja, minimal. Der CPU-Overhead beträgt in der Praxis typischerweise unter 2% — für den Gegenwert, den du erhältst (historische Performance-Daten, Plan-Regression-Diagnose, Upgrade-Sicherheitsnetz), ist das ein sehr guter Deal. Auf Systemen unter extremem CPU-Druck ist dieser Overhead trotzdem messbar.
Der IO-Overhead ist relevanter: Query Store schreibt Daten in die Datenbankdatei — das ist zusätzliche IO. Der Schreibpuffer (FLUSH_INTERVAL_SECONDS, Standard: 900 Sekunden = 15 Minuten) puffert die Daten erst im RAM und schreibt dann in einem Rutsch. Auf SSD-basierten Systemen ist dieser IO-Overhead vernachlässigbar. Auf langsamen Storage-Systemen — hier schaut Sparfuchs-Leser automatisch schuldig — kann er bemerkbar sein.
Ein besonderer Aspekt: Bei SQL Server Always On Availability Groups werden Query Store Daten mitgespiegelt (ab SQL 2016). Wenn die primäre Instanz ausfällt und ein Failover stattfindet, hat die neue primäre Instanz die gesamte Query Store Historie. Das ist ein erheblicher Vorteil gegenüber dem Plan Cache, der nach einem Failover leer ist.
-- Query Store Speicherverbrauch und Gesundheitsstatus
SELECT
actual_state_desc AS betriebsmodus,
current_storage_size_mb AS belegt_mb,
max_storage_size_mb AS limit_mb,
CAST(current_storage_size_mb AS FLOAT)
/ NULLIF(max_storage_size_mb, 0) * 100 AS auslastung_pct,
flush_interval_seconds, -- wie oft wird in die DB geschrieben?
query_capture_mode_desc,
size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
-- Query Store Puffer explizit in die Datenbank schreiben
-- Nützlich vor einem geplanten Neustart, um Datenverlust zu minimieren
EXEC sp_query_store_flush_db;
-- Eine einzelne Abfrage aus dem Query Store entfernen
-- Nützlich, wenn eine Abfrage den Speicher dominiert
-- ACHTUNG: Entfernt auch alle Pläne und Statistiken für diese Abfrage!
EXEC sp_query_store_remove_query @query_id = 123;
-- Kompletten Query Store leeren (z.B. vor einer Upgrade-Baseline)
-- Alle historischen Daten gehen unwiederbringlich verloren!
-- ALTER DATABASE MeineDB SET QUERY_STORE CLEAR;
Diagnose-Kästen: Query Store im Einsatz
Symptome
|
Hinweis: Symptome — wann der Query Store hilft |
|---|
|
"Die Abfrage war letzte Woche schnell, heute ist sie langsam" — der Klassiker. Query Store zeigt den genauen Zeitpunkt des Plan-Wechsels und beide Pläne im Vergleich. |
|
Nach SQL Server Update oder Kompatibilitätslevel-Wechsel verschlechtern sich Abfragen — Query Store zeigt welche Abfragen betroffen sind und ermöglicht Plan Forcing als Sofortmaßnahme. |
|
"Ich weiß nicht, welche Abfrage am meisten Ressourcen kostet" — Top Resource Consuming Queries beantwortet das sofort, ohne, dass vorab etwas vorbereitet werden musste. |
|
Sporadisch langsame Abfragen ohne erkennbares Muster — Parameter-Sniffing-Diagnose über Queries with Multiple Plans und max/avg Laufzeit-Quotient. |
|
Query Store ist in READ_ONLY Modus gewechselt — actual_state_desc = READ_ONLY bedeutet: keine neuen Daten seit Speicherlimit erreicht wurde. |
So misst du das
-- Vollständiger Query Store Health Check
-- 1. Ist er aktiv und in welchem Zustand?
SELECT
actual_state_desc,
current_storage_size_mb,
max_storage_size_mb,
CAST(current_storage_size_mb AS FLOAT)
/ NULLIF(max_storage_size_mb, 0) * 100 AS auslastung_pct,
query_capture_mode_desc
FROM sys.database_query_store_options;
-- 2. Gibt es forcierte Pläne die fehlschlagen?
SELECT plan_id, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE is_forced_plan = 1
AND force_failure_count > 0;
-- 3. Top-5 CPU-Abfragen der letzten Stunde
SELECT TOP 5
q.query_id,
SUM(rs.count_executions * rs.avg_cpu_time) / 1000.0 AS total_cpu_ms,
COUNT(DISTINCT p.plan_id) AS anzahl_plaene,
SUBSTRING(qt.query_sql_text, 1, 100) AS abfragetext
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval ri
ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE ri.start_time >= DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;
Typische Fehlinterpretationen
|
Warnung: Fehlinterpretationen beim Query Store |
|---|
|
"Query Store verlangsamt den Server" — der Overhead liegt typischerweise unter 2% CPU. Auf SSD-basierten Systemen ist der IO-Overhead vernachlässigbar. Einzige Ausnahme: Extreme CPU-Sättigung, wo jedes Prozent zählt. In dem Fall: QUERY_CAPTURE_MODE = AUTO statt ALL, und MAX_STORAGE_SIZE_MB auf einen sinnvollen Wert setzen. |
|
"Forced Plan ist eine dauerhafte Lösung" — Plan Forcing kauft Zeit. Die eigentliche Ursache (veraltete Statistiken, fehlender Index, geändertes Datenvolumen) bleibt bestehen und muss behoben werden. Ein Plan der heute gut ist, kann nach dem nächsten großen Daten-Import wieder falsch sein. |
|
"Query Store erfasst alles" — mit QUERY_CAPTURE_MODE = AUTO (dem Standard) werden einmalige Ad-hoc-Abfragen nicht erfasst. Das ist korrekt so. Wenn du eine einmalige Abfrage untersuchst, musst du sie im Plan Cache suchen oder Extended Events verwenden (Kapitel 8). |
|
"Automatic Tuning kann ich einschalten und vergessen" — sys.dm_db_tuning_recommendations nach Deployments und Upgrades prüfen. Was hat Automatic Tuning automatisch forciert? Ist die Entscheidung noch korrekt? |
Erste Gegenmaßnahmen
|
Tipp: Erste Gegenmaßnahmen |
|---|
|
1. Query Store aktivieren — auf allen Produktionsdatenbanken, heute. Ohne aktiven Query Store bist du bei Plan Regression blind. |
|
2. MAX_STORAGE_SIZE_MB erhöhen — Standard 100 MB reicht für Produktion nicht. Mindestens 512 MB, besser 1.000–2.000 MB. |
|
3. READ_ONLY prüfen — SELECT actual_state_desc FROM sys.database_query_store_options. Wenn READ_ONLY: Größe erhöhen und mit QUERY_STORE CLEAR bereinigen. |
|
4. Bei akuter Plan Regression: SSMS → Datenbank → Query Store → Regressed Queries Report öffnen. Zeigt sofort welcher Plan-Wechsel das Problem verursacht hat. |
|
5. Plan forcieren: EXEC sp_query_store_force_plan @query_id = X, @plan_id = Y — gibt Zeit für die eigentliche Ursachenanalyse. |
Zusammenfassung
Der Query Store ist das, was du dir als DBA immer gewünscht hast aber nicht wusstest, dass es existiert: Ein persistenter Recorder der alle Ausführungspläne und Laufzeitstatistiken speichert — über Neustarts hinweg, ohne manuelle Vorbereitung, direkt in der Datenbankdatei. Kein anderes Feature hat die Plan Regression-Diagnose so fundamental verändert.
Die wichtigsten Punkte dieses Kapitels:
|
Hinweis: Ausblick auf Kapitel 20 |
|---|
|
Mit Kapitel 20 wechseln wir die Perspektive — von der Administrator- zur Entwickler-Sicht. Teil IV beginnt mit dem Datenbankdesign selbst: Falsche Datentypen, ungünstige Primary Keys, fehlende Normalisierung oder übertriebene Denormalisierung. Das sind Performance-Probleme, die kein Index, kein Plan Forcing und kein Query Store dauerhaft lösen kann. Performance fängt beim Schema an — und das ist eine Botschaft für die Entwickler im Raum. |
Abb. 1: Query Store Architektur
Kapitel 20