Bewährte Methoden zum Verwalten der Abfragespeicher

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

In diesem Artikel wird die Verwaltung der SQL Server-Abfragespeicher und der umgebenden Features beschrieben.

• Weitere Informationen zum Konfigurieren und Verwalten mit dem Abfragespeicher finden Sie unter Überwachen der Leistung mit dem Abfragespeicher.

Hinweis

In SQL Server 2022 (16.x) ist Abfragespeicher jetzt für alle neu erstellten SQL Server-Datenbanken standardmäßig aktiviert, um den Leistungsverlauf zu verbessern, abfrageplanbezogene Probleme zu beheben und neue Abfrageprozessorfunktionen zu aktivieren.

Abfragespeicher Standardwerte in Azure SQL-Datenbank

Dieser Abschnitt beschreibt die optimalen Standardwerte der Konfiguration in Azure SQL-Datenbank, mit denen der zuverlässige Betrieb des Abfragespeichers und der abhängigen Features sichergestellt wird. Die Standardkonfiguration ist für die fortlaufende Datensammlung optimiert. Dies bedeutet, dass möglichst wenig Zeit im Status OFF bzw. READ_ONLY verbracht wird. Weitere Informationen zu allen verfügbaren Abfragespeicheroptionen finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

Konfiguration	Beschreibung	Standard	Kommentar
MAX_STORAGE_SIZE_MB	Gibt das Limit für den Datenspeicherplatz an, den der Abfragespeicher in der Kundendatenbank belegt.	100 vor SQL Server 2019 (15.x) 1000 ab SQL Server 2019 (15.x)	Für neue Datenbanken erzwungen
INTERVAL_LENGTH_MINUTES	Definiert die Größe des Zeitfensters, in dem gesammelte Laufzeitstatistiken für Abfragepläne aggregiert und dauerhaft gespeichert werden. Jeder aktive Abfrageplan verfügt über maximal eine Zeile für einen mit dieser Konfiguration definierten Zeitraum.	60	Für neue Datenbanken erzwungen
STALE_QUERY_THRESHOLD_DAYS	Zeitbasierte Bereinigungsrichtlinie, mit der der Aufbewahrungszeitraum für dauerhaft gespeicherte Laufzeitstatistiken und inaktive Abfragen gesteuert wird.	30	Für neue Datenbanken und Datenbanken mit vorheriger Standardeinstellung erzwungen (367)
SIZE_BASED_CLEANUP_MODE	Gibt an, ob die automatische Datenbereinigung durchgeführt wird, wenn der Datenumfang des Abfragespeichers sich dem Grenzwert nähert.	AUTO	Für alle Datenbanken erzwungen
QUERY_CAPTURE_MODE	Gibt an, ob die Gesamtmenge aller Abfragen oder nur eine Teilmenge der Abfragen nachverfolgt wird.	AUTO	Für alle Datenbanken erzwungen
DATA_FLUSH_INTERVAL_SECONDS	Gibt an, wie lange gesammelte Laufzeitstatistiken maximal im Arbeitsspeicher aufbewahrt werden, bevor eine Leerung auf einen Datenträger erfolgt.	900	Für neue Datenbanken erzwungen

Wichtig

Diese Standardwerte werden automatisch in der letzten Phase der Abfragespeicher Aktivierung in einem Azure SQL-Datenbank angewendet. Nachdem sie aktiviert wurde, ändert Azure SQL-Datenbank keine Konfigurationswerte, die von Kunden festgelegt werden, es sei denn, sie wirken sich negativ auf die primäre Workload oder zuverlässige Vorgänge der Abfragespeicher aus.

Hinweis

In Azure SQL-Datenbank mit einer Einzeldatenbank oder einem Pool für elastische Datenbanken kann der Abfragespeicher nicht deaktiviert werden. Beim Ausführen von ALTER DATABASE [database] SET QUERY_STORE = OFF wird die Warnung 'QUERY_STORE=OFF' is not supported in this version of SQL Server. zurückgegeben.

