Verwendungsszenarien für den AbfragespeicherQuery Store Usage Scenarios

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

Der Abfragespeicher kann in einer großen Bandbreite von Szenarien eingesetzt werden, wenn das Nachverfolgen und Sicherstellen einer vorhersagbaren Arbeitsleistung entscheidend ist.Query Store can be used in wide set of scenarios when tracking and ensuring predictable workload performance is critical. Diese Beispiele dienen zur Veranschaulichung:Here are some examples you can consider:

  • Bestimmen und Reparieren von Abfragen mit PlanauswahlregressionPinpoint and fix queries with plan choice regressions
  • Ermitteln und Optimieren von Abfragen mit dem höchsten RessourcenverbrauchIdentify and tune top resource consuming queries
  • A/B-TestsA/B testing
  • Aufrechterhalten einer stabilen Leistung während des Upgrades auf das neuere SQL ServerSQL ServerKeep performance stability during the upgrade to newer SQL ServerSQL Server
  • Erkennen und Verbessern von Ad-hoc-WorkloadsIdentify and improve ad hoc workloads

Bestimmen und Reparieren von Abfragen mit PlanauswahlregressionPinpoint and fix queries with plan choice regressions

Im Rahmen seiner normalen Abfrageausführung kann der Abfrageoptimierer entscheiden, einen anderen Plan zu wählen, da sich wichtige Eingangsparameter geändert haben: die Datenkardinalität hat sich geändert, es wurden Indizes erstellt, geändert oder gelöscht, Statistikinformationen wurden aktualisiert usw. Meistens funktioniert der neue Plan besser oder in etwa gleich gut wie der zuvor verwendete.During its regular query execution, Query Optimizer may decide to choose a different plan because important inputs became different: data cardinality has changed, indexes have been created, altered, or dropped, statistics have been updated, etc. For the most part, the new plan is better, or about the same than the plan used previously. Es gibt jedoch Fälle, in denen der neue Plan deutlich schlechter funktioniert – diese Situation wird als Planauswahl-Änderungsregression bezeichnet.However, there are cases when new plan is significantly worse - this situation is referred to as plan choice change regression. Vor der Einführung des Abfragespeichers war das ein schwer zu erkennendes und behebendes Problem, da SQL ServerSQL Server keinen integrierten Datenspeicher bereitstellte, in dem Benutzer nach Ausführungsplänen suchen konnten, die im Lauf der Zeit verwendet worden waren.Prior to Query Store, it was an issue difficult to identify and fix as SQL ServerSQL Server did not provide built-in data store, for users to look at for execution plans that were used over time.

Mit dem Abfragespeicher lassen sich diese Aufgaben schnell lösen:With the Query Store, you can quickly:

  • Identifizieren aller Abfragen, deren Ausführungsmetrik im interessierenden Zeitraum (letzte Stunde, letzter Tag, letzte Woche usw.) heruntergestuft wurde.Identify all queries which execution metrics have been degraded, in the period of time of interest (last hour, day, week, etc.). Verwendung von Zurückgestellten Abfragen in SQL Server Management StudioSQL Server Management Studio zum Beschleunigen der Analyse.Use Regressed Queries in SQL Server Management StudioSQL Server Management Studio to speed up your analysis.

  • Unter den zurückgestellten Abfragen lassen sich leicht diejenigen identifizieren, die mehrere Pläne hatten und deren Leistung sich aufgrund schlechter Planauswahl verschlechtert hat.Among the regressed queries, it's easy to find those that had multiple plans and which degraded because of the bad plan choice. Verwenden Sie den Bereich Planzusammenfassung in Zurückgestellte Abfragen , um alle Pläne für eine zurückgestellte Abfrage und ihre Abfrageleistung im zeitlichen Verlauf darzustellen.Use Plan Summary pane in Regressed Queries to visualize all plans for a regressed query and their query performance over time.

  • Setzen Sie die Verwendung des im Verlauf vorhergehenden Plans durch, wenn der sich als besser erwiesen hat.Force the previous plan from the history, if it proved to be better. Verwenden Sie die Schaltfläche Plan erzwingen in Zurückgestellte Abfragen, um die Verwendung des ausgewählten Plans für die Abfrage durchzusetzen.Use Force Plan button in Regressed Queries to force selected plan for the query.

