SQL Server New Features 2022 - Query Store

Mit der Query Store Funktion erhalten Sie Einblick in die Abfrageplanauswahl und die Leistung von SQL Server, Azure SQL Database, Azure SQL Managed Instance und Azure Synapse Analytics. Query Store vereinfacht das Troubleshooting. Es hilft dabei Leistungsunterschiede schnell zu finden, die durch Abfrageplanänderungen verursacht werden. Query Store erfasst automatisch die Historie von Abfragen, Plänen und Laufzeitstatistiken und speichert sie für Ihre Überprüfung. Die Daten werden in Zeitfenster unterteilt, sodass Sie Datenbanknutzungsmuster sehen und verstehen können. Außerdem ist es möglich Abfrageplanänderungen in der Vergangenheit besser nachzuvollziehen.

Der Query Store wurde in SQL Server 2016 eingeführt, um Nutzungsdaten für SQL Server-Objekte zu sammeln und T-SQL-Leistungsengpässe zu identifizieren. SQL Server 2022 findet immer wieder neue Möglichkeiten den Query Store zu nutzen. Dadurch, dass der Query Store und dessen Funktionen stetig weiterentwickelt werden, ist es nun standardmäßig in SQL Server 2022 aktiviert. Außerdem ist es möglich den Query Store für Read Replicas in Availability Groups zu verwenden.

Allgemeine Informationen

Query Store ermöglicht es Ausführungspläne, CPU oder Speicherverbrauch zu überwachen oder optimierte Abfragepläne zu erzwingen. Gegenüber dem “Plan Cache” werden diese Daten persistent auf der Festplatte gespeichert. Damit bleiben erfasste Daten auch nach Neustart verfügbar.

• In den folgenden Szenarien beweist sich der Query Store als nützlich:
• Erfassen der Queries, die überproportional viel Ressourcen verbrauchen
• Abfragehistorie abrufen
• Information über Abfrage-Regression: d.h. der von der Query-Engine erstellte Ausführungsplan ist weniger performant, als der vorige. Hier hilft der Query Store dies möglichst schnell zu erkennen und die effizienteren Pläne auszuführen.
• Ermitteln, wie oft eine Abfrage in einem bestimmten Zeitraum ausgeführt wurde

Schnelle Fakten

• Der SQL Server Query Store ist eine Funktion auf Datenbankebene, d.h. er kann in jeder SQL-Datenbank separat mit SQL Server Management Studio oder T-SQL aktiviert werden. Es handelt sich nicht um eine Einstellung auf Instanzebene.
• Der SQL Server Query Store ermöglicht eine schnelle Analyse der Abfrageleistung mit Hilfe von integrierten Berichten und DMWs
• Die Funktion Query Store ist in allen SQL Server-Editionen verfügbar
• Query Store ist bei Azure Datenbanken automatisch aktiviert
• VIEW DATABASE STATE ist die benötigte Berechtigung um mit SQL Query Store zu arbeiten.

Für das Erzwingen von Plänen & Dataflush Intervallen ist die db_owner Rolle notwendig.

Einrichtung

Um den SQL Server Query Store für eine On-Premise SQL Server Datenbank zu aktivieren, klicken Sie mit der rechten Maustaste auf eine Datenbank im SSMS Object Explorer und wählen im Kontextmenü die Option Eigenschaften:

Wählen Sie Query Store unter den Einstellungen der Datenbank aus.

Wählen Sie in der Dropdown-Liste Operation Mode (Betriebsart) die Option Read-Write (Lesen-Schreiben):

Sobald die Option Lesen-Schreiben ausgewählt ist, werden die anderen Felder unter dem Dropdown-Feld Betriebsmodus (angefordert) mit Standardwerten vor konfiguriert. Bestätigen Sie nun mit OK die Einstellungen - somit ist Query Store aktiviert.

Um zu bestätigen, dass der SQL Server-Query Store für die gewählte Datenbank aktiviert ist, gehen Sie zum Objekt-Explorer und aktualisieren Sie mit Rechtsklick (Refresh) die Datenbank. Query Store wurde erfolgreich eingerichtet, wenn Ihnen die folgenden Ordner angezeigt werden.

Um den SQL Server Query Store mit Hilfe von T-SQL zu aktivieren, führen Sie die folgende Anweisung in einem Abfragefenster aus:

Beachten Sie, dass SQL Server Query Store nicht für die Master- oder tempdb-Datenbank aktiviert werden kann.

Optionen

Die erste Option der Query Store Einstellungen ist der Betriebsmodus.