Wenn Sie weiterhin Ihre benutzerdefinierten Einstellungen nutzen möchten, helfen Ihnen die Informationen unter ALTER DATABASE SET-Optionen (Transact-SQL) weiter, um die Konfiguration wieder in den vorherigen Zustand zu versetzen. Informationen zur Auswahl der optimalen Konfigurationsparameter finden Sie im Artikel Bewährte Methoden für den Abfragespeicher.

Festlegen des optimalen Erfassungsmodus für den Abfragespeicher

Behalten Sie die relevantesten Daten im Abfragespeicher. Die folgende Tabelle beschreibt die typischen Szenarios für jeden Erfassungsmodus für den Abfragespeicher:

Erfassungsmodus für den Abfragespeicher	Szenario
Alle	Analysieren Sie Ihre Arbeitsauslastung sorgfältig im Hinblick auf alle Abfrageformen und deren Ausführungshäufigkeit und andere Statistiken. Identifizieren Sie neue Abfragen in Ihrer Workload. Erkennen Sie, ob Ad-hoc-Abfragen verwendet werden, um Möglichkeiten für Benutzer oder eine automatische Parametrisierung zu identifizieren. Hinweis: Dies ist der Standardaufnahmemodus in SQL Server 2016 (13.x) und SQL Server 2017 (14.x).
Automatisch	Konzentrieren Sie sich auf relevante und verwertbare Abfragen. Zum Beispiel auf jene Abfragen, die regelmäßig ausgeführt werden oder einen erheblichen Ressourcenverbrauch aufweisen. Hinweis: In SQL Server 2019 (15.x) und höheren Versionen ist dies der Standardaufnahmemodus.
Keine	Sie haben bereits den Abfragesatz erfasst, den Sie während der Laufzeit überwachen möchten, und möchten nun Ablenkungen beseitigen, die durch andere Abfragen entstehen können. „Keine“ ist für Testzwecke geeignet sowie für Vergleichsumgebungen. „Keine“ eignet sich auch für Softwareanbieter, die bei Auslieferung die Abfragespeicherkonfiguration so festlegen, dass die Anwendungsauslastung überwacht wird. „Keine“ sollte mit Bedacht verwendet werden, da Sie womöglich die Gelegenheit verpassen, wichtige neue Abfragen nachzuverfolgen und zu optimieren. Vermeiden Sie den Einsatz von „Keine“, es sei denn es ist für ein bestimmtes Szenario erforderlich.
Benutzerdefiniert	SQL Server 2019 (15.x) führte einen benutzerdefinierten Aufnahmemodus unter dem ALTER DATABASE ... SET QUERY_STORE Befehl ein. Obwohl "Auto" standardmäßig und empfohlen wird, können Datenbankadministratoren benutzerdefinierte Erfassungsrichtlinien verwenden, um das Verhalten der Abfragespeicher Erfassung weiter zu optimieren, wenn es weiterhin Bedenken hinsichtlich des Aufwands gibt, Abfragespeicher möglicherweise eingeführt werden. Weitere Informationen und Empfehlungen finden Sie weiter unten in diesem Artikel unter benutzerdefinierte Erfassungsrichtlinien . Weitere Informationen zu dieser Syntax finden Sie unter ALTER DATABASE SET-Optionen.

Hinweis

Cursor, Abfragen in gespeicherten Prozeduren und nativ kompilierte Abfragen werden immer erfasst, wenn der Erfassungsmodus für den Abfragespeicher auf Alle (ALL), Automatisch (AUTO) oder Benutzerdefiniert (CUSTOM) festgelegt ist. Zum Erfassen von nativ kompilierten Abfragen aktivieren Sie die Sammlung von Statistiken pro Abfrage mithilfe von sys.sp_xtp_control_query_exec_stats.

Aufbewahren der relevantesten Daten im Abfragespeicher

Konfigurieren Sie den Abfragespeicher so, dass nur die relevanten Daten enthalten sind. Dann wird er kontinuierlich ausgeführt, was die Problembehandlung erheblich vereinfacht bei minimalen Auswirkungen auf die normale Arbeitsauslastung.

Die folgende Tabelle enthält bewährte Methoden:

Bewährte Vorgehensweise	Einstellung
Begrenzen der Menge von beibehaltenen Verlaufsdaten.	Konfigurieren Sie die zeitbasierte Richtlinie, um die automatische Bereinigung zu aktivieren.
Filtern Sie nicht relevante Abfragen heraus.	Konfigurieren Sie den Erfassungsmodus für den Abfragespeicher als Automatisch.
Löschen Sie weniger relevanten Abfragen, wenn die maximale Größe erreicht ist.	Aktivieren Sie die größenbasierte Cleanuprichtlinie.

Benutzerdefinierte Erfassungsrichtlinien

Wenn der benutzerdefinierte Abfragespeicher Aufnahmemodus aktiviert ist, stehen zusätzliche Abfragespeicher Konfigurationen unter einer neuen Abfragespeicher Aufnahmerichtlinieneinstellung zur Feinabstimmung der Datensammlung auf einem bestimmten Server zur Verfügung.

Die neuen benutzerdefinierten Einstellungen definieren, was während des Zeitschwellenwerts für die interne Erfassungsrichtlinie geschieht. Hierbei handelt es sich um eine Zeitbegrenzung, in der die konfigurierbaren Bedingungen ausgewertet werden, und trifft eine davon zu, ist die Abfrage geeignet, von Abfragespeicher aufgezeichnet zu werden.

Der Abfragespeicher Aufnahmemodus gibt die Abfrageerfassungsrichtlinie für Abfragespeicher an.

• Alle Abfragen werden erfasst. Diese Option ist die Standardeinstellung in SQL Server 2016 (13.x) und SQL Server 2017 (14.x).
• Auto: Seltene Abfragen und Abfragen mit unbedeutender Kompilierungs- und Ausführungsdauer werden ignoriert. Die Schwellenwerte für die Dauer der Ausführungsanzahl, Kompilierung und Laufzeit werden intern bestimmt. Ab SQL Server 2019 (15.x) ist dies die Standardoption.
• Keine: Abfragespeicher beendet die Erfassung neuer Abfragen.
• Benutzerdefiniert: Ermöglicht zusätzliche Kontrolle und die Möglichkeit, die Datensammlungsrichtlinie zu optimieren. Die neuen benutzerdefinierten Einstellungen definieren, was während des Zeitschwellenwerts für die interne Erfassungsrichtlinie geschieht. Hierbei handelt es sich um eine Zeitbegrenzung, in der die konfigurierbaren Bedingungen ausgewertet werden, und trifft eine davon zu, ist die Abfrage geeignet, von Abfragespeicher aufgezeichnet zu werden.

Die Optimierung einer geeigneten benutzerdefinierten Erfassungsrichtlinie für Ihre Umgebung sollte in folgenden Fällen berücksichtigt werden:

• Die Datenbank ist sehr groß.
• Die Datenbank verfügt über eine große Anzahl eindeutiger Ad-hoc-Abfragen.
• Die Datenbank weist spezifische Größen- oder Wachstumsbeschränkungen auf.

SSMS

Verwenden der neuesten Version von SQL Server Management Studio (SSMS)

So zeigen Sie die aktuellen Einstellungen in Management Studio an:

1. Klicken Sie in SQL Server Management Studio Objekt-Explorer mit der rechten Maustaste auf die Datenbank.
2. Wählen Sie Eigenschaften aus.
3. Wählen Sie Abfragespeicher aus. Überprüfen Sie auf der Seite Abfragespeicher, ob der Vorgangsmodus (angefordert) schreibgeschützt ist.
4. Ändern Sie Abfragespeicher Aufnahmemodus in "Benutzerdefiniert".
5. Beachten Sie, dass die vier Aufnahmerichtlinienfelder unter Abfragespeicher Aufnahmerichtlinie jetzt aktiviert und konfigurierbar sind.

T-SQL

Verwenden Sie die ALTER DATABASE .. SET-Syntax zum Festlegen QUERY_CAPTURE_MODE = CUSTOM und Anschließendes Angeben von Optionen für QUERY_CAPTURE_POLICY. Weitere Informationen zu den benutzerdefinierten Aufnahmerichtlinienoptionen finden Sie in den folgenden T-SQL-Beispielen und den ALTER DATABASE SET-Optionen (Transact-SQL ).