query-store-usage-1query-store-usage-1

Eine detaillierte Beschreibung des Szenarios finden Sie im Blog Query Store: A flight data recorder for your database (Abfragespeicher: Ein Flugdatenschreiber für Ihre Datenbank).For detailed description of the scenario refer to Query Store: A flight data recorder for your database blog.

Ermitteln und Optimieren von Abfragen mit dem höchsten RessourcenverbrauchIdentify and tune top resource consuming queries

Zwar können im Rahmen Ihrer Arbeitsauslastung Tausende Abfragen generiert werden, normalerweise verwendet jedoch nur eine Handvoll den größten Teil der Systemressourcen und erfordert daher Ihre Aufmerksamkeit.Although your workload may generate thousands of queries, typically only a handful of them actually use the most of the system resources and therefore require your attention. Unter den Abfragen mit dem größten Ressourcenverbrauch finden sich üblicherweise zurückgestellte Abfragen oder solche, die mit weiterer Optimierung verbessert werden können.Among top resource consuming queries, you will typically find queries that are either regressed or those that can be improved with additional tuning.

Die Untersuchung lässt sich am einfachsten durch Öffnen von Abfragen mit dem höchsten Ressourcenverbrauch in Management StudioManagement Studiobeginnen.The easiest way to start exploration is to open Top Resource Consuming Queries in Management StudioManagement Studio. Die Benutzeroberfläche ist in drei Bereiche unterteilt: Ein Histogramm, das die Abfragen mit dem höchsten Ressourcenverbrauch darstellt (links), eine Planzusammenfassung für die ausgewählte Abfrage (rechts) und eine visuellen Abfrageplan für den ausgewählten Plan (unten).User interface is separated into three panes: A histogram representing top resource consuming queries (left), a plan summary for selected query (right) and visual query plan for selected plan (bottom). Klicken Sie auf die Schaltfläche Konfigurieren , um die Anzahl der zu analysierenden Abfragen und das untersuchte Zeitintervall zu steuern.Click the Configure button to control how many queries you want to analyze and the time interval of interest. Darüber hinaus können Sie unter verschiedenen Dimensionen des Ressourcenverbrauchs (Dauer, CPU, Arbeitsspeicher, E/A, Anzahl der Ausführungen) und der Baseline (Mittel, Min, Max, Summe, Standardabweichung) wählen.Additionally, you can choose between different resource consumption dimensions (duration, CPU, memory, IO, number of executions) and the baseline (Average, Min, Max, Total, Standard Deviation).

query-store-usage-2query-store-usage-2

Betrachten Sie die Planzusammenfassung auf der rechten Seite, um den Ausführungsverlauf zu analysieren und sich über die verschiedenen Pläne und ihre Laufzeitstatistik zu informieren.Look at the plan summary on the right to analyze the execution history and learn about the different plans and their runtime statistics. Verwenden Sie den unteren Bereich, um die verschiedenen Pläne zu untersuchen oder sie nebeneinander visuell zu vergleichen (mithilfe der Schaltfläche „Vergleichen“).Use the bottom pane to examine the different plans or to compare them visually, rendered side by side (use the Compare button).

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:

  1. Wenn die Abfrage mit mehreren Plänen ausgeführt wurde und der letzte Plan signifikant schlechter ist als der vorherige, können Sie den Planerzwingungsmechanismus verwenden, um sicherzustellen, dass bei zukünftigen Ausführungen von SQL ServerSQL Server immer der optimale Plan verwendet wird.If the query was executed with multiple plans and the last plan is significantly worse than previous plan, you can use the plan forcing mechanism to ensure SQL ServerSQL Server will use the optimal plan for future executions

  2. Überprüfen Sie, ob der Optimierer Hinweise auf fehlende Indizes im XML-Plan gibt.Check if the optimizer is suggesting any missing indexes in XML plan. Wenn das der Fall ist, erstellen Sie den fehlenden Index, und verwenden Sie den Abfragespeicher, um die Abfrageleistung nach erfolgter Indexerstellung zu bewertenIf yes, create the missing index and use the Query Store to evaluate query performance after the index creation

  3. Vergewissern Sie sich, dass die Statistiken für die zugrunde liegenden Tabellen aktuell sind, die von der Abfrage verwendet werden.Make sure that the statistics are up-to-date for the underlying tables used by the query.

  4. Überprüfen Sie, ob die von der Abfrage verwendeten Indizes defragmentiert sind.Make sure that indexes used by the query are defragmented.

  5. Ziehen Sie bei aufwändigen Abfragen eine Neuerstellung in Erwägung.Consider rewriting expensive query. Nutzen Sie beispielsweise die Vorteile der Abfrageparametrisierung, und verringern Sie den Einsatz von dynamischem SQL.For example, take advantages of query parameterization and reduce usage of dynamic SQL. Implementieren Sie nach dem Lesen der Daten die optimale Logik (führen Sie Datenfilterung auf der Datenbankseite statt auf der Anwendungsseite aus).Implement optimal logic when read the data (apply data filtering on database side, not on application side).

