Bewährte Methoden für den AbfragespeicherBest practices with Query Store

GILT FÜR: JaSQL Server JaAzure SQL-Datenbank JaAzure Synapse Analytics (SQL DW) NeinParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

In diesem Artikel werden die bewährten Methoden für den Einsatz des SQL Server-Abfragespeichers mit Ihrer Arbeitsauslastung vorgestellt.This article outlines the best practices for using SQL Server Query Store with your workload.

Verwenden des neuesten SQL Server Management StudioSQL Server Management StudioUse the latest SQL Server Management StudioSQL Server Management Studio

SQL Server Management StudioSQL Server Management Studio verfügt über mehrere Benutzeroberflächen, die zum Konfigurieren des Abfragespeichers und zur Nutzung der gesammelten Daten über Ihre Arbeitsauslastung konzipiert wurden.has a set of user interfaces designed for configuring Query Store and for consuming collected data about your workload. Laden Sie die neueste Version von Management StudioManagement Studio hier herunter.Download the latest version of Management StudioManagement Studio here.

Eine kurze Beschreibung zur Verwendung des Abfragespeichers bei Fehlerbehebungen finden Sie in den @AzureBlogs zu Abfragespeichern.For a quick description on how to use Query Store in troubleshooting scenarios, see Query Store Azure blogs.

Verwenden von Query Performance Insight in Azure SQL-DatenbankUse Query Performance Insight in Azure SQL Database

Wenn Sie den Abfragespeicher in Azure SQL-DatenbankSQL Database ausführen, können Sie mit Query Performance Insight die Ressourcennutzung im Verlauf der Zeit analysieren.If you run Query Store in Azure SQL-DatenbankSQL Database, you can use Query Performance Insight to analyze resource consumption over time. Sie können zwar Management StudioManagement Studio und Azure Data Studio verwenden, um detaillierte Ressourcennutzungswerte für alle Ihre Abfragen wie CPU, Arbeitsspeicher und E/A abzurufen, Query Performance Insight bietet Ihnen jedoch eine schnelle und effiziente Möglichkeit, um deren Auswirkung auf den DTU-Verbrauch Ihrer Datenbank insgesamt zu ermitteln.While you can use Management StudioManagement Studio and Azure Data Studio to get detailed resource consumption for all your queries, such as CPU, memory, and I/O, Query Performance Insight gives you a quick and efficient way to determine their impact on overall DTU consumption for your database. Weitere Informationen finden Sie unter Query Performance Insight für Azure SQL-Datenbank.For more information, see Azure SQL Database Query Performance Insight.

Dieser Abschnitt beschreibt die optimalen Standardeinstellungen der Konfiguration, mit denen der zuverlässige Betrieb des Abfragespeichers und der abhängigen Features sichergestellt wird.This section describes optimal configuration defaults that are designed to ensure reliable operation of the Query Store and dependent features. Die Standardkonfiguration ist für die fortlaufende Datensammlung optimiert. Dies bedeutet, dass möglichst wenig Zeit im Status OFF bzw. READ_ONLY verbracht wird.Default configuration is optimized for continuous data collection, that is minimal time spent in OFF/READ_ONLY states.

KonfigurationConfiguration BESCHREIBUNGDescription StandardDefault CommentComment
MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB Gibt das Limit für den Datenspeicherplatz an, den der Abfragespeicher in der Kundendatenbank belegt.Specifies the limit for the data space that Query Store can take inside the customer database 100100 Für neue Datenbanken erzwungenEnforced for new databases
INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES Definiert die Größe des Zeitfensters, in dem gesammelte Laufzeitstatistiken für Abfragepläne aggregiert und dauerhaft gespeichert werden.Defines size of time window during which collected runtime statistics for query plans are aggregated and persisted. Jeder aktive Abfrageplan verfügt über maximal eine Zeile für einen mit dieser Konfiguration definierten Zeitraum.Every active query plan has at most one row for a period of time defined with this configuration 6060 Für neue Datenbanken erzwungenEnforced for new databases
STALE_QUERY_THRESHOLD_DAYSSTALE_QUERY_THRESHOLD_DAYS Zeitbasierte Bereinigungsrichtlinie, mit der der Aufbewahrungszeitraum für dauerhaft gespeicherte Laufzeitstatistiken und inaktive Abfragen gesteuert wird.Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries 3030 Für neue Datenbanken und Datenbanken mit vorheriger Standardeinstellung erzwungen (367)Enforced for new databases and databases with previous default (367)
SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE Gibt an, ob die automatische Datenbereinigung durchgeführt wird, wenn der Datenumfang des Abfragespeichers sich dem Grenzwert nähert.Specifies whether automatic data cleanup takes place when Query Store data size approaches the limit AUTOAUTO Für alle Datenbanken erzwungenEnforced for all databases
QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE Gibt an, ob die Gesamtmenge aller Abfragen oder nur eine Teilmenge der Abfragen nachverfolgt wird.Specifies whether all queries or only a subset of queries are tracked AUTOAUTO Für alle Datenbanken erzwungenEnforced for all databases
FLUSH_INTERVAL_SECONDSFLUSH_INTERVAL_SECONDS Gibt an, wie lange gesammelte Laufzeitstatistiken maximal im Arbeitsspeicher aufbewahrt werden, bevor eine Leerung auf einen Datenträger erfolgt.Specifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk 900900 Für neue Datenbanken erzwungenEnforced for new databases

Wichtig

Diese Standardeinstellungen werden in der letzten Phase der Abfragespeicheraktivierung automatisch auf alle Azure SQL-Datenbanken angewendet (siehe wichtigen Hinweis oben).These defaults are automatically applied in the final stage of Query Store activation in all Azure SQL databases (see preceding important note). Nach diesem Optimierungsschritt werden die von Kunden festgelegten Konfigurationswerte für Azure SQL-Datenbank nicht mehr geändert, es sei denn, sie wirken sich negativ auf die primäre Workload oder den zuverlässigen Betrieb des Abfragespeichers aus.After this light up, Azure SQL Database won't be changing configuration values set by customers, unless they negatively impact primary workload or reliable operations of the Query Store.

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.If you want to stay with your custom settings, use ALTER DATABASE with Query Store options to revert configuration to the previous state. Lesen Sie den Artikel Bewährte Methoden für den Abfragespeicher , um zu erfahren, wie Sie die optimalen Konfigurationsparameter auswählen.Check out Best Practices with the Query Store in order to learn how top chose optimal configuration parameters.