Beispiel für benutzerdefinierte Aufnahmerichtlinien

Im folgenden Beispiel wird QUERY_CAPTURE_MODE auf AUTO festgelegt und ein benutzerdefinierter Aufnahmemodus festgelegt. Jeder der folgenden Richtlinien legt die benutzerdefinierten Aufnahmerichtlinien auf seinen Standardwert in SQL Server 2022 (16.x) fest. Erwägen Sie, diese Werte anzupassen, um die Anzahl der erfassten Abfragen zu verringern und daher den Speicherbedarf auf dem Datenträger des Abfragespeicher zu verringern. Es wird empfohlen, diese Werte schrittweise in kleinen Schritten zu ändern.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Die folgende Beispielabfrage ändert eine vorhandene Abfragespeicher, um eine benutzerdefinierte Erfassungsrichtlinie zu verwenden, die die Standardeinstellungen für EXECUTION_COUNT und TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Abfragespeicher maximale Größe

Der standardmäßige maximale Größenwert der Abfragespeicher beträgt 1000 MB, beginnend mit SQL Server 2019 (15,x). In früheren Versionen betrug der Standardwert 100 MB. Das Erhöhen der maximalen Größenbeschränkung der Abfragespeicher ist in einer besetzten Datenbank mit vielen eindeutigen Abfrageplänen angemessen. Das Anpassen der Aufnahmerichtlinie (siehe vorheriger Abschnitt) ist ein wichtigerer Aspekt, um die Größe des Abfragespeicher auf dem Datenträger zu beschränken und zu verhindern, dass der Abfragespeicher in den READ_ONLY Modus wechselt. Während der Abfragespeicher Abfragen, Ausführungspläne und Statistiken sammelt, wächst die Datenbank an, bis dieser Grenzwert erreicht ist. In diesem Fall ändert Abfragespeicher automatisch den Betriebsmodus in READ_ONLY und beendet das Sammeln neuer Daten, was bedeutet, dass Ihre Leistungsanalyse nicht mehr korrekt ist.

• In SQL Server und Azure SQL verwaltete Instanz MAX_STORAGE_SIZE_MB wird der Grenzwert nicht streng erzwungen.
• In Azure SQL-Datenbank beträgt der maximal zulässige MAX_STORAGE_SIZE_MB Wert 10.240 MB.

Die Speichergröße wird nur überprüft, wenn der Abfragespeicher Daten auf einen Datenträger schreibt. Dieses Intervall wird durch die Option DATA_FLUSH_INTERVAL_SECONDS oder die Option Datenleerungsintervall im Dialogfeld des Management Studio-Abfragespeichers festgelegt.

• Der Standardwert des Intervalls liegt bei 900 Sekunden (oder 15 Minuten).
• Wenn der Abfragespeicher den Grenzwert zwischen Speichergrößenüberprüfungen MAX_STORAGE_SIZE_MB verletzt hat, wechselt er in den schreibgeschützten Modus.
• Bei Aktivierung von SIZE_BASED_CLEANUP_MODE wird auch der Bereinigungsmechanismus zum Erzwingen der Begrenzung MAX_STORAGE_SIZE_MB ausgelöst.
  • Sobald genügend Speicherplatz gelöscht wurde, wechselt der Abfragespeicher Modus automatisch zurück zum READ_WRITE Modus.

Weitere Informationen finden Sie unter ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Datenlöschintervall (Minuten)

Das Daten geleertintervall definiert die Häufigkeit, bevor gesammelte Laufzeitstatistiken auf dem Datenträger gespeichert werden. In SQL Server Management Studio liegt der Wert in Minuten, in Transact-SQL wird er jedoch in Sekunden ausgedrückt. Der Standardwert ist 15 Minuten (900 Sekunden).