A/B-TestsA/B testing

Verwenden Sie den Abfragespeicher, um die Arbeitsleistung vor und nach der beabsichtigten Änderung der Anwendung zu vergleichen.Use Query Store to compare workload performance before and after the application change you plan to introduce. Die folgende Liste enthält eine Reihe von Beispielen, für die der Abfragespeicher eingesetzt werden kann, um den Einfluss der Umgebungs- oder Anwendungsänderung auf die Arbeitsleistung zu beurteilen:The following list contains several examples where you can use Query Store to assess impact of the environment or application change to the workload performance:

  • Einführen einer neuen Anwendungsversion.Rolling out new application version.

  • Hinzufügen neuer Hardware auf dem Server.Adding new hardware to the server.

  • Erstellen von fehlenden Indizes in Tabellen, auf die von aufwendigen Abfragen verwiesen wirdCreating missing indexes on tables referenced by expensive queries.

  • Anwenden einer Filterrichtlinie für Sicherheit auf Zeilenebene.Applying filtering policy for row-level security. Weitere Informationen finden Sie unter Optimieren von Sicherheit auf Zeilenebene mithilfe des Abfragespeichers.For more information, see Optimizing Row Level Security with Query Store.

  • Hinzufügen von temporaler Verwaltung durch das System zu Tabellen, die häufigen Änderungen durch OLTP-Anwendungen unterliegen.Adding temporal system-versioning to tables that are frequently modified by your OLTP applications.

Wenden Sie für jedes dieser Szenarien den folgenden Arbeitsablauf an:In any of these scenarios apply the following workflow:

  1. Führen Sie Ihre Arbeitsauslastung mit dem Abfragespeicher vor der geplanten Änderung aus, um die Basislinie für die Leistung zu erstellen.Run your workload with the Query Store before the planned change to generate performance baseline.

  2. Wenden Sie die Anwendungsänderung zum vorgesehen Zeitpunkt an.Apply application change at the controlled moment in time.

  3. Führen Sie die Arbeitsauslastung danach für einen ausreichend langen Zeitraum aus, um ein Leistungsbild des Systems nach der Änderung zu erstellenContinue running the workload long enough to generate performance image of the system after the change

  4. Vergleichen Sie die Ergebnisse von Nr. 1 und Nr. 3.Compare results from #1 and #3.

    1. Öffnen Sie Datenbank Gesamtverbrauch, um den Einfluss auf die gesamte Datenbank zu bestimmen.Open Overall Database Consumption to determine impact to the entire database.

    2. Öffnen Sie Abfragen mit dem höchsten Ressourcenverbrauch (oder Ihre eigene Analyse mithilfe von Transact-SQLTransact-SQL), um den Einfluss der Änderung auf die wichtigsten Abfragen zu analysieren.Open Top Resource Consuming Queries (or run your own analysis using Transact-SQLTransact-SQL) to analyze impact of the change to the most important queries.

  5. Entscheiden Sie, ob die Änderung beibehalten oder ein Rollback für den Fall ausgeführt werden soll, dass die neue Leistung nicht akzeptabel ist.Decide whether to keep the change or perform roll back in case when new performance is unacceptable.