Verwenden des Abfragespeichers mit Pools für elastische DatenbankenUse Query Store with elastic pool databases

Sie können den Abfragespeicher bedenkenlos in allen Datenbanken verwenden, selbst in dicht gepackten Pools.You can use Query Store in all databases without concerns, in even densely packed pools. Alle mit übermäßiger Ressourcennutzung zusammenhängenden Probleme, die bei der Aktivierung des Abfragespeichers für die große Anzahl Datenbanken in Pools für elastische Datenbanken auftreten konnten, wurden behoben.All issues related to excessive resource usage that might have occurred when Query Store was enabled for the large number of databases in the elastic pools have been resolved.

Dauerhafte Abfragespeicheranpassung an Ihre ArbeitsauslastungKeep Query Store adjusted to your workload

Konfigurieren Sie Abfragespeicher basierend auf den Anforderungen hinsichtlich der Arbeitsauslastung und der Behandlung von Leistungsproblemen.Configure Query Store based on your workload and performance troubleshooting requirements. 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.The default parameters are good enough to start, but you should monitor how Query Store behaves over time and adjust its configuration accordingly.

Eigenschaften des AbfragespeichersQuery Store properties

Hier sind einige Richtlinien zum Festlegen der Parameterwerte:Here are guidelines to follow for setting parameter values:

Maximale Größe (MB): Gibt den Grenzwert für den Datenbereich an, den der Abfragespeicher innerhalb der Datenbank einnimmt.Max Size (MB): Specifies the limit for the data space that Query Store takes inside your database. Dies ist die wichtigste Einstellung, die sich direkt auf den Betriebsmodus des Abfragespeichers auswirkt.This is the most important setting that directly affects the operation mode of Query Store.

Während der Abfragespeicher Abfragen, Ausführungspläne und Statistiken sammelt, wächst die Datenbank an, bis dieser Grenzwert erreicht ist.While Query Store collects queries, execution plans, and statistics, its size in the database grows until this limit is reached. In diesem Fall ändert der Abfragespeicher automatisch den Betriebsmodus in schreibgeschützt und beendet die Erfassung von neuen Daten, sodass die Leistungsanalyse nicht mehr korrekt ist.When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.

Der Standardwert in SQL Server 2016 (13.x)SQL Server 2016 (13.x) und SQL Server 2017 (14.x)SQL Server 2017 (14.x) ist 100 MB.The default value in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x) is 100 MB. Diese Größe reicht möglicherweise nicht aus, wenn die Arbeitsauslastung eine große Anzahl unterschiedlicher Abfragen und Pläne generiert oder wenn der Abfrageverlauf einen längeren Zeitraum aufbewahrt werden soll.This size might not be sufficient if your workload generates a large number of different queries and plans or if you want to keep query history for a longer period of time. Ab SQL Server 2019 (15.x)SQL Server 2019 (15.x) beträgt der Standardwert 1 GB.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is 1 GB. Verfolgen Sie den aktuellen Speicherplatz und erhöhen Sie den Wert für Maximale Größe (MB) , um zu verhindern, dass der Abfragespeicher in den schreibgeschützten Modus übergeht.Keep track of current space usage and increase the Max Size (MB) value to prevent Query Store from transitioning to read-only mode.

Wichtig

Der Grenzwert Maximale Größe (MB) wird nicht erzwungen.The Max Size (MB) limit isn't strictly enforced. Die Speichergröße wird nur überprüft, wenn der Abfragespeicher Daten auf einen Datenträger schreibt.Storage size is checked only when Query Store writes data to disk. Das Intervall wird durch die Option Datenleerungsintervall (Minuten) festgelegt.This interval is set by the Data Flush Interval (Minutes) option. Wenn der Abfragespeicher die maximale Größe zwischen Speichergrößenüberprüfungen überschritten hat, geht er in den schreibgeschützten Modus über.If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode. Bei Aktivierung von Größenbasierter Bereinigungsmodus wird auch der Bereinigungsmechanismus zum Erzwingen der maximalen Größe ausgelöst.If Size Based Cleanup Mode is enabled, the cleanup mechanism to enforce the maximum size limit is also triggered.

Verwenden Sie Management StudioManagement Studio , oder führen Sie das folgende Skript aus, um aktuelle Informationen zur Größe des Abfragespeichers zu erhalten:Use Management StudioManagement Studio or execute the following script to get the latest information about Query Store size:

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:The following script sets a new value for Max Size (MB):

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

Datenleerungsintervall (Minuten) : Diese Option definiert die Häufigkeit, mit der die erfassten Laufzeitstatistiken auf dem Datenträger gespeichert werden.Data Flush Interval (Minutes): It defines the frequency to persist collected runtime statistics to disk. In der grafischen Benutzeroberfläche wird sie in Minuten ausgedrückt, in Transact-SQLTransact-SQL wird sie jedoch in Sekunden angegeben.It's expressed in minutes in the graphical user interface (GUI), but in Transact-SQLTransact-SQL it's expressed in seconds. Der Standardwert ist 900 Sekunden, d.h. 15 Minuten in der grafischen Benutzeroberfläche.The default is 900 seconds, which is 15 minutes in the graphical user interface. 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.Consider using a higher value if your workload doesn't generate a large number of different queries and plans, or if you can withstand longer time to persist data before a database shutdown.

Hinweis

Mit dem Ablaufverfolgungsflag 7745 wird verhindert, dass Abfragespeicherdaten bei einem Failover oder Befehl zum Herunterfahren auf den Datenträger geschrieben werden.Using trace flag 7745 prevents Query Store data from being written to disk in case of a failover or shutdown command. Weitere Informationen finden Sie im Abschnitt Verwenden von Ablaufverfolgungsflags für unternehmenskritische Server.For more information, see the Use trace flags on mission-critical servers section.

Verwenden Sie SQL Server Management StudioSQL Server Management Studio oder Transact-SQLTransact-SQL, um verschiedene Werte für das Datenleerungsintervall festzulegen:Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Data Flush Interval:

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