Diese Option ist deaktiviert und kann auch nicht aktiviert werden. Diese Option zeigt den Status des SQL Server Query Store an. Es gibt drei Modi für den SQL Server-Query Store: Aus, Nur Lesen und Lesen/Schreiben.

• Aus - Der SQL Server-Query-Store ist ausgeschaltet.
• Read Only - Neue Abfragestatistiken aus ausgeführte Pläne werden nicht verfolgt.
• Read Write - Ermöglicht die Erfassung von Abfrageausführungsplänen und Abfragelaufzeitstatistiken

Im Betriebsmodus können in der Dropdown-Box die gleichen Optionen eingestellt werden, wie bei der vorherigen Option. Die hier eingestellten Werte haben direkte Auswirkungen auf den Zustand des Abfragespeichers.

In der Option Data Flush Interval (Minutes) kann ein Intervall in Minuten festgelegt werden. Es gibt an, wie häufig die Abfrage-Laufzeitstatistiken und Abfrage-Ausführungspläne aus dem Speicher der SQL Server-Instanz auf die Festplatte geschrieben werden. Standardmäßig ist diese Option auf 15 Minuten eingestellt:

Wenn diese Option auf einen niedrigeren Wert, als die Häufigkeit der Flushes eingestellt ist, werden die Daten häufig vom Speicher auf die Festplatte übertragen. Solch eine Einstellung kann sich also negativ auf die Performance der SQL Server-Instanz auswirken. Wenn der Wert jedoch erhöht wird, werden mehr SQL Server Query Store-Informationen im Speicher der SQL Server-Instanz abgelegt, bevor sie auf der Festplatte geleert werden. Das erhöht das Risiko, wenn diese Daten im Falle eines Neustarts/Absturzes des SQL Servers verloren gehen.

Die Option Statistics Collection Interval definiert das Aggregationsintervall der Abfragelaufzeitstatistiken, die im SQL Server Query Store verwendet werden sollen. Standardmäßig ist es auf 60 Minuten eingestellt. Ein niedrigerer Wert bedeutet, dass die Granularität der Abfrage-Laufzeit-Statistiken feiner ist, wodurch mehr Intervalle auftreten. Dies verlangt jedoch mehr Speicher für die Abfragestatistiken.

Die maximale Größe des Query Stores kann mit der Einstellung Max Size konfiguriert werden. Standardmäßig ist die Größe des Query Stores auf 100 MB eingestellt. Die Daten im SQL Server Query Store werden in der Datenbank gespeichert, in der der SQL Server Query Store aktiviert ist. Der SQL Server-Query-Store wächst nicht automatisch. Sobald der Query Store die maximale Größe überschreiten möchte, wird der Betriebsmodus automatisch von dem Read-Write Modus in den Read only gewechselt. Also werden nach Erreichen der maximalen Größe keine weiteren Daten zu Ablaufstatistiken und Ausführungsplänen gesammelt:

Die Option Capture Mode des Abfragespeichers bestimmt, welche Art von Abfrage im Query Store erfasst wird. Standardmäßig ist die Option auf All eingestellt. Das bedeutet, dass jede ausgeführte Abfrage im SQL Server-Query Store, der auf der Datenbank läuft, gespeichert wird. Wenn die Option Capture Mode des Abfragespeichers auf Auto eingestellt ist, versucht der SQL Server-Query Store die zu erfassenden Abfragen nach Priorität zu sortieren und selten ausgeführte und andere Ad-hoc-Abfragen zu ignorieren. Außerdem gibt es einen dritten Wert im Dropdown-Feld: None. Wenn der Wert None gewählt wird, dann sammelt der SQL Server Query Store keine Informationen für neue Abfragen, sondern weiterhin nur Informationen über die Abfragen, die zuvor aufgezeichnet wurden:

Die Option Size Based Cleanup Mode dient zum Bereinigen der Daten im SQL Server-Query Store, wenn die maximale Größe (MB) 90 % der Kapazität erreicht hat. Bei dem Bereinigungsprozess werden die ältesten und weniger teuren Abfragedaten entfernt. Der Bereinigungsprozess wird gestoppt, wenn 80 % der maximalen Größe erreicht sind. Standardmäßig ist diese Option auf Auto eingestellt. Wenn im Dropdown-Feld Size Based Cleanup Mode der Wert Aus eingestellt ist, wird der Bereinigungsprozess nicht durchgeführt, wenn die Größe des SQL Server-Query-Stores 90 % der maximalen Größe erreicht. Wie eben bereits erwähnt, wechselt der Query Store bei dem Erreichen der Maximalen Größe in den Read Only Modus.