In der folgenden Abbildung ist die Abfragespeicheranalyse (Schritt 4) im Fall eines fehlenden, nicht erstellten Index dargestellt.The following illustration shows Query Store analysis (step 4) in case of missing index creation. Öffnen Sie den Bereich Abfragen mit dem höchsten Ressourcenverbrauch /Planzusammenfassung, um diese Ansicht für die Abfrage zu erstellen, auf die sich die Indexerstellung auswirken soll:Open Top Resource Consuming Queries / Plan summary pane to get this view for the query that should be impacted by the index creation:

query-store-usage-3query-store-usage-3

Darüber hinaus können Sie Pläne vor und nach der Indexerstellung vergleichen, indem Sie sie nebeneinander anzeigen.Additionally, you can compare plans before and after index creation by rendering them side by side. (Symbolleistenoption „Pläne für die ausgewählte Abfrage in einem separaten Fenster vergleichen“, die auf der Symbolleiste mit einem roten Quadrat gekennzeichnet ist.)("Compare the plans for the selected query in a separate window" toolbar option, which is marked with red square on the toolbar.)

query-store-usage-4query-store-usage-4

Der Plan (plan_id = 1, oben) enthält vor der Indexerstellung einen Hinweis auf einen fehlenden Index, und Sie können durch die Untersuchung bestätigen, dass „Clustered Index Scan“ der Operator mit dem höchsten Ressourcenverbrauch in der Abfrage war (rotes Rechteck).Plan before index creation (plan_id = 1, above) has missing index hint and you can inspect that Clustered Index Scan was the most expensive operator in the query (red rectangle).

Der Plan verwendet nach der Erstellung des fehlenden Index (plan_id = 15, unten ) jetzt „Index Seek (Nonclustered)“, wodurch sich der Gesamtaufwand der Abfrage verringert und die Leistung verbessert (grünes Rechteck).Plan after missing index creation (plan_id = 15, below) now has Index Seek (Nonclustered) which reduces the overall cost of the query and improves it performance (green rectangle).

Auf der Grundlage der Analyse ist wohl wahrscheinlich, dass Sie den Index beibehalten möchten, da sich die Abfrageleistung verbessert hat.Based on analysis you would likely keep the index as query performance has been improved.

Aufrechterhalten einer stabilen Leistung während des Upgrades auf das neuere SQL ServerSQL ServerKeep performance stability during the upgrade to newer SQL ServerSQL Server

Vor SQL Server 2014 (12.x)SQL Server 2014 (12.x)waren Benutzer dem Risiko einer nachlassenden Leistung während des Upgrades auf die neueste Plattformversion ausgesetzt.Prior to SQL Server 2014 (12.x)SQL Server 2014 (12.x), users were exposed to the risk of performance regression during the upgrade to the latest platform version. Der Grund liegt darin, dass die neueste Version des Abfrageoptimierers sofort aktiviert wurde, sobald neue Teile installiert wurden.The reason for that was the fact that latest version of Query Optimizer became active immediately once new bits are installed.

Seit SQL Server 2014 (12.x)SQL Server 2014 (12.x) sind alle Änderungen des Abfrageoptimierers an den neuesten Datenbank-Kompatibilitätsgrad gebunden, sodass Pläne nicht sofort im Moment des Upgrades geändert werden, sondern erst, wenn ein Benutzer COMPATIBILITY_LEVEL auf die neuste Version aktualisiert.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) all Query Optimizer changes are tied to the latest database compatibility level, so plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL to the latest one. Diese Möglichkeit gibt Ihnen in Kombination mit dem Abfragespeicher ein großes Maß an Kontrolle über die Abfrageleistung im Upgradeprozess.This capability, in combination with Query Store gives you a great level of control over the query performance in the upgrade process. Der empfohlene Upgradeworkflow ist in der folgenden Abbildung dargestellt:Recommended upgrade workflow is shown in the following picture:

query-store-usage-5query-store-usage-5

  1. Upgrade von SQL ServerSQL Server, ohne den Datenbankkompatibilitätsgrad zu ändernUpgrade SQL ServerSQL Server without changing the database compatibility level. Dadurch werden zwar nicht die neuesten Änderungen des Abfrageoptimierers bereitgestellt, aber es sind neuere SQL ServerSQL Server-Funktionen verfügbar, z.B. der Abfragespeicher.It doesn't expose the latest Query Optimizer changes but still provides newer SQL ServerSQL Server features including Query Store.

  2. Aktivieren des AbfragespeichersEnable Query Store. Weitere Informationen zu diesem Thema finden Sie unter Keep Query Store adjusted to your workload (Dauerhafte Abfragespeicheranpassung an Ihre Arbeitsauslastung).For more information on this topic, see Keep Query Store adjusted to your workload.

  3. Erlauben Sie es dem Abfragespeicher, Abfragen und Pläne abzufangen, und legen Sie eine Baseline der Leistung mit dem Kompatibilitätsgrad der Quelle oder der vorherigen Datenbank fest.Allow Query Store to capture queries and plans, and establishes a performance baseline with the source/previous database compatibility level. Bleiben Sie ausreichend lang in diesem Schritt, um alle Pläne zu erfassen und eine stabile Baseline zu erstellen.Stay at this step long enough to capture all plans and get a stable baseline. Dabei kann es sich um die Dauer eines üblichen Geschäftszyklus für eine Produktionsworkload handeln.This can be the duration of a usual business cycle for a production workload.

  4. Umstieg auf den neuesten Datenbankkompatibilitätsgrad: Machen Sie die neuesten Änderungen des Abfrageoptimierers für Ihre Arbeitsauslastung verfügbar, und lassen Sie ihn der Möglichkeit nach neue Pläne erstellen.Move to latest database compatibility level: get your workload exposed to the latest Query Optimizer changes and let it potentially create new plans.

  5. Verwenden Sie den Abfragespeicher für die Analyse und Reparaturen mithilfe zurückgestellter Abfragen: Im Allgemeinen sollten die neuen Änderungen des Abfrageoptimierers bessere Pläne erzeugen.Use Query Store for analysis and regression fixes: for the most part, the new Query Optimizer changes should produce better plans. Jedoch verfügen Sie in Form des Abfragespeichers über eine einfache Möglichkeit, Planauswahlregressionen durchzuführen und falsche Entscheidungen mithilfe des Mechanismus zum Durchsetzen von Plänen zu korrigieren.However, Query Store will provide an easy way to identify plan choice regressions and fix them using a plan forcing mechanism. Ab SQL Server 2017 (14.x)SQL Server 2017 (14.x) wird bei Verwendung der Automatischen Plankorrektur dieser Schritt automatisch durchgeführt.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), when using the Automatic Plan Correction feature, this step becomes automatic.

    a.a. Erzwingen Sie für alle Fälle, in denen Regressionen auftreten, den zuvor bekannten geeigneten Plan im Abfragespeicher.For cases where there are regressions, force the previously known good plan in the Query Store.

    b.b. Falls Abfragepläne nicht erzwungen werden können oder die Leistung weiterhin unzureichend ist, ziehen Sie in Betracht, die vorherige Einstellung des Datenbank-Kompatibilitätsgrads wiederherzustellen und sich anschließend an den Microsoft-Kundensupport zu wenden.If there are query plans that fail to force, or if performance is still insufficient, consider reverting the database compatibility level to the prior setting, and then engaging Microsoft Customer Support.

Tipp

Verwenden Sie den SQL Server Management StudioSQL Server Management Studio-Task Datenbankupgrade, um ein Upgrade für den Datenbank-Kompatibilitätsgrad der Datenbank durchzuführen.Use SQL Server Management StudioSQL Server Management Studio Upgrade Database task to upgrade the database compatibility level of the database. Ausführliche Informationen finden Sie unter Upgraden von Datenbanken mit dem Abfrageoptimierungs-Assistenten.See Upgrading Databases by using the Query Tuning Assistant for details.

Erkennen und Verbessern von Ad-hoc-WorkloadsIdentify and improve ad hoc workloads