Statistiksammelungsintervall: Definiert den Grad der Granularität für die erfasste Laufzeitstatistik, ausgedrückt in Minuten.Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. Der Standardwert ist 60 Sekunden.The default is 60 minutes. 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.Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Denken Sie daran, dass der Wert die Größe der Abfragespeicherdaten direkt beeinflusst.Keep in mind that the value directly affects the size of Query Store data. Verwenden Sie SQL Server Management StudioSQL Server Management Studio oder Transact-SQLTransact-SQL, um einen anderen Wert für das Statistiksammlungsintervall festzulegen:Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Statistics Collection Interval:

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

Schwellenwert für veraltete Abfragen (Tage) : Zeitbasierte Richtlinie zur Bereinigung, die den Aufbewahrungszeitraum für persistente Laufzeitstatistiken und inaktive Abfragen steuert, ausgedrückt in Tagen.Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. 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.By default, Query Store is configured to keep the data for 30 days, which might be unnecessarily long for your scenario.

Vermeiden Sie es, Verlaufsdaten aufzubewahren, die Sie nicht mehr verwenden möchten.Avoid keeping historical data that you don't plan to use. Dies reduziert die Wahrscheinlichkeit für Änderungen in den schreibgeschützten Status.This practice reduces changes to read-only status. Die Größe der Abfragespeicherdaten sowie die Zeit, um Probleme zu erkennen und zu mindern, lassen sich besser vorhersagen.The size of Query Store data and the time to detect and mitigate the issue will be more predictable. Verwenden Sie Management StudioManagement Studio oder das folgende Skript, um die zeitbasierte Cleanuprichtlinie zu konfigurieren:Use Management StudioManagement Studio or the following script to configure time-based cleanup policy:

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

Größenbasierter Bereinigungsmodus: Gibt an, ob die automatische Bereinigung ausgeführt wird, wenn der Umfang der Abfragespeicherdaten den Grenzwert erreicht.Size Based Cleanup Mode: Specifies whether automatic data cleanup takes place when Query Store data size approaches the limit. Aktivieren Sie die größenbasierte Bereinigung, um sicherzustellen, dass der Abfragespeicher immer im Lese-/ Schreibmodus ausgeführt wird und die neuesten Daten erfasst.Activate size-based cleanup to make sure that Query Store always runs in read-write mode and collects the latest data.

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

Erfassungsmodus für Abfragespeicher: Gibt die Abfrageerfassungsrichtlinie für den Abfragespeicher an.Query Store Capture Mode: Specifies the query capture policy for Query Store.

  • All: Erfasst alle Abfragen.All: Captures all queries. Diese Option ist die Standardeinstellung in SQL Server 2016 (13.x)SQL Server 2016 (13.x) und SQL Server 2017 (14.x)SQL Server 2017 (14.x).This option is the default in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
  • Automatisch: Unregelmäßige Abfragen und Abfragen mit unbedeutender Kompilierungs- und Ausführungsdauer werden ignoriert.Auto: Infrequent queries and queries with insignificant compile and execution duration are ignored. Die Schwellenwerte für die Dauer der Ausführungsanzahl, Kompilierung und Laufzeit werden intern bestimmt.Thresholds for execution count, compile, and runtime duration are internally determined. Ab SQL Server 2019 (15.x)SQL Server 2019 (15.x) ist dies die Standardoption.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default option.
  • Keine: Der Abfragespeicher beendet die Erfassung neuer Abfragen.None: Query Store stops capturing new queries.
  • Benutzerdefiniert: Bietet zusätzliche Steuerungsmöglichkeiten und ermöglicht die Optimierung der Datensammlungsrichtlinie.Custom: Allows additional control and the capability to fine-tune the data collection policy. Die neuen benutzerdefinierten Einstellungen definieren, was während des Zeitschwellenwerts für die interne Erfassungsrichtlinie geschieht.The new custom settings define what happens during the internal capture policy time threshold. 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.This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store.

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.Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. Zum Erfassen von nativ kompilierten Abfragen aktivieren Sie die Sammlung von Statistiken pro Abfrage mithilfe von sys.sp_xtp_control_query_exec_stats.To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

Das folgende Skript legt QUERY_CAPTURE_MODE auf AUTO fest:The following script sets QUERY_CAPTURE_MODE to AUTO:

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

BeispieleExamples

Im folgenden Beispiel wird QUERY_CAPTURE_MODE auf AUTO gesetzt und es werden weitere empfohlene Optionen in SQL Server 2016 (13.x)SQL Server 2016 (13.x) festgelegt:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2016 (13.x)SQL Server 2016 (13.x):

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)SQL Server 2017 (14.x) zum Einschließen von Wartezeitstatistiken festgelegt:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2017 (14.x)SQL Server 2017 (14.x) to include wait statistics:

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 QUERY_CAPTURE_MODE auf AUTO gesetzt und es werden weitere empfohlene Optionen in SQL Server 2019 (15.x)SQL Server 2019 (15.x) festgelegt. Optional wird anstelle des neuen Erfassungsmodus AUTO die benutzerdefinierte (CUSTOM) Erfassungsrichtlinie mit den zugehörigen Standardeinstellungen festgelegt:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2019 (15.x)SQL Server 2019 (15.x), and optionally sets the CUSTOM capture policy with its defaults, instead of the new default AUTO capture mode:

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
      )
    );

Erste Schritte bei der Behandlung von LeistungsproblemenStart with query performance troubleshooting

Der Workflow zur Behandlung von Problemen mit dem Abfragespeicher ist einfach, wie im folgenden Diagramm dargestellt:The troubleshooting workflow with Query Store is simple, as shown in the following diagram:

Problembehandlung bei AbfragespeichernQuery Store troubleshooting

Aktivieren Sie den Abfragespeicher mit Management StudioManagement Studio, wie im vorherigen Abschnitt beschrieben, oder führen Sie die folgende Transact-SQLTransact-SQL -Anweisung aus:Enable Query Store by using Management StudioManagement Studio, as described in the previous section, or execute the following Transact-SQLTransact-SQL statement:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Es dauert einige Zeit, bis der Abfragespeicher das Dataset erfasst, das Ihre Arbeitsauslastung präzise darstellt.It takes some time until Query Store collects the data set that accurately represents your workload. In der Regel reicht ein Tag, selbst bei sehr komplexen Arbeitsauslastungen.Usually, one day is enough even for very complex workloads. Sie können jedoch unmittelbar nach Aktivierung der Funktion damit beginnen, die Daten zu untersuchen und Abfragen zu identifizieren, die Ihre Aufmerksamkeit erfordern.However, you can start exploring the data and identify queries that need your attention immediately after you enable the feature. Navigieren Sie zu dem Abfragespeicher-Unterordner unter dem Datenbankknoten im Objekt-Explorer von Management StudioManagement Studio, um Problembehandlungsansichten für bestimmte Szenarios zu öffnen.Go to the Query Store subfolder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.