Mit der Option Stale Query Threshold für veraltete Abfragen, können Sie festlegen, wie lange die Daten im SQL Server-Abfragespeicher verbleiben sollen. Standardmäßig ist sie auf 30 Tage eingestellt.

Die letzte Option auf der Registerkarte ist eine Option, mit der alle Daten im SQL Server-Query Store durch das Drücken der Schaltfläche Purge Query Data (Abfragedaten bereinigen) gelöscht/bereinigt werden können:

Architektur und Sammlung

Der SQL Server Query Store sammelt den Abfragetext, den Abfrageplan und die Laufzeitstatistiken und legt diese im Speicher ab.

Er extrahiert jede Abfrageanweisung aus der Abfrage. Wenn zum Beispiel in der gespeicherten Prozedur mehrere Abfrageanweisungen vorhanden sind, speichert der SQL Server Query Store jede Abfrageanweisung separat und erhält die Abfragelaufzeitstatistiken von jeder Abfrageanweisung.

Der eindeutige Abfrageplan und der Abfragetext - falls neu - werden direkt an den ASYNC Writer übergeben, der sie auf die Festplatte schreibt.

Die Abfragelaufzeitstatistiken werden nicht direkt in den ASYNC Writer geschrieben. Die Abfragelaufzeitstatistiken werden in einem bestimmten Zeitintervall geschrieben, das in der Option Data Flush Interval (Minutes) festgelegt ist.

Der Query Store sammelt alle Informationen unabhängig davon, ob die Informationen auf der Festplatte oder im Speicher gespeichert sind und zeigt sie in integrierten Berichten an.

Berichte

Wie bereits in diesem Artikel erwähnt, beginnt der SQL Server Query Store nach der Aktivierung mit dem Sammeln von Abfrage-Laufzeitstatistiken und Abfrage-Ausführungsplänen. Integrierte Berichte verwenden die gesammelten Daten, analysieren sie und zeigen sie in einem Gitter- oder Diagrammformat an. Je nachdem, was in den Berichten eingestellt ist.

Derzeit gibt es sechs integrierte Berichte:

• Regressed Queries ist ein integrierter Bericht, der alle Abfragen anzeigt, deren Ausführungsmatrizen in einem bestimmten Zeitraum (letzte Stunde, Tag, Woche) beeinträchtigt sind.
• Der integrierte Bericht Overall Resource Consumption zeigt eine Zusammenfassung des Ressourcenverbrauchs während eines bestimmten Zeitraums. Standardmäßig werden die Ergebnisse für den letzten Monat angezeigt und in vier Diagrammen dargestellt: Dauer, CPU-Zeit, Lesevorgänge und Ausführungsanzahl.
• Der integrierte Bericht über die ressourcenintensivsten Abfragen zeigt standardmäßig die 25 Abfragen für eine bestimmte Datenbank an, die in einem bestimmten Zeitraum die meisten Ressourcen wie CPU-Zeit, Speicherverbrauch, physische Lesevorgänge usw. verbrauchen.
• Mit dem integrierten Bericht Tracked Queries können Laufzeitstatistiken und Ausführungspläne für eine bestimmte Abfrage über einen bestimmten Zeitraum verfolgt werden. Geben Sie in das Textfeld Verfolgte Abfrage eine Abfrage-ID ein (z. B. 205) und drücken Sie die grüne Schaltfläche Play neben dem Feld Verfolgte Abfrage.
• Der integrierte Bericht Queries With Forced Plans zeigt alle erzwungenen Ausführungspläne für bestimmte Abfragen an.
• Um SQL Server zu zwingen, einen bestimmten Ausführungsplan für eine bestimmte Abfrage zu verwenden, wählen Sie in den integrierten Berichten Regressed Queries, Top Resource Consuming Queries, Queries With High Variation oder Tracked Queries zunächst die Ausführungsplan-ID aus und klicken Sie auf die Schaltfläche Force Plan.
• Der integrierte Bericht Queries with high Variation analysiert die Abfragen und zeigt die Abfragen mit den häufigsten Parameterisierungsproblemen an.

Whats new?

SSMS v18.4 fügte eine Reihe von GUI-Optionen auf der Seite Query Store hinzu, die es Datenbankadministratoren (DBAs) erleichtern, Erfassungsrichtlinien festzulegen und Änderungen ohne Code vorzunehmen. Diese Optionen können mit T-SQL für SSMS v18.3 und niedriger konfiguriert werden. Mit dieser Erweiterung können wir sie mit ein paar Klicks auf der Registerkarte Abfragespeicher konfigurieren.

