Bewährte Methoden zum Verwalten der Abfragespeicher

Gilt für: SQL Server Azure SQL-Datenbank Azure 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.

Abfragespeicherfehler 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 Standardeinstellungen werden in der letzten Phase der Abfragespeicheraktivierung automatisch in einer Azure SQL Database-Instanz angewendet. Nach der Aktivierung werden die vom Kunden festgelegten Konfigurationswerte durch Azure SQL Database nicht mehr geändert, sofern sie sich nicht negativ auf die primäre Workload oder den zuverlässigen Betrieb des Abfragespeichers auswirken.

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 Standarderfassungsmodus 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 Standarderfassungsmodus.
None	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 Erfassungsmodus unter dem ALTER DATABASE ... SET QUERY_STORE-Befehl ein. Obwohl Auto die Standardeinstellung ist und empfohlen wird, können Datenbankadministratoren, wenn sie Bedenken wegen des Mehraufwands haben, den der Abfragespeicher verursachen könnte, benutzerdefinierte Erfassungsrichtlinien verwenden, um das Erfassungsverhalten des Abfragespeichers weiter zu optimieren. 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:

Best Practice	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- Erfassungsmodus aktiviert ist, sind zusätzliche Abfragespeicher-Konfigurationen unter einer neuen Abfragespeicher- Erfassungsrichtlinieneinstellung verfügbar, um die Datensammlung auf einem bestimmten Server fein abzustimmen.

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.

Abfragespeicher- Erfassungsmodus: gibt die Abfrageerfassungsrichtlinie für den Abfragespeicher an.

• Alle: erfasst alle Abfragen. Diese Option ist die Standardeinstellung in SQL Server 2016 (13.x) und SQL Server 2017 (14.x).
• Automatisch: Unregelmäßige 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: Der Abfragespeicher beendet die Erfassung neuer Abfragen.
• Benutzerdefiniert: Bietet zusätzliche Steuerungsmöglichkeiten und ermöglicht die Optimierung der Datensammlungsrichtlinie. 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

Die aktuelle Version von SQL Server Management Studio (SSMS)

So zeigen Sie die aktuellen Einstellungen in Management Studio an:

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

T-SQL

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

Beispiel für benutzerdefinierte Erfassungsrichtlinien

Im folgenden Beispiel wird QUERY_CAPTURE_MODE auf AUTO festgelegt und ein benutzerdefinierter Erfassungsmodus festgelegt. Jeder der folgenden Richtlinien legt die benutzerdefinierten Erfassungsrichtlinien 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 überschreibt.

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 Erfassungsrichtlinie (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 der Abfragespeicher automatisch den SCHREIBGESCHÜTZTEN Betriebsmodus und beendet die Erfassung von neuen Daten, sodass die Leistungsanalyse nicht mehr korrekt ist.

• Für die 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 die Begrenzung MAX_STORAGE_SIZE_MB zwischen Speichergrößenüberprüfungen überschritten 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 ausreichend Speicherplatz befreit wurde, wird der Abfragespeichermodus automatisch in den LESE-/SCHREIBMODUS gewechselt.

Weitere Informationen finden Sie unter ALTER DATABASE SET-Optionen.

Datenleerungsintervall (Minuten)

Das Intervall für die Datenbereinigung legt fest, wie oft die gesammelten Laufzeitstatistiken auf der Festplatte gespeichert werden. In SQL Server Management Studio wird der Wert in Minuten angegeben, aber in Transact-SQL wird er in Sekunden ausgedrückt. Der Standardwert beträgt 15 Minuten (900 Sekunden).

• Eine Vergrößerung des Datenflush-Intervalls kann die Gesamtauswirkungen des Query Store auf die Speicher-E/A reduzieren, führt jedoch zu einer stärkeren Auslastung des Speichers mit weniger, aber stärkeren Auswirkungen auf die Festplattenauslastung. 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.
• Wenn Sie das Intervall für den Datenabgleich verringern, verringert sich die Menge der Abfragespeicherdaten, die im Falle eines Herunterfahrens, eines Stromausfalls oder eines Failovers verloren gehen würden. Dieses kann auch die Auswirkungen des Speicher-E/A im Abfragespeicher glätten, indem es 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 der aktuellen Einstellungen des Abfragespeichers

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

SSMS

Die aktuelle Version von SQL Server Management Studio (SSMS)

So zeigen Sie die aktuellen Einstellungen in Management Studio an:

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

T-SQL

Führen Sie das folgende T-SQL-Skript aus, um die neuesten Informationen über die Größe des Abfragespeichers 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);