Management StudioManagement Studio -Abfragespeicheransichten arbeiten mit dem Satz von Ausführungsmetriken, die alle als eine der folgenden Statistikfunktionen ausgedrückt werden:Query Store views operate with the set of execution metrics, each expressed as any of the following statistic functions:

SQL ServerSQL Server-Versionversion AusführungsmetrikExecution metric StatistikfunktionStatistic function
SQL Server 2016 (13.x)SQL Server 2016 (13.x) CPU-Zeit, Dauer, Ausführungsanzahl, logische Lesevorgänge, logische Schreibvorgänge, Speicherverbrauch, physische Lesevorgänge, CLR-Zeit, Parallelitätsgrad (Degree of Parallelism, DOP) und ZeilenanzahlCPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism (DOP), and Row count Durchschnitt, Maximum, Minimum, Standardabweichung, GesamtAverage, Maximum, Minimum, Standard Deviation, Total
SQL Server 2017 (14.x)SQL Server 2017 (14.x) CPU-Zeit, Dauer, Ausführungsanzahl, logische Lesevorgänge, logische Schreibvorgänge, Speicherverbrauch, physische Lesevorgänge, CLR-Zeit, Parallelitätsgrad, Zeilenanzahl, Protokollspeicher, TempDB-Speicher und WartezeitenCPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism, Row count, Log memory, TempDB memory, and Wait times Durchschnitt, Maximum, Minimum, Standardabweichung, GesamtAverage, Maximum, Minimum, Standard Deviation, Total

Die folgende Grafik veranschaulicht, wie Sie die Abfragespeicheransichten suchen:The following graphic shows how to locate Query Store views:

Ansichten von AbfragespeichernQuery Store views

In der folgenden Tabelle wird erläutert, wann Sie die einzelnen Abfragespeicheransichten verwenden sollten:The following table explains when to use each of the Query Store views:

SQL Server Management Studio-AnsichtSQL Server Management Studio view SzenarioScenario
Rückläufige AbfragenRegressed Queries Identifizieren von Abfragen, bei denen die Ausführungsmetriken vor kurzem rückläufig waren (z.B. sich verschlechtert haben).Pinpoint queries for which execution metrics have recently regressed (for example, changed to worse).
Verwenden Sie diese Ansicht, um beobachtete Leistungsprobleme in Ihrer Anwendung mit den tatsächlichen Abfragen zu korrelieren, die korrigiert oder verbessert werden müssen.Use this view to correlate observed performance problems in your application with the actual queries that need to be fixed or improved.
Gesamter RessourcenverbrauchOverall Resource Consumption Analysieren Sie den Gesamtressourcenverbrauch für die Datenbank für eine der Ausführungsmetriken.Analyze the total resource consumption for the database for any of the execution metrics.
Verwenden Sie diese Ansicht, um Ressourcenmuster zu identifizieren (tägliche im Vergleich zu nächtlichen Arbeitsauslastungen), und optimieren Sie den Gesamtverbrauch für Ihre Datenbank.Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.
Abfragen mit höchstem RessourcenverbrauchTop Resource Consuming Queries Wählen Sie die gewünschte Ausführungsmetrik, und identifizieren Sie Abfragen mit den extremsten Werten für ein angegebenes Zeitintervall.Choose an execution metric of interest, and identify queries that had the most extreme values for a provided time interval.
Verwenden Sie diese Ansicht, um sich auf die relevantesten Abfragen zu konzentrieren, die die größte Auswirkung auf den Ressourcenverbrauch der Datenbank haben.Use this view to focus your attention on the most relevant queries that have the biggest impact to database resource consumption.
Abfragen mit erzwungenen PlänenQueries With Forced Plans Zeigt vorherige erzwungene Pläne durch Verwendung des Abfragespeichers an.Lists previously forced plans using Query Store.
Verwenden Sie diese Ansicht, um schnell auf alle aktuell erzwungenen Pläne zuzugreifen.Use this view to quickly access all currently forced plans.
Abfragen mit hoher VariationQueries With High Variation Analysieren Sie Abfragen mit hoher Ausführungsvariation in Verbindung mit allen verfügbaren Dimensionen wie Dauer, CPU-Zeit, E/A und Speicherauslastung im gewünschten Zeitintervall.Analyze queries with high-execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage, in the desired time interval.
Verwenden Sie diese Ansicht, um Abfragen mit stark abweichender Leistung zu identifizieren, die die Benutzerfreundlichkeit in Ihren Anwendungen beeinträchtigen können.Use this view to identify queries with widely variant performance that can be affecting user experience across your applications.
Statistik der AbfragewartezeitQuery Wait Statistics Analysieren Sie Wartekategorien, die in einer Datenbank am aktivsten sind, sowie welche Abfragen am meisten zur ausgewählten Wartekategorie beitragen.Analyze wait categories that are most active in a database and which queries contribute most to the selected wait category.
Verwenden Sie diese Ansicht, um Wartezeitstatistiken zu analysieren und Abfragen zu identifizieren, die sich auf die Benutzerfreundlichkeit in Ihren Anwendungen auswirken können.Use this view to analyze wait statistics and identify queries that might be affecting user experience across your applications.

Gilt für: Ab SQL Server Management StudioSQL Server Management Studio, Version 18.0 und SQL Server 2017 (14.x)SQL Server 2017 (14.x).Applies to: Starting with SQL Server Management StudioSQL Server Management Studio v18.0 and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
Nachverfolgte AbfragenTracked Queries Verfolgen Sie die Ausführung der wichtigsten Abfragen in Echtzeit.Track the execution of the most important queries in real time. In der Regel verwenden Sie diese Ansicht, wenn Sie über Abfragen mit erzwungenen Plänen verfügen und Sie sicherstellen möchten, dass die Abfrageleistung stabil ist.Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.

Tipp

Eine ausführliche Beschreibung dazu, wie Sie mit Management StudioManagement Studio die Abfragen mit dem größten Ressourcenverbrauch identifizieren und diejenigen Abfragen korrigieren können, die aufgrund der Änderung der Planauswahl zurückgestellt wurden, finden Sie in den Blogs zu AbfragespeichernAzure.For a detailed description of how to use Management StudioManagement Studio to identify the top resource-consuming queries and fix those that regressed due to the change of a plan choice, see Query Store Azure Blogs.