• Das Erhöhen des Datenlöschintervalls kann die gesamte Abfragespeicher Speicher-E/A-Auswirkungen verringern, die Speicher-E/A-Workload wird jedoch eher spielig, mit weniger, aber schwereren Auswirkungen auf die Datenträgerauslastung. Ziehen Sie in Betracht, einen höheren Wert zu verwenden, wenn Ihre Arbeitsauslastung keine große Anzahl verschiedener Abfragen und Pläne generiert oder Sie längere Zeit warten können, bevor Daten vor dem Herunterfahren der Datenbank persistent gespeichert werden.
• Durch das Verringern des Datenlöschintervalls wird die Menge Abfragespeicher Daten verringert, die beim Herunterfahren, Stromausfall oder Failover verloren gehen würden. Sie kann auch die Auswirkungen der Speicher-E/A von Abfragespeicher glätten, indem sie häufiger auf den Datenträger schreiben, aber mit weniger Daten.

Hinweis

Mit dem Ablaufverfolgungsflag 7745 wird verhindert, dass Abfragespeicherdaten bei einem Failover oder Befehl zum Herunterfahren auf den Datenträger geschrieben werden. Weitere Informationen finden Sie unter Verwenden von Abfragespeicher in unternehmenskritischen Servern.

Ändern Abfragespeicher Standardeinstellungen

Konfigurieren Sie Abfragespeicher basierend auf den Anforderungen hinsichtlich der Arbeitsauslastung und der Behandlung von Leistungsproblemen. Die Standardparameter sind für den Einstieg ausreichend, Sie sollten jedoch das Verhalten des Abfragespeichers im Verlauf der Zeit überwachen und die Konfiguration entsprechend anpassen.

Anzeigen Abfragespeicher aktuellen Einstellungen

Zeigen Sie die aktuellen Abfragespeicher Einstellungen in SQL Server Management Studio (SSMS) oder T-SQL an.

SSMS

Verwenden der neuesten Version von SQL Server Management Studio (SSMS)

So zeigen Sie die aktuellen Einstellungen in Management Studio an:

1. Klicken Sie in SQL Server Management Studio Objekt-Explorer mit der rechten Maustaste auf die Datenbank.
2. Wählen Sie Eigenschaften aus.
3. Wählen Sie Abfragespeicher aus.

T-SQL

Führen Sie das folgende T-SQL-Skript aus, um die neuesten Informationen zu Abfragespeicher Größe zu erhalten:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

Im folgenden Skript wird ein neuer Wert für Maximale Größe (MB) festgelegt:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Verwenden Sie SQL Server Management Studio oder Transact-SQL, um verschiedene Werte für das Datenleerungsintervall festzulegen:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Statistikauflistungsintervall: Definiert den Grad der Granularität für die gesammelte Laufzeitstatistik, ausgedrückt in Minuten. Der Standardwert ist 60 Sekunden. Es ist ratsam, einen niedrigeren Wert zu verwenden, wenn Sie eine höhere Granularität benötigen oder weniger Zeit zum Erkennen und Verringern von Problemen haben. Denken Sie daran, dass der Wert die Größe der Abfragespeicherdaten direkt beeinflusst. Verwenden Sie SQL Server Management Studio oder Transact-SQL, um einen anderen Wert für das Statistiksammlungsintervall festzulegen:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Veralteter Abfrageschwellenwert (Tage): Zeitbasierte sauber up-Richtlinie, die den Aufbewahrungszeitraum beibehaltener Laufzeitstatistiken und inaktiver Abfragen steuert, ausgedrückt in Tagen. Standardmäßig ist der Abfragespeicher so konfiguriert, dass Daten 30 Tage lang gespeichert werden. Dies ist möglicherweise für Ihr Szenario unnötig lange.