Einige Workloads weisen keine besonders häufig ausgeführten Abfragen auf, die Sie optimieren können, um die Gesamtleistung einer Anwendung zu verbessern.Some workloads do not have dominant queries that you can tune to improve overall application performance. Diese Workloads zeichnen sich normalerweise durch eine relativ große Anzahl verschiedener Abfragen aus, von denen jede einen Teil der Systemressourcen beansprucht.Those workloads are typically characterized with relatively large number of different queries each of them consuming portion of system resources. Aufgrund ihrer Einzigartigkeit werden solche Abfragen nur sehr selten ausgeführt (normalerweise nur einmal, daher die Bezeichnung „ad-hoc“), daher ist ihr Ressourcenverbrauch zur Laufzeit nicht kritisch.Being unique, those queries are executed very rarely (usually only once, thus name ad hoc), so their runtime consumption is not critical. Da andererseits die Anwendung unterm Strich ständig neue Abfragen generiert, wird ein erheblicher Teil der Systemressourcen für die Kompilierung von Abfragen aufgewendet, was nicht optimal ist.On the other hand, given that application is generating net new queries all the time, significant portion of system resources is spent on query compilation, which is not optimal. Das ist auch für den Abfragespeicher keine ideale Situation, da die große Anzahl an Abfragen und Plänen den vorgesehenen Speicherplatz schnell erschöpft und der Abfragespeicher so sehr bald in den schreibgeschützten Modus versetzt werden muss.This is not ideal situation for Query Store either given that large number of queries and plans flood the space you have reserved which means that Query Store will likely end up in the read-only mode very quickly. Wenn Sie die Richtlinie zur größenbasierten Bereinigung (dringend empfohlen , um den Abfragespeicher stets betriebsbereit zu halten) aktiviert haben, bereinigen Hintergrundprozesse während des größten Teils der Zeit die Strukturen des Abfragespeichers, was ebenfalls in erheblichem Maß Systemressourcen verbraucht.If you activated Size Based Cleanup Policy (highly recommended to keep Query Store always up and running), then background process will be cleaning Query Store structures most of the time also taking significant system resources.

Die Ansicht Abfragen mit dem höchstem Ressourcenverbrauch gibt Ihnen einen ersten Hinweis auf die Ad-hoc-Natur Ihrer Arbeitsauslastung:Top Resource Consuming Queries view gives you first indication of the ad hoc nature of your workload:

query-store-usage-6query-store-usage-6

Verwenden Sie die Metrik Ausführungsanzahl , um zu analysieren, ob Ihre Abfragen mit dem höchsten Ressourcenverbrauch Ad-hoc-Abfragen sind (dafür müssen Sie den Abfragespeicher mit QUERY_CAPTURE_MODE = ALLausführen).Use Execution Count metric to analyze whether your top queries are ad hoc (this requires you to run Query Store with QUERY_CAPTURE_MODE = ALL). Im Diagramm oben können Sie sehen, dass 90% der Abfragen mit dem höchstem Ressourcenverbrauch nur einmal ausgeführt werden.From the diagram above, you can see that 90% of your Top Resource Consuming Queries are executed only once.

Alternativ können Sie ein Transact-SQLTransact-SQL-Skript ausführen, um die Gesamtzahl der Abfragetexte, Abfragen und Pläne im System abzurufen und ihre Verschiedenheit durch den Vergleich des query_hash- und plan_hash-Werts zu bestimmen:Alternatively, you can run Transact-SQLTransact-SQL script to get total number of query texts, queries, and plans in the system and determine how different they are by comparing the query_hash and plan_hash:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Dies ist ein potenzielles Ergebnis, das Sie bei Arbeitsauslastungen mit Ad-hoc-Abfragen erhalten können:This is one potential result you can get in case of workload with ad hoc queries:

query-store-usage-7query-store-usage-7

Das Abfrageergebnis zeigt, dass trotz der großen Anzahl an Abfragen und Plänen im Abfragespeicher sich deren query_hash und query_plan_hash tatsächlich nicht unterscheiden.Query result shows that despite the large number of queries and plans in the Query Store their query_hash and query_plan_hash are actually not different. Ein Verhältnis zwischen eindeutigen Abfragetexten und eindeutigen Abfragehashes, das erheblich größer als 1 ist, ist ein Hinweis, dass die Arbeitsauslastung einen geeigneten Kandidaten für Parametrisierung darstellt, da der einzige Unterschied zwischen den Abfragen eine literale Konstante (Parameter) ist, die als Teil des Abfragetexts übergeben wird.A ratio between unique query texts and unique query hashes, which is much bigger than 1, is an indication that workload is a good candidate for parameterization, as the only difference between the queries is literal constant (parameter) provided as part of the query text.