Wenn Sie eine Abfrage mit nicht optimaler Leistung identifiziert haben, richtet sich das weitere Vorgehen nach der Art des Problems.When you identify a query with suboptimal performance, your action depends on the nature of the problem.

  • Wenn die Abfrage mit mehreren Plänen ausgeführt wurde und der letzte Plan signifikant schlechter als der vorherige ist, können Sie den Planerzwingungsmechanismus verwenden, um diesen zu erzwingen.If the query was executed with multiple plans and the last plan is significantly worse than the previous plan, you can use the plan forcing mechanism to force it. SQL ServerSQL Server versucht, den Plan im Optimierer zu erzwingen.tries to force the plan in the optimizer. Wenn das Erzwingen des Plans fehlschlägt, wird ein XEvent ausgelöst, und der Optimierer wird angewiesen, die Optimierung auf die übliche Weise durchzuführen.If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

    Erzwingen des Plans für AbfragespeicherQuery Store force plan

    Hinweis

    Die vorherige Abbildung kann verschiedene Formen für bestimmte Abfragepläne aufweisen, wobei die möglichen Status folgende Bedeutungen haben:The previous graphic might feature different shapes for specific query plans, with the following meanings for each possible status:

    FormShape BedeutungMeaning
    CircleCircle Abfrage abgeschlossen, d.h., dass eine reguläre Ausführung erfolgreich abgeschlossen wurde.Query completed, which means that a regular execution successfully finished.
    SquareSquare Abgebrochen, d.h., dass ein vom Client initiierter Abbruch der Ausführung erfolgte.Cancelled, which means that a client-initiated aborted execution.
    TriangleTriangle Fehlerhaft, d.h., dass die Ausführung durch eine Ausnahme abgebrochen wurde.Failed, which means that an exception aborted execution.

    Darüber hinaus gibt die Größe der Form Aufschluss über die Anzahl von Abfrageausführungen innerhalb des angegebenen Zeitintervalls.Also, the size of the shape reflects the query execution count within the specified time interval. Die Größe der Form nimmt mit zunehmender Anzahl von Ausführungen zu.The size increases with a higher number of executions.

  • Sie können daraus schließen, dass der Abfrage ein Index für eine optimale Ausführung fehlt.You might conclude that your query is missing an index for optimal execution. Diese Informationen werden innerhalb des Abfrageausführungsplans eingeblendet.This information is surfaced within the query execution plan. Erstellen Sie den fehlenden Index, und überprüfen Sie die Abfrageleistung mit dem Abfragespeicher.Create the missing index, and check the query performance by usingQuery Store.

    Anzeigen des Plans für AbfragespeicherQuery Store show plan

Wenn Sie Ihre Arbeitsauslastung auf SQL-DatenbankSQL Databaseausführen, registrieren Sie sich für den SQL-DatenbankSQL Database -Indexratgeber, um automatisch Indexempfehlungen zu erhalten.If you run your workload on SQL-DatenbankSQL Database, sign up for SQL-DatenbankSQL Database Index Advisor to automatically receive index recommendations.

  • In einigen Fällen können Sie eine statistische Neukompilierung erzwingen, wenn Sie feststellen, dass der Unterschied zwischen der geschätzten und der tatsächlichen Anzahl der Zeilen im Ausführungsplan maßgeblich ist.In some cases, you might enforce statistic recompilation if you see that the difference between the estimated and the actual number of rows in the execution plan is significant.
  • Schreiben Sie problematische Abfragen neu, beispielsweise, um die Vorteile der Abfrageparametrisierung nutzen zu können oder um eine bessere Logik zu implementieren.Rewrite problematic queries, for example, to take advantage of query parameterization or to implement more optimal logic.

Überprüfen, ob der Abfragespeicher kontinuierlich Abfragedaten erfasstVerify that Query Store collects query data continuously

Der Abfragespeicher kann den Betriebsmodus automatisch ändern.Query Store can silently change the operation mode. Überwachen Sie regelmäßig den Status des Abfragespeichers, um sicherzustellen, dass der Abfragespeicher funktioniert, und um Maßnahmen zu ergreifen, damit so Ausfälle aufgrund von vermeidbaren Ursachen verhindert werden.Regularly monitor the state of Query Store to ensure that Query Store is operating, and to take action to avoid failures due to preventable causes. Führen Sie die folgende Abfrage aus, um den Betriebsmodus zu ermitteln und die wichtigsten Parameter anzuzeigen:Execute the following query to determine the operation mode and view the most relevant parameters:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Der Unterschied zwischen actual_state_desc und desired_state_desc weist darauf hin, dass automatisch eine Änderung des Betriebsmodus aufgetreten ist.The difference between the actual_state_desc and desired_state_desc indicates that a change of the operation mode occurred automatically. Die häufigste Änderung besteht darin, dass der Abfragespeicher im Hintergrund in den schreibgeschützten Modus wechselt.The most common change is for Query Store to silently switch to read-only mode. In sehr seltenen Fällen kann sich der Abfragespeicher in einem fehlerhaften Zustand aufgrund von internen Fehlern befinden.In extremely rare circumstances, Query Store can end up in the ERROR state because of internal errors.

Wenn der tatsächliche Status schreibgeschützt ist, verwenden Sie die readonly_reason -Spalte, um die Ursache zu ermitteln.When the actual state is read-only, use the readonly_reason column to determine the root cause. In der Regel werden Sie feststellen, dass der Abfragespeicher in den schreibgeschützten Modus gewechselt hat, da das Kontingent überschritten wurde.Typically, you find that Query Store transitioned to read-only mode because the size quota was exceeded. In diesem Fall wird readonly_reason auf 65536 festgelegt.In that case, the readonly_reason is set to 65536. Andere Gründe finden Sie unter sys.database_query_store_options (Transact-SQL).For other reasons, see sys.database_query_store_options (Transact-SQL).

Ziehen Sie die folgenden Schritte in Betracht, um den Abfragespeicher in den schreibgeschützten Modus zu schalten und die Datensammlung zu aktivieren:Consider the following steps to switch Query Store to read-write mode and activate data collection:

  • Erhöhen Sie die maximale Speichergröße mithilfe der MAX_STORAGE_SIZE_MB -Option von ALTER DATABASE.Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.

  • Bereinigen Sie die Abfragespeicherdaten mithilfe der folgenden Anweisung:Clean up Query Store data by using the following statement:

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
    