Vermeiden Sie es, Verlaufsdaten aufzubewahren, die Sie nicht mehr verwenden möchten. Dies reduziert die Wahrscheinlichkeit für Änderungen in den schreibgeschützten Status. Die Größe der Abfragespeicherdaten sowie die Zeit, um Probleme zu erkennen und zu mindern, lassen sich besser vorhersagen. Verwenden Sie Management Studio oder das folgende Skript, um zeitbasierte sauber up-Richtlinie zu konfigurieren:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Größenbasierter Bereinigungsmodus: Gibt an, ob automatische Daten sauber up erfolgen, wenn Abfragespeicher Datengröße dem Grenzwert nähert. Aktivieren Sie die größenbasierte Bereinigung, um sicherzustellen, dass der Abfragespeicher immer im Lese-/ Schreibmodus ausgeführt wird und die neuesten Daten erfasst. Es gibt keine Garantie für schwere Workloads, die Abfragespeicher sauber up konsistent Standard die Datengröße unter dem Grenzwert enthalten. Es ist möglich, dass die automatische Datenbereinigung in Rückstand gerät und (vorübergehend) in den schreibgeschützten Modus wechselt.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Abfragespeicher Aufnahmemodus: Gibt die Abfrageerfassungsrichtlinie für Abfragespeicher an.

• Alle Abfragen werden erfasst. Diese Option ist die Standardeinstellung in SQL Server 2016 (13.x) und SQL Server 2017 (14.x).
• Auto: Seltene Abfragen und Abfragen mit unbedeutender Kompilierungs- und Ausführungsdauer werden ignoriert. Die Schwellenwerte für die Dauer der Ausführungsanzahl, Kompilierung und Laufzeit werden intern bestimmt. Ab SQL Server 2019 (15.x) ist dies die Standardoption.
• Keine: Abfragespeicher beendet die Erfassung neuer Abfragen.
• Benutzerdefiniert: Ermöglicht zusätzliche Kontrolle und die Möglichkeit, die Datensammlungsrichtlinie zu optimieren. Die neuen benutzerdefinierten Einstellungen definieren, was während des Zeitschwellenwerts für die interne Erfassungsrichtlinie geschieht. Hierbei handelt es sich um eine Zeitbegrenzung, in der die konfigurierbaren Bedingungen ausgewertet werden, und trifft eine davon zu, ist die Abfrage geeignet, von Abfragespeicher aufgezeichnet zu werden.

Wichtig

Cursor, Abfragen in gespeicherten Prozeduren und nativ kompilierte Abfragen werden immer erfasst, wenn der Erfassungsmodus für den Abfragespeicher auf Alle (ALL), Automatisch (AUTO) oder Benutzerdefiniert (CUSTOM) festgelegt ist. Zum Erfassen von nativ kompilierten Abfragen aktivieren Sie die Sammlung von Statistiken pro Abfrage mithilfe von sys.sp_xtp_control_query_exec_stats.

Das folgende Skript legt QUERY_CAPTURE_MODE auf AUTO fest:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Beispiele

Im folgenden Beispiel wird QUERY_CAPTURE_MODE auf AUTO festgelegt und andere empfohlene Optionen in SQL Server 2016 (13.x) festgelegt:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

Im folgenden Beispiel wird QUERY_CAPTURE_MODE auf AUTO festgelegt und andere empfohlene Optionen in SQL Server 2017 (14.x) festgelegt, um Wartezeitstatistiken einzuschließen:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

Im folgenden Beispiel wird die BENUTZERDEFINIERTE Aufnahmerichtlinie auf die Standardeinstellungen für SQL Server 2019 (15.x) anstelle des neuen Automatischen Aufnahmemodus festgelegt. Weitere Informationen zu benutzerdefinierten Aufnahmerichtlinienoptionen und -standardwerten finden Sie unter <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Abfragespeicher Standard tenance

Dieser Abschnitt enthält einige Richtlinien zum Verwalten des Abfragespeichers selbst.

Abfragespeicherzustand

Der Abfragespeicher speichert seine Daten in der Benutzerdatenbank und besitzt aus diesem Grund eine Größenbegrenzung (konfiguriert mit MAX_STORAGE_SIZE_MB). Wenn die Daten im Abfragespeicher dieses Limit erreichen, ändert sich der Status automatisch vom Lese-/ Schreibzugriff in den schreibgeschützten Modus, und es werden keine neuen Daten mehr erfasst.