So können Sie die folgenden Funktionen auf der Seite mit den Datenbankeigenschaften des Abfragespeichers selbst mit SSMS 18.4 ändern und anpassen.

• Werte für maximale Pläne pro Abfrage hinzugefügt (MAX_PLANS_PER_QUERY)
• Wert für Erfassungsmodus für Wartestatistiken hinzugefügt (WAIT_STATS_CAPTURE_MODE)
• Benutzerdefinierte Richtlinien (STALE_CAPTURE_POLICY_THRESHOLD, EXECUTION_COUNT, TOTAL_COMPILE_CPU_TIME_MS, TOTAL_EXECUTION_CPU_TIME_MS) zum Capture-Modus hinzugefügt.

Einstellung der maximalen Pläne pro Abfrage und des Erfassungsmodus für die Wartestatistik

Die Eigenschaft Max Plans Per Query stellt die maximale Anzahl von Ausführungsplänen dar, die vom Abfragespeicher pro Abfrage gespeichert werden können. Der Standardwert für maximale Pläne pro Abfrage ist auf 200 Ausführungspläne pro Abfrage festgelegt. Natürlich können Sie diese Zahl je nach Komplexität und Stabilität Ihrer Abfragen erhöhen oder verringern. Falls eine große Anzahl verschiedener Pläne generiert wird, sollten Sie die Anzahl im Auge behalten, da sie Speicherplatz beansprucht.

Mit der Option Wait Statistics Capture Mode können Sie festlegen, ob der Abfragespeicher Wartestatistikinformationen erfassen soll. Standardmäßig ist diese Eigenschaft auf ON gesetzt. Wenn diese Option aktiviert ist, können Sie Wartestatistikdaten erfassen, um Fehler bei der Leistung zu beheben und herauszufinden, welche Abfrage lange dauert und wartet.

Legen Sie die Werte für die Optionen Max Plans Per Query und Wait Statistics Capture Mode auf der Registerkarte Query Store fest oder ändern Sie sie, wie im folgenden Screenshot gezeigt.

Dies sind auch die neu hinzugefügten GUI-Optionen für den Abfragespeicher.

Abgesehen von der Konfiguration dieser Optionen über die grafische Benutzeroberfläche, können Sie diese Optionen auch über T-SQL in SSMS 18.3 und darunter aktivieren/ändern.

Abfrage Store Capture Modus

Mit dem Store Capture Modus können Sie verschiedene Einstellungen für die Abfrageerfassungsrichtlinie für die Datenbank festlegen, in der Sie Ihre Abfragen ausführen. Sie können festlegen, ob alle Abfragen erfasst werden sollen, oder ob nur aktuelle Abfragen oder bestimmte Abfragen in Abhängigkeit von der Ausführungszahl und dem CPU-Verbrauch erfasst werden.

Dies sind die verfügbaren Optionen:

• ALL - Der Standardwert ist ALL in SQL Server 2016 und SQL Server 2017. Das bedeutet, dass alle Abfragen, die gegen die Datenbank ausgeführt werden, im Abfragespeicher erfasst werden.
• Auto - Ab SQL Server 2019 ist dies die Standardoption, die seltene Abfragen mit unbedeutender Kompilierungs- und Ausführungsdauer ausschließt.
• None - Query Store erfasst keine neuen Abfragen, es werden keine Ausführungspläne und Statistiken aufgezeichnet
• Custom - Dies ist eine wichtige Option, die zusätzliche Kontrolle für benutzerdefinierte Erfassungsrichtlinien bietet.

Wenn Sie in der obigen Dropdown-Liste Benutzerdefiniert auswählen, können Sie den Abschnitt Erfassungsrichtlinie für Abfragespeicher bearbeiten und diese Werte anpassen.

Schlussfolgerung

SQL Server Query Store mit SSMS 18.4 zusammen mit SQL Server 2019 ist ein großartiges Tool zur Fehlerbehebung bei der Abfrageperformance. Es ist eine leistungsstarke Funktion für Benutzer, die die Ausführung von Abfragen zur Laufzeit und Abfrageausführungspläne verfolgt. Es überwacht die Leistung von Queries, analysiert und die Ergebnisse in integrierten Berichten übersichtlich bereitstellt.

Gerne stehen wir Ihnen als kompetente Partner und Spezialisten zum Thema SQL Server professionell zur Seite. Wenn Sie mehr über die neuen Features im SQL Server 2022 Release erfahren möchten, vereinbaren Sie einfach ein unverbindliches Beratungsgespräch über unser Kontaktformular.