Wenden Sie einen oder beide der folgenden Schritte an, indem Sie die folgende Anweisung ausführen, die den Betriebsmodus explizit wieder in den Lese-/ Schreibzugriff zurücksetzt:You can apply one or both of these steps by executing the following statement that explicitly changes the operation mode back to read-write:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Gehen Sie proaktiv folgendermaßen vor:Take the following steps to be proactive:

  • Sie können die automatischen Änderungen des Betriebsmodus durch Anwenden bewährter Methoden verhindern.You can prevent silent changes of operation mode by applying best practices. Stellen Sie sicher, dass die Abfragespeichergröße immer unterhalb des maximal zulässigen Werts liegt, um so die Wahrscheinlichkeit des Übergangs in den schreibgeschützten Modus maßgeblich zu verringern.Ensure that Query Store size is always below the maximally allowed value to dramatically reduce a chance of transitioning to read-only mode. Aktivieren Sie die größenbasierte Richtlinie, wie im Abschnitt zum Konfigurieren des Abfragespeichers beschrieben, sodass der Abfragespeicher die Daten automatisch bereinigt, wenn sich die Größe dem Grenzwert nähert.Activate size-based policy as described in the Configure Query Store section so that Query Store automatically cleans data when the size approaches the limit.
  • Um sicherzustellen, dass die neuesten Daten beibehalten werden, konfigurieren Sie die zeitbasierte Richtlinie, um regelmäßig veraltete Informationen zu entfernen.To make sure that most recent data is retained, configure time-based policy to remove stale information regularly.
  • Nicht zuletzt sollten Sie es in Betracht ziehen, den Erfassungsmodus für den Abfragespeicher auf Automatisch einzustellen, da dadurch Abfragen herausgefiltert werden, die in der Regel weniger relevant für Ihre Arbeitsauslastung sind.Finally, consider setting Query Store Capture Mode to Auto because it filters out queries that are usually less relevant for your workload.

FehlerzustandERROR state

Zum Wiederherstellen des Abfragespeichers versuchen Sie explizit den Lese-/Schreibmodus einzustellen, und prüfen Sie den tatsächlichen Status noch mal.To recover Query Store, try explicitly setting the read-write mode and check the actual state again.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Wenn das Problem weiterhin besteht, bedeutet dies, dass die beschädigten Abfragespeicherdaten auf dem Datenträger beibehalten werden.If the problem persists, it indicates that corruption of Query Store data is persisted on the disk.

Ab SQL Server 2017 (14.x)SQL Server 2017 (14.x) kann der Abfragespeicher wiederhergestellt werden, indem die gespeicherte Prozedur sp_query_store_consistency_check in der betroffenen Datenbank ausgeführt wird.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure within the affected database. Der Abfragespeicher muss vor dem Wiederherstellungsvorgang deaktiviert werden.Query Store must be disabled before you attempt the recovery operation. Für SQL Server 2016 (13.x)SQL Server 2016 (13.x) müssen Sie die Daten aus dem Abfragespeicher, wie gezeigt, löschen.For SQL Server 2016 (13.x)SQL Server 2016 (13.x), you need to clear the data from Query Store as shown.

Wenn die Wiederherstellung nicht erfolgreich war, können Sie versuchen, den Abfragespeicher vor dem Aktivieren des Lese-/Schreibmodus zu löschen.If the recovery was unsuccessful, you can try clearing Query Store before you set the read-write mode.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_de
FROM sys.database_query_store_options;

Festlegen des optimalen Erfassungsmodus für den AbfragespeicherSet the optimal Query Store Capture Mode

Behalten Sie die relevantesten Daten im Abfragespeicher.Keep the most relevant data in Query Store. Die folgende Tabelle beschreibt die typischen Szenarios für jeden Erfassungsmodus für den Abfragespeicher:The following table describes typical scenarios for each Query Store Capture Mode:

Erfassungsmodus für den AbfragespeicherQuery Store Capture Mode SzenarioScenario
AlleAll Analysieren Sie Ihre Arbeitsauslastung sorgfältig im Hinblick auf alle Abfrageformen und deren Ausführungshäufigkeit und andere Statistiken.Analyze your workload thoroughly in terms of all queries' shapes and their execution frequencies and other statistics.

Identifizieren Sie neue Abfragen in Ihrer Workload.Identify new queries in your workload.

Erkennen Sie, ob Ad-hoc-Abfragen verwendet werden, um Möglichkeiten für Benutzer oder eine automatische Parametrisierung zu identifizieren.Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.

Hinweis: Dies ist der Standarderfassungsmodus in SQL Server 2016 (13.x)SQL Server 2016 (13.x) und SQL Server 2017 (14.x)SQL Server 2017 (14.x).Note: This is the default capture mode in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
AutomatischAuto Konzentrieren Sie sich auf relevante und verwertbare Abfragen.Focus your attention on relevant and actionable queries. Zum Beispiel auf jene Abfragen, die regelmäßig ausgeführt werden oder einen erheblichen Ressourcenverbrauch aufweisen.An example is those queries that execute regularly or that have significant resource consumption.

Hinweis: Ab SQL Server 2019 (15.x)SQL Server 2019 (15.x) ist dies der Standarderfassungsmodus.Note: Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default capture mode.
NoneNone 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.You've already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries might introduce.

„Keine“ ist für Testzwecke geeignet sowie für Vergleichsumgebungen.None is suitable for testing and benchmarking environments.

„Keine“ eignet sich auch für Softwareanbieter, die bei Auslieferung die Abfragespeicherkonfiguration so festlegen, dass die Anwendungsauslastung überwacht wird.None is also appropriate for software vendors who ship Query Store configuration configured to monitor their application workload.