Fragen Sie sys.database_query_store_options ab, um zu ermitteln, ob der Abfragespeicher zurzeit aktiv ist und Laufzeitstatistiken erfasst.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Der Status des Abfragespeichers wird durch die Spalte actual_state bestimmt. Wenn er vom gewünschten Status abweicht, können Sie über die readonly_reason-Spalte weitere Informationen erhalten. Wenn die Größe des Abfragespeichers das Kontingent überschreitet, wechselt das Feature in den schreibgeschützten Modus („read_only“) und gibt einen Grund an. Informationen zu Gründen finden Sie unter sys.database_query_store_options.

Abrufen von Optionen zum Abfragespeicher

Führen Sie zum Abrufen detaillierter Informationen zum Status des Abfragespeichers Folgendes in einer Benutzerdatenbank aus.

SELECT * FROM sys.database_query_store_options;

Festlegen des Abfragespeicherintervalls

Sie können das Intervall zum Sammeln von Statistiken zur Abfragelaufzeit überschreiben (der Standardwert ist 60 Minuten). Der neue Wert für das Intervall wird in der Sicht sys.database_query_store_options angezeigt.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Ganzzahlige Werte sind für den Typ INTERVAL_LENGTH_MINUTES nicht zulässig. Verwenden Sie eine der folgenden Intervalle: 1, 5, 10, 15, 30, 60 oder 1440 Minuten.

Hinweis

Für Azure Synapse Analytics wird das Anpassen der Konfigurationsoptionen für den Abfragespeicher, wie in diesem Abschnitt veranschaulicht, nicht unterstützt.

Die Speicherplatzverwendung des Abfragespeichers

So überprüfen Sie die aktuelle Größe des Abfragespeichers und begrenzen die folgende Anweisung in der Benutzerdatenbank.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Mit der folgenden Anweisung können Sie den Speicher erweitern, wenn der Abfragespeicher voll ist.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Festlegen von Optionen zum Abfragespeicher

Sie können mehrere Optionen zum Abfragespeicher gleichzeitig mit einer einzigen ALTER DATABASE-Anweisung festlegen.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Eine vollständige Liste der Konfigurationsoptionen finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

Bereinigen des Speichers

Die internen Tabellen des Abfragespeichers werden beim Erstellen der Datenbank in der PRIMÄREN Dateigruppe erstellt. Diese Konfiguration kann später nicht mehr geändert werden. Wenn der Speicherplatz nicht mehr verfügbar ist, sollten Sie ältere Abfragespeicher Daten mithilfe der folgenden Anweisung löschen.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Alternativ können Sie nur Ad-hoc-Abfragedaten löschen, da sie für Abfrageoptimierungen und Die Plananalyse weniger relevant ist, aber genauso viel Platz beansprucht.

In Azure Synapse Analytics ist das Löschen des Abfragespeichers nicht verfügbar. Daten werden automatisch für die letzten sieben Tage aufbewahrt.

Ad-hoc-Abfragen löschen

Dadurch werden Ad-hoc- und interne Abfragen aus dem Abfragespeicher gelöscht, sodass der Abfragespeicher nicht mehr platzt und Abfragen entfernt, die wir wirklich nachverfolgen müssen.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Sie können eine eigene Prozedur mit abweichender Logik für das Bereinigen von Daten definieren, die Sie nicht mehr benötigen.

Im vorherigen Beispiel wird die sp_query_store_remove_query erweiterte gespeicherte Prozedur verwendet, um unnötige Daten zu entfernen. Weitere Funktionen:

• Verwenden Sie sp_query_store_reset_exec_stats, um die Laufzeitstatistiken für einen angegebenen Plan zu löschen.
• Verwenden Sie sp_query_store_remove_plan, um einen einzelnen Plan zu entfernen.

Zugehöriger Inhalt

• ALTER DATABASE SET-Optionen (Transact-SQL)
• Katalogsichten des Abfragespeichers (Transact-SQL)
• Gespeicherte Prozeduren für den Abfragespeicher (Transact-SQL)
• Verwenden des Abfragespeicher mit IN-Memory OLTP
• Leitfaden zur Architektur der Abfrageverarbeitung
• Abfragespeicherhinweise
• Überwachen der Leistung mithilfe des Abfragespeichers
• Optimieren der Leistung mit dem Abfragespeicher
• Speicher und Analyse von historischen Abfragen in Azure Synapse Analytics