Statistikerfassungsintervall: definiert die Granularität für die gesammelten Laufzeitstatistiken, 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);

Schwellenwert für veraltete Abfrage (Tage): Zeitbasierte Cleanuprichtlinie, die die Aufbewahrungsdauer für persistente Laufzeitstatistiken und inaktive 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 die zeitbasierte Cleanuprichtlinie zu konfigurieren:

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

Größenbasierter Bereinigungsmodus: gibt an, ob das automatische Cleanup ausgeführt wird, wenn der Umfang der Abfragespeicherdaten den Grenzwert erreicht. Aktivieren Sie die größenbasierte Bereinigung, um sicherzustellen, dass der Abfragespeicher immer im Lese-/ Schreibmodus ausgeführt wird und die neuesten Daten erfasst. Bei hohen Arbeitsauslastungen gibt es keine Garantie dafür, dass die Bereinigung des Abfragespeichers die Datengröße stets unter dem Grenzwert hält. 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);

Erfassungsmodus für den Abfragespeicher: gibt die Abfrageerfassungsrichtlinie für Abfragespeicher an.

• Alle: erfasst alle Abfragen. Diese Option ist die Standardeinstellung in SQL Server 2016 (13.x) und SQL Server 2017 (14.x).
• Automatisch: Unregelmäßige 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: Der Abfragespeicher beendet die Erfassung neuer Abfragen.
• Benutzerdefiniert: Bietet zusätzliche Steuerungsmöglichkeiten und ermöglicht die Optimierung der Datensammlungsrichtlinie. 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 gesetzt und es werden weitere 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 gesetzt und es werden weitere empfohlene Optionen in SQL Server 2017 (14.x) zum Einschließen von Wartezeitstatistiken 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,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

Das folgende Beispiel setzt die CUSTOM-Erfassungsrichtlinie auf die Standardwerte von SQL Server 2019 (15.x) statt auf den neuen Standardmodus der automatischen Erfassung. Weitere Informationen zu benutzerdefinierten Erfassungsrichtlinienoptionen 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-Wartung

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. Sie können eine der folgenden Intervalle verwenden: 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 nicht mehr genügend Speicherplatz vorhanden ist, sollten Sie mithilfe der folgenden Anweisung ältere Daten aus dem Abfragespeicher löschen:

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Sie können auch nur Ad-hoc-Abfragedaten löschen, da diese evtl. für die Abfrageoptimierung und Plananalyse weniger wichtig sind, aber trotzdem viel Platz einnehmen.

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

Löschen von Ad-hoc-Abfragen

Dadurch werden Ad-hoc- und interne Abfragen aus dem Abfragespeicher gelöscht, damit der Abfragespeicher über ausreichend Speicherplatz verfügt und keine Abfragen entfernt, die unbedingt nachverfolgt werden 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 vorhergehenden Beispiel wird die erweiterte gespeicherte Prozedur sp_query_store_remove_query verwendet, um nicht benötigte 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 Abfragespeichers mit In-Memory-OLTP
• Leitfaden zur Architektur der Abfrageverarbeitung
• Abfragespeicherhinweise
• Überwachen der Leistung mithilfe des Abfragespeichers
• Optimieren der Leistung mit dem Abfragespeicher
• Speicherung und Analyse von Verlaufsabfragen in Azure Synapse Analytics