„Keine“ sollte mit Bedacht verwendet werden, da Sie womöglich die Gelegenheit verpassen, wichtige neue Abfragen nachzuverfolgen und zu optimieren.None should be used with caution because you might miss the opportunity to track and optimize important new queries. Vermeiden Sie den Einsatz von „Keine“, es sei denn es ist für ein bestimmtes Szenario erforderlich.Avoid using None unless you have a specific scenario that requires it.
BenutzerdefiniertCustom Mit SQL Server 2019 (15.x)SQL Server 2019 (15.x) wurde ein benutzerdefinierter Erfassungsmodus für den ALTER DATABASE SET QUERY_STORE-Befehl eingeführt.SQL Server 2019 (15.x)SQL Server 2019 (15.x) introduces a Custom capture mode under the ALTER DATABASE SET QUERY_STORE command. Bei Aktivierung stehen zusätzliche Abfragespeicherkonfigurationen unter einer neuen Einstellung für die Erfassungsrichtlinie des Abfragespeichers zur Verfügung, um die Datensammlung auf einem bestimmten Server zu optimieren.When enabled, additional Query Store configurations are available under a new Query Store capture policy setting to fine-tune data collection in a specific server.

Die neuen benutzerdefinierten Einstellungen definieren, was während des Zeitschwellenwerts für die interne Erfassungsrichtlinie geschieht.The new custom settings define what happens during the internal capture policy time threshold. 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.This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store. Weitere Informationen zu dieser Einstellung finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).For more information, see ALTER DATABASE SET Options (Transact-SQL).

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.Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. Zum Erfassen von nativ kompilierten Abfragen aktivieren Sie die Sammlung von Statistiken pro Abfrage mithilfe von sys.sp_xtp_control_query_exec_stats.To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

Aufbewahren der relevantesten Daten im AbfragespeicherKeep the most relevant data in Query Store

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.Configure Query Store to contain only the relevant data so that it runs continuously and provides a great troubleshooting experience with a minimal impact on your regular workload. Die folgende Tabelle enthält bewährte Methoden:The following table provides best practices:

Bewährte MethodeBest practice EinstellungSetting
Begrenzen der Menge von beibehaltenen Verlaufsdaten.Limit retained historical data. Konfigurieren Sie die zeitbasierte Richtlinie, um die automatische Bereinigung zu aktivieren.Configure time-based policy to activate autocleanup.
Filtern Sie nicht relevante Abfragen heraus.Filter out nonrelevant queries. Konfigurieren Sie den Erfassungsmodus für den Abfragespeicher als Automatisch.Configure Query Store Capture Mode to Auto.
Löschen Sie weniger relevanten Abfragen, wenn die maximale Größe erreicht ist.Delete less relevant queries when the maximum size is reached. Aktivieren Sie die größenbasierte Cleanuprichtlinie.Activate size-based cleanup policy.

Vermeiden des Einsatzes von nicht parametrisierten AbfragenAvoid using non-parameterized queries

Es wird nicht empfohlen, parametrisierte Abfragen zu verwenden, wenn dies nicht erforderlich ist.Using non-parameterized queries when that isn't necessary isn't a best practice. Ein Beispiel hierfür ist die Ad-hoc-Analyse.An example is in the case of ad-hoc analysis. Zwischengespeicherte Pläne können nicht wiederverwendet werden, sodass der Abfrageoptimierer gezwungen ist, Abfragen für jeden eindeutigen Abfragetext zu kompilieren.Cached plans can't be reused, which forces Query Optimizer to compile queries for every unique query text. Weitere Informationen finden Sie unter Richtlinien für die Verwendung der erzwungenen Parametrisierung.For more information, see Guidelines for using forced parameterization.

Der Abfragespeicher kann darüber hinaus schnell die Kontingentgröße aufgrund einer potenziell großen Anzahl von verschiedenen Abfragetexten und somit einer großen Anzahl von verschiedenen Ausführungsplänen mit ähnlicher Form überschreiten.Also, Query Store can rapidly exceed the size quota because of a potentially large number of different query texts and consequently a large number of different execution plans with similar shape. Daher wird die Leistung Ihrer Arbeitsauslastung suboptimal sein, und der Abfragespeicher wechselt möglicherweise in den schreibgeschützten Modus oder löscht kontinuierlich die Daten, um mit den eingehenden Abfragen Schritt zu halten.As a result, performance of your workload is suboptimal, and Query Store might switch to read-only mode or constantly delete data to try to keep up with the incoming queries.

Ziehen Sie folgende Möglichkeiten in Betracht:Consider the following options:

  • Parametrisieren Sie Abfragen, sofern möglich.Parameterize queries where applicable. Beispielsweise indem Sie Abfragen innerhalb einer gespeicherten Prozedur oder sp_executesql umschließen.For example, wrap queries inside a stored procedure or sp_executesql. Weitere Informationen finden Sie unter Parameter und Wiederverwendung von Ausführungsplänen.For more information, see Parameters and execution plan reuse.
  • Verwenden Sie die Option Für Ad-hoc-Arbeitsauslastungen optimieren, wenn Ihre Arbeitsauslastung viele einmalige Ad-hoc-Batches mit anderen Abfrageplänen enthält.Use the optimize for ad hoc workloads option if your workload contains many single-use ad-hoc batches with different query plans.
    • Vergleichen Sie die Anzahl der unterschiedlichen query_hash-Werte mit der Gesamtanzahl von Einträgen in sys.query_store_query.Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. Ist das Verhältnis nahe 1, generiert Ihre Ad-hoc-Arbeitsauslastung verschiedene Abfragen.If the ratio is close to 1, your ad-hoc workload generates different queries.
  • Wenden Sie die erzwungene Parametrisierung auf die Datenbank oder auf eine Teilmenge der Abfragen an, wenn die Anzahl der unterschiedlichen Abfragepläne nicht groß ist.Apply forced parameterization for the database or for a subset of queries if the number of different query plans isn't large.
    • Verwenden Sie die Planhinweisliste, um die Parametrisierung nur für die ausgewählte Abfrage zu erzwingen.Use a plan guide to force parameterization only for the selected query.
    • Konfigurieren Sie die erzwungene Parametrisierung über den Befehl für die Option zur Parametrisierung der Datenbank, wenn Ihre Arbeitsauslastung eine kleine Anzahl von unterschiedlichen Abfragepläne umfasst.Configure forced parameterization by using the parameterization database option command, if there are a small number of different query plans in your workload. Ein Beispiel: Das Verhältnis zwischen der Anzahl der unterschiedlichen query_hash und der Gesamtanzahl der Einträge in sys.query_store_query ist wesentlich kleiner als 1.An example is when the ratio between the count of distinct query_hash and the total number of entries in sys.query_store_query is much less than 1.
  • Legen Sie QUERY_CAPTURE_MODE auf AUTO fest, um Ad-hoc-Abfragen mit geringem Ressourcenverbrauch automatisch herauszufiltern.Set QUERY_CAPTURE_MODE to AUTO to automatically filter out ad-hoc queries with small resource consumption.