Normalerweise tritt diese Situation ein, wenn Ihre Anwendung Abfragen erstellt (statt gespeicherte Prozeduren oder parametrisierte Abfragen aufzurufen) oder auf objektrelationalen Zuordnungsframeworks basiert, die standardmäßig Abfragen generieren.Usually, this situation happens if your application generates queries (instead of invoking stored procedures or parameterized queries) or if it relies on object-relational mapping frameworks that generate queries by default.

Wenn der Anwendungscode in Ihre Zuständigkeit fällt, können Sie eine Neuerstellung der Datenzugriffsschicht in Erwägung ziehen, um gespeicherte Prozeduren oder parametrisierte Abfragen zu verwenden.If you are in control of the application code, you may consider rewriting of the data access layer to utilize stored procedures or parameterized queries. Diese Situation lässt sich aber auch ohne Änderung der Anwendung erheblich verbessern, indem die Abfrageparametrisierung für die gesamte Datenbank (alle Abfragen) oder für die individuellen Abfragevorlagen mit dem gleichen Abfragehash durchgesetzt wird.However, this situation can be also significantly improved without application changes by forcing query parameterization for the entire database (all queries) or for the individual query templates with the same query_hash.

Der Ansatz mit einzelnen Abfragevorlagen erfordert die Erstellung von Planhinweislisten:Approach with individual query templates requires plan guide creation:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Die Lösung mit Planhinweislisten ist genauer, erfordert aber mehr Arbeit.Solution with plan guides is more precise but it requires more work.

Wenn alle Ihre Abfragen (oder eine Mehrheit davon) Kandidaten für automatische Parametrisierung sind, stellt das Ändern von FORCED PARAMETERIZATION für die gesamte Datenbank möglicherweise die bessere Option dar:If all your queries (or the majority of them) are candidates for auto-parameterization, then changing FORCED PARAMETERIZATION for the entire database may be a better option:

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

Hinweis

Weitere Informationen zu diesem Thema finden Sie unter Richtlinien für die Verwendung der erzwungenen Parametrisierung.For more information on this topic, see Guidelines for Using Forced Parameterization.

Nach dem Ausführen eines dieser Schritte zeichnet Abfragen mit höchstem Ressourcenverbrauch ein anderes Bild Ihrer Arbeitsauslastung.After you apply any of these steps, Top Resource Consuming Queries will show you different picture of your workload.

query-store-usage-8query-store-usage-8

In manchen Fällen generiert Ihre Anwendung möglicherweise viele verschiedene Abfragen, die keine geeigneten Kandidaten für automatische Parametrisierung darstellen.In some cases, your application may generate lots of different queries which are not good candidates for auto-parameterization. In diesem Fall findet sich eine große Anzahl Abfragen im System, das Verhältnis zwischen eindeutigen Abfragen und eindeutigem Abfragehash (query_hash) liegt aber wahrscheinlich nahe bei 1.In that case, you see large number of queries in the system but the ratio between unique queries and unique query_hash is likely close to 1.

In diesem Fall kann es sinnvoll sein, die Serveroption Für Ad-hoc-Arbeitsauslastungen optimieren zu aktivieren, um die Verschwendung von Cachespeicher für Abfragen zu vermeiden, die wahrscheinlich nicht erneut ausgeführt werden.In that case, you may want to enable the Optimize for Ad hoc Workloads server option to prevent wasting cache memory on queries that won't likely be executed again. Um die Erfassung solcher Abfragen im Abfragespeicher zu verhindern, legen Sie QUERY_CAPTURE_MODE auf AUTOfest.To prevent capture of those queries in the Query Store, set QUERY_CAPTURE_MODE to AUTO.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Weitere InformationenSee Also

Überwachen der Leistung mit dem Abfragespeicher Monitoring Performance By Using the Query Store
Best Practices für den Abfragespeicher Best Practice with the Query Store
Upgraden von Datenbanken mit dem Abfrageoptimierungs-AssistentenUpgrading Databases by using the Query Tuning Assistant