Vermeiden eines DROP- und CREATE-Musters für enthaltende ObjekteAvoid a DROP and CREATE pattern for containing objects

Der Abfragespeicher ordnet einen Abfrageeintrag einem enthaltenen Objekt zu (gespeicherte Prozedur, Funktion und Trigger).Query Store associates query entry with a containing object, such as stored procedure, function, and trigger. Wenn Sie ein enthaltenes Objekt neu erstellen, wird ein neuer Abfrageeintrag für den gleichen Abfragetext generiert.When you re-create a containing object, a new query entry is generated for the same query text. Dies verhindert die Nachverfolgung der Leistungsstatistiken für diese Abfrage im Verlauf der Zeit und die Anwendung eines Mechanismus zur Nutzungsplanerzwingung.This prevents you from tracking performance statistics for that query over time and using a plan forcing mechanism. Damit dies vermieden wird, verwenden Sie den ALTER <object>-Prozess, um die Definition des enthaltenen Objekts nach Möglichkeit zu ändern.To avoid this situation, use the ALTER <object> process to change a containing object definition whenever it's possible.

Regelmäßiges Überprüfen des Status der erzwungenen PläneCheck the status of forced plans regularly

Die Planerzwingung ist ein nützlicher Mechanismus zur Behandlung von Leistungsproblemen für kritische Abfragen, um sie besser vorhersagbar zu machen.Plan forcing is a convenient mechanism to fix performance for the critical queries and make them more predictable. Wie bei Planhinweisen und Planhinweislisten ist das Erzwingen eines Plans keine Garantie dafür, dass er in späteren Ausführungen verwendet wird.As with plan hints and plan guides, forcing a plan isn't a guarantee that it will be used in future executions. Wenn das Datenbankschema sich derart ändert, dass Objekte, auf die der Ausführungsplan verweist, geändert oder gelöscht werden, wird das Erzwingen eines Plans in der Regel scheitern.Typically, when database schema changes in a way that objects referenced by the execution plan are altered or dropped, plan forcing starts failing. In diesem Fall greift SQL ServerSQL Server auf eine Neukompilierung der Abfrage zurück, während die tatsächliche Ursache für den Fehler beim Erzwingen in sys.query_store_plan ersichtlich ist.In that case, SQL ServerSQL Server falls back to query recompilation while the actual forcing failure reason is surfaced in sys.query_store_plan. Die folgende Abfrage gibt Informationen zu erzwungenen Plänen zurück:The following query returns information about forced plans:

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Eine vollständige Liste der Gründe finden Sie unter sys.query_store_plan.For a full list of reasons, see sys.query_store_plan. Sie können auch das XEvent query_store_plan_forcing_failed verwenden, um Fehler bei der Planerzwingung nachzuverfolgen und zu beheben.You can also use the query_store_plan_forcing_failed XEvent to track and troubleshoot plan forcing failures.

Vermeiden der Umbenennung von Datenbanken bei Abfragen mit erzwungenen PlänenAvoid renaming databases for queries with forced plans

Ausführungspläne verweisen auf Objekte mithilfe von dreiteiligen Namen wie database.schema.object.Execution plans reference objects by using three-part names like database.schema.object.

Wenn Sie eine Datenbank umbenennen, wird das Erzwingen eines Plans fehlschlagen, wodurch bei allen nachfolgenden Abfrageausführungen eine Neukompilierung durchgeführt wird.If you rename a database, plan forcing fails, which causes recompilation in all subsequent query executions.

Verwenden von Ablaufverfolgungsflags für unternehmenskritische ServerUse trace flags on mission-critical servers

Die globalen Ablaufverfolgungsflags 7745 und 7752 können verwendet werden, um die Verfügbarkeit von Datenbanken mithilfe des Abfragespeichers zu verbessern.The global trace flags 7745 and 7752 can be used to improve availability of databases by using Query Store. Weitere Informationen finden Sie unter Ablaufverfolgungsflags.For more information, see Trace flags.

  • Das Ablaufverfolgungsflag 7745 verhindert, dass der Abfragespeicher standardmäßig Daten auf den Datenträger schreibt, bevor SQL ServerSQL Server beendet werden kann.Trace flag 7745 prevents the default behavior where Query Store writes data to disk before SQL ServerSQL Server can be shut down. Dies bedeutet, dass Abfragespeicherdaten, die erfasst, aber noch nicht dauerhaft auf einem Datenträger gespeichert wurden, bis zu dem mit DATA_FLUSH_INTERVAL_SECONDS definierten Zeitfenster verloren gehen.This means that Query Store data that has been collected but not yet persisted to disk will be lost, up to the time window defined with DATA_FLUSH_INTERVAL_SECONDS.
  • Ablaufverfolgungsflag 7752 aktiviert asynchrones Laden von Abfragespeicher.Trace flag 7752 enables asynchronous load of Query Store. Dadurch kann eine Datenbank online geschaltet und können Abfragen ausgeführt werden, bevor der Abfragespeicher vollständig wiederhergestellt wurde.This allows a database to become online and queries to be executed before Query Store has been fully recovered. Beim Standardverhalten erfolgt ein synchrones Laden des Abfragespeichers.The default behavior is to do a synchronous load of Query Store. Das Standardverhalten verhindert, dass Abfragen ausgeführt werden, bevor der Abfragespeicher wiederhergestellt wurde, verhindert aber auch, dass irgendwelche Abfragen in der Datensammlung ignoriert werden.The default behavior prevents queries from executing before Query Store has been recovered but also prevents any queries from being missed in the data collection.

Hinweis

Ab SQL Server 2019 (15.x)SQL Server 2019 (15.x) wird dieses Verhalten durch die Engine gesteuert, und das Ablaufverfolgungsflag 7752 hat keine Auswirkungen.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this behavior is controlled by the engine, and trace flag 7752 has no effect.

Wichtig

Wenn Sie den Abfragespeicher für Erkenntnisse zu Just-In-Time-Arbeitsauslastungen in SQL Server 2016 (13.x)SQL Server 2016 (13.x) verwenden, planen Sie baldmöglichst die Installation der Fixes zur Leistungsskalierbarkeit in KB 4340759 ein.If you're using Query Store for just-in-time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

Weitere InformationenSee also