Feedbackfeatures für die Abfrageverarbeitung

Dieser Artikel enthält ausführliche Beschreibungen verschiedener IQP-Feedbackfeatures (Intelligent Query Processing). Die Feedbackfeatures für die Abfrageverarbeitung sind Teil der Intelligenten Abfrageverarbeitungsfamilie. Feedback zur Abfrageverarbeitung ist ein Prozess, bei dem der Abfrageprozessor in SQL Server, Azure SQL Datenbank und Azure SQL Managed Instance Verlaufsdaten zur Ausführung einer Abfrage verwendet, um zu entscheiden, ob die Abfrage möglicherweise Hilfe von einer oder mehreren Änderungen an der Art und Weise der Kompilierung und Ausführung erhält. Die Leistungsdaten werden im Abfragespeicher mit verschiedenen Vorschlägen zur Verbesserung der Abfrageausführung gesammelt. Bei erfolgreicher Ausführung speichern wir diese Änderungen für die zukünftige Verwendung auf dem Datenträger im Arbeitsspeicher und/oder im Abfragespeicher. Wenn die Vorschläge nicht zu einer ausreichenden Verbesserung führen, werden sie verworfen, und die Abfrage wird ohne dieses Feedback weiterhin ausgeführt.

Die in diesem Artikel beschriebenen Feedbackfeatures sind:

Feedback zur Speicherzuweisung

Manchmal wird eine Abfrage mit einer zu großen oder zu kleinen Speicherzuweisung ausgeführt. Wenn die Speicherzuweisung zu groß ist, wird die Parallelität auf dem Server gehemmt. Wenn es zu klein ist, können wir auf den Datenträger verschütten, was ein kostspieliger Vorgang ist. Feedback zur Speicherzuweisung versucht, sich an den Arbeitsspeicherbedarf einer früheren Ausführung (ab SQL Server 2022 (16.x) mehrerer Ausführungen) einer Abfrage zu erinnern und die der Abfrage gewährte Gewährung entsprechend anzupassen. Dieses Feature wurde in drei Wellen veröffentlicht. Feedback zur Speicherzuweisung im Batchmodus, gefolgt von Feedback zur Speicherzuweisung im Zeilenmodus, und in SQL Server 2022 (16.x) führen wir Feedback zur Speicherzuweisung auf datenträgerbasierter Persistenz mithilfe des Abfragespeicher und eines verbesserten Algorithmus ein, der als Perzentilzuweisung bezeichnet wird.

Feedback zur Speicherzuweisung im Batchmodus

Anwendungsbereich: SQL Server (ab SQL Server 2017 (14.x)), Azure SQL-Datenbank

Der Ausführungsplan einer Abfrage enthält den für die Ausführung erforderlichen Mindestspeicher und die ideale Größe der Speicherzuweisung, damit alle Zeilen in den Arbeitsspeicher passen. Es gibt Leistungseinbußen, wenn die Speicherzuweisungsgrößen falsch sind. Zu große Zuweisungen führen zu verschwendetem Speicherplatz und geringerer Parallelität. Nicht ausreichende Speicherzuweisungen führen zu teuren Überläufen auf den Datenträger. Für wiederholte Workloads berechnet das Feedback zur Speicherzuweisung im Batchmodus den tatsächlich erforderlichen Speicherplatz für eine Abfrage neu und aktualisiert anschließend den Zuweisungswert des zwischengespeicherten Plans. Wenn eine identische Abfrageanweisung ausgeführt wird, verwendet die Abfrage die angepasste Speicherzuweisungsgröße. Dadurch werden zu hohe Speicherzuweisungen verringert, die die Parallelität beeinträchtigen, und Probleme bei zu gering geschätzten Speicherzuweisungen behoben, die teuren Überläufe auf den Datenträger verursachen.

Der folgende Graph veranschaulicht ein Beispiel für den Gebrauch des Feedbacks zur adaptiven Speicherzuweisung im Batchmodus. Die erste Ausführung der Abfrage hat aufgrund von einer hoher Zahl von Überlaufen 88 Sekunden in Anspruch genommen:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Ein Diagramm von gewährten und verschütteten MBs des Arbeitsspeichers, der auf hohe Auslagerungen hinweist.

Wenn das Feedback zur Speicherzuweisung aktiviert ist, dauert die zweite Ausführung 1 Sekunde (vorher 88 Sekunden), Überläufe treten nicht mehr auf, und die Zuweisung ist höher:

Ein Diagramm mit gewährten und verschütteten MBs des Arbeitsspeichers, der keine Verschüttungen anzeigt.

Größenanpassung des Feedbacks zur Speicherzuweisung im Batchmodus

Wenn bei einer Bedingung mit einer zu großen Speicherzuweisung der zugewiesene Speicher mehr als doppelt so groß ist als der tatsächlich verwendete Speicher, berechnet das Feedback zur Speicherzuweisung diese neu und aktualisiert den zwischengespeicherten Plan. Pläne mit Speicherzuweisungen unter 1 MB werden bei Überschreitungen nicht neu berechnet.

Bei einer Bedingung für eine unzureichende Speicherzuweisung, die zu einem Überlauf auf den Datenträger für Batchmodusoperatoren führt, löst das Feedback zur Speicherzuweisung eine Neuberechnung der Speicherzuweisung aus. Spillereignisse werden an Das Feedback zur Speicherzuweisung gemeldet und können über das spilling_report_to_memory_grant_feedback erweiterte Ereignis angezeigt werden. Dieses Ereignis gibt die Knoten-ID aus dem Plan und der Größe der übergelaufenen Daten dieses Knotens zurück.

Die angepasste Speicherzuweisung wird im tatsächlichen Plan (nach der Ausführung) über die GrantedMemory -Eigenschaft angezeigt.

Diese Eigenschaft wird im Stammoperator des grafischen Showplans oder in der Showplan-XML-Ausgabe angezeigt:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Damit Ihre Workloads automatisch für diese Verbesserung in Frage kommen, aktivieren Sie den Kompatibilitätsgrad 140 für die Datenbank.

Beispiel:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Feedback zur Speicherzuweisung und parameterempfindliche Szenarios

Verschiedene Parameterwerte können auch unterschiedliche Abfragepläne erfordern, um optimale Ergebnisse zu bieten. Diese Art von Abfrage wird als „parameterempfindlich“ bezeichnet.

Bei parameterempfindlichen Plänen deaktiviert sich das Feedback zur Speicherzuweisung in Abfragen selbst, wenn es nicht stabile Speicheranforderungen aufweist. Das Feature zur Speicherzuweisung ist nach mehreren wiederholten Ausführungen der Abfrage deaktiviert. Dies kann durch überwachung des memory_grant_feedback_loop_disabled erweiterten Ereignisses beobachtet werden. Diese Bedingung wird durch den Persistenz- und Perzentilmodus für das Feedback zur Speicherzuweisung behoben, das in SQL Server 2022 (16.x) eingeführt wurde. Die Persistenzfunktion des Speicherzuweisungsfeedbacks erfordert, dass die Abfragespeicher in der Datenbank aktiviert und auf den Schreibzugriffsmodus festgelegt wird.

Weitere Informationen zum Sniffing von Parametern und zur Parametersensitivität finden Sie im Leitfaden zur Architektur der Abfrageverarbeitung.

Caching des Feedbacks zur Speicherzuweisung

Das Feedback kann für eine einzelne Ausführung in einem zwischengespeicherten Plan gespeichert werden. Es sind jedoch die aufeinanderfolgenden Ausführungen dieser Anweisung, die von den Anpassungen der Speicherzuweisungsfeedbacks profitieren. Diese Funktion wird bei wiederholten Ausführungen von Anweisungen angewendet. Das Feedback zur Speicherzuweisung ändert nur den zwischengespeicherten Plan. Vor SQL Server 2022 (16.x) wurden Änderungen nicht im Abfragespeicher erfasst.

Feedback wird nicht beibehalten, wenn der Plan aus dem Cache entfernt wird. Feedback geht auch verloren, wenn ein Failover vorliegt. Eine Anweisung mit OPTION (RECOMPILE) erstellt einen neuen Plan und speichert ihn nicht zwischen. Da es nicht zwischengespeichert wird, wird kein Feedback zur Speicherzuweisung generiert und nicht für diese Kompilierung und Ausführung gespeichert. Wenn jedoch eine entsprechende Anweisung (d. h. mit demselben Abfragehash), die nicht verwendet OPTION (RECOMPILE) wurde, zwischengespeichert und dann erneut ausgeführt wurde, können die zweiten und späteren aufeinanderfolgenden Ausführungen von Feedback zur Speicherzuweisung profitieren.

Nachverfolgen der Feedbackaktivität zur Speicherzuweisung

Sie können Feedbackereignisse für die Speicherzuweisung mithilfe des erweiterten Ereignisses memory_grant_updated_by_feedback nachverfolgen. Dieses Ereignis verfolgt den Verlauf der Ausführungsanzahl, die Zahl von Aktualisierungen des Plans durch das Feedback zur Speicherzuweisung und die optimale zusätzliche Speicherzuweisung vor der Anpassung und nach der Anpassung des zwischengespeicherten Plans durch das Feedback zur Speicherzuweisung.

Feedback zur Speicherzuweisung, Ressourcenkontrolle und Abfragehinweise

Der tatsächlich zugewiesene Speicher berücksichtigt die Abfragespeichereinschränkung, die von der Ressourcenkontrolle oder dem Abfragehinweis bestimmt wird.

Deaktivieren des Feedbacks zur Speicherzuweisung im Batchmodus, ohne den Kompatibilitätsgrad zu ändern

Das Feedback zur Speicherzuweisung kann im Datenbank- oder Anweisungsbereich deaktiviert werden, während der Datenbankkompatibilitätsgrad weiterhin bei 140 und höher bleibt. Führen Sie die folgenden SQL-Anweisungen im Kontext der entsprechenden Datenbank aus, um das Feedback zur Speicherzuweisung im Batchmodus für alle Abfrageausführungen zu deaktivieren:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Ist diese Einstellung aktiviert, wird sie in sys.database_scoped_configurations als aktiviert aufgeführt.

Führen Sie die SQL-Anweisungen im Kontext der entsprechenden Datenbank aus, um das Feedback zur Speicherzuweisung im Batchmodus für alle Abfrageausführungen erneut zu aktivieren:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Sie können das Feedback zur Speicherzuweisung im Batchmodus auch für eine bestimmte Abfrage deaktivieren, indem Sie DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK als USE HINT-Abfragehinweis festlegen. Beispiel:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Ein USE HINT-Abfragehinweis hat Vorrang vor einer Datenbankbereichskonfigurations- oder Ablaufverfolgungsflagseinstellung.

Feedback zur Speicherzuweisung im Zeilenmodus

Anwendungsbereich: SQL Server (ab SQL Server 2019 (15.x)), Azure SQL-Datenbank

Das Feedback zur Speicherzuweisung im Zeilenmodus erweitert die Feedbackfunktion zur Speicherzuweisung im Batchmodus, indem die Größe der Speicherzuweisung sowohl für Batch- als auch für Zeilenmodusoperatoren angepasst wird.

Aktivieren Sie den Datenbankkompatibilitätsgrad 150 oder höher für die Datenbank, mit der Sie beim Ausführen der Abfrage verbunden sind, um das Feedback zur Speicherzuweisung im Zeilenmodus in Azure SQL Datenbank zu aktivieren.

Beispiel:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Wie bei der Speicherzuweisung im Batchmodus ist auch die Aktivität der Zeilenmodus-Speicherzuweisung über das memory_grant_updated_by_feedback XEvent sichtbar. Außerdem führen wir zwei neue Attribute des Abfrageausführungsplans ein, um einen besseren Einblick in den aktuellen Zustand eines Speicherzuweisungsfeedbackvorgangs für den Zeilen- und Batchmodus zu erhalten.

Das Feedback zur Speicherzuweisung erfordert keine Abfragespeicher. Die in SQL Server 2022 (16.x) eingeführten Persistenzverbesserungen erfordern jedoch, dass der Abfragespeicher für die Datenbank aktiviert ist und sich im Zustand "Schreibzugriff" befindet. Weitere Informationen zur Persistenz finden Sie weiter unten in diesem Artikel unter Perzentil- und Persistenzmodus: Feedback zur Speicherzuweisung .

Die Feedbackaktivität für die Speicherzuweisung im Zeilenmodus ist über das memory_grant_updated_by_feedback erweiterte Ereignis sichtbar.

Ab dem Feedback zur Speicherzuweisung im Zeilenmodus werden zwei neue Abfrageplanattribute für tatsächliche Pläne nach der Ausführung angezeigt: IsMemoryGrantFeedbackAdjusted und LastRequestedMemory, die dem XML-Element des MemoryGrantInfo Abfrageplans hinzugefügt werden.

  • Das LastRequestedMemory Attribut zeigt den gewährten Arbeitsspeicher in Kb (Kb) aus der vorherigen Abfrageausführung an.
  • Mit IsMemoryGrantFeedbackAdjusted dem -Attribut können Sie den Status des Speicherzuweisungsfeedbacks für die Anweisung innerhalb eines tatsächlichen Abfrageausführungsplans überprüfen.

Folgende Werte werden in diesem Attribut angezeigt:

IsMemoryGrantFeedbackAdjusted-Wert BESCHREIBUNG
No: First Execution Das Feedback zur Speicherzuweisung passt den Arbeitsspeicher nicht für die erste Kompilierung und die zugehörige Ausführung an.
No: Accurate Grant Wenn kein Spill auf den Datenträger erfolgt und die Anweisung mindestens 50 % des gewährten Arbeitsspeichers verwendet, wird das Feedback zur Speicherzuweisung nicht ausgelöst.
No: Feedback disabled Wenn das Feedback zur Speicherzuweisung kontinuierlich ausgelöst wird und zwischen Vorgängen zur Arbeitsspeichervergrößerung und Speicherverringerung schwankt, deaktiviert die Datenbank-Engine das Feedback zur Speicherzuweisung für die Anweisung.
Yes: Adjusting Das Feedback zur Speicherzuweisung wurde angewendet und kann für die nächste Ausführung weiter angepasst werden.
Yes: Stable Das Feedback zur Speicherzuweisung wurde angewendet und der zugewiesene Speicher ist jetzt stabil. Das bedeutet: Der für die vorherige Ausführung zuletzt zugewiesene Speicher entspricht dem für die aktuelle Ausführung.

Deaktivieren des Feedbacks zur Speicherzuweisung im Zeilenmodus, ohne den Kompatibilitätsgrad zu ändern

Das Feedback zur Speicherzuweisung im Zeilenmodus kann im Datenbank- oder Anweisungsbereich deaktiviert werden, während der Datenbankkompatibilitätsgrad weiterhin bei 150 und höher bleibt. Führen Sie die SQL-Anweisungen im Kontext der entsprechenden Datenbank aus, um das Erteilen des Zeilenmodusspeichers für alle Abfrageausführungen zu deaktivieren:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Um das Feedback zur Speicherzuweisung im Zeilenmodus für alle Abfrageausführungen, die aus der Datenbank stammen, erneut zu aktivieren, führen Sie die folgende Anweisung im Kontext der betroffenen Datenbank aus:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Sie können das Feedback zur Speicherzuweisung im Zeilenmodus auch für eine bestimmte Abfrage deaktivieren, indem Sie DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK als USE HINT-Abfragehinweis festlegen. Beispiel:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Ein USE HINT-Abfragehinweis hat Vorrang vor einer Datenbankbereichskonfigurations- oder Ablaufverfolgungsflagseinstellung.

Feedback zur Speicherzuweisung im Perzentil- und Persistenzmodus

Gilt für: SQL Server 2022 (16.x) und höher

Dieses Feature wurde in SQL Server 2022 (16.x) eingeführt. Diese Leistungsverbesserung ist jedoch für Abfragen verfügbar, die mit dem Datenbankkompatibilitätsgrad 140 (eingeführt in SQL Server 2017) oder höher oder dem QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n Hinweis 140 und höher ausgeführt werden, und wenn Abfragespeicher für die Datenbank aktiviert ist und sich im Zustand "Schreibzugriff" befindet.

  • Das Feedback zur Perzentilspeicherzuweisung ist in SQL Server 2022 (16.x) standardmäßig aktiviert, hat jedoch keine Auswirkung, wenn Abfragespeicher nicht aktiviert ist und sich im Zustand "Lesezugriff" befindet.
  • Die Persistenz für Speicherzuweisung, CE- und DOP-Feedback ist in SQL Server 2022 (16.x) standardmäßig aktiviert, hat aber keine Auswirkung, wenn Abfragespeicher nicht aktiviert ist und sich im Zustand "Lesezugriff" befindet.
  • Feedback zur Gewährung von perzentilem Arbeitsspeicher ist derzeit nicht in Azure SQL-Datenbank und Azure SQL Managed Instance verfügbar.
  • Die Persistenz ist derzeit in Azure SQL Datenbank und Azure SQL Managed Instance nicht verfügbar.

Es wird empfohlen, eine Leistungsbaseline für Ihre Workload zu verwenden, bevor das Feature für Ihre Datenbank aktiviert wird. Anhand der Baselinenummern können Sie ermitteln, ob Sie den beabsichtigten Nutzen aus dem Feature erhalten.

Das Feedback zur Speicherzuweisung (Memory Grant Feedback, MGF) ist ein vorhandenes Feature, das die Größe des für eine Abfrage zugewiesenen Arbeitsspeichers basierend auf der bisherigen Leistung anpasst. In den Anfangsphasen dieses Projekts wurde die Anpassung der Speicherzuweisung jedoch mit dem Plan im Cache gespeichert. Wenn ein Plan aus dem Cache entfernt wurde, musste der Feedbackprozess erneut beginnen. Dadurch wurde bei den ersten Ausführungen einer Abfrage nach dem Entfernen die Leistung beeinträchtigt. Die neue Lösung besteht darin, die Gewährungsinformationen mit den anderen Abfrageinformationen im Abfragespeicher beizubehalten, sodass die Vorteile zwischen Cacheräumungen bestehen. Persistenz und Perzentil beheben vorhandene Einschränkungen des Feedback zur Speicherzuweisung auf nicht intrusive Weise.

Darüber hinaus wurde bei den Anpassungen der Zuweisungsgröße nur die zuletzt verwendete Zuweisung berücksichtigt. Wenn also eine parametrisierte Abfrage oder Workload bei jeder Ausführung erheblich unterschiedliche Speicherzuweisungsgrößen erfordert, können die neuesten Grantinformationen ungenau sein. Es kann nicht mit den tatsächlichen Anforderungen der ausgeführten Abfrage in Schritt gehen. Das Feedback zur Speicherzuweisung ist in diesem Szenario nicht hilfreich für die Leistung, da wir den Arbeitsspeicher immer basierend auf dem zuletzt verwendeten Gewährungswert anpassen. Die nächste Abbildung zeigt das Verhalten, das bei der Speicherzuweisung ohne Perzentil- und Persistenzmodus möglich ist.

Ein Diagramm des gewährten im Vergleich zum tatsächlich benötigten Speicherverhalten im Feedback zur Speicherzuweisung ohne Perzentile und Persistenzmodus: Feedback zur Speicherzuweisung.

Wie Sie sehen können, führt die Oszillation zwischen den tatsächlich benötigten und gewährten Speichermengen in diesem ungewöhnlichen, aber möglichen Abfrageverhalten zu einer Verschwendung und unzureichendem Arbeitsspeicher, wenn sich die Abfrageausführung selbst in Bezug auf die Arbeitsspeichermenge abwechselt. In diesem Szenario deaktiviert sich das Feedback zur Speicherzuweisung selbst und erkennt, dass es mehr Schaden als Nutzen anrichtet.

Mithilfe einer perzentilbasierten Berechnung über den aktuellen Verlauf der Abfrage können wir anstelle der letzten Ausführung die Werte für die Gewährungsgröße basierend auf dem vergangenen Ausführungsverwendungsverlauf glätten und versuchen, für die Minimierung von Spills zu optimieren. Bei derselben wechselnden Workload würde beispielsweise das folgende Speicherzuweisungsverhalten auftreten:

Ein Diagramm des gewährten im Vergleich zum tatsächlich benötigten Speicherverhalten im Feedback zur Speicherzuweisung mit Perzentil und Persistenzmodus zur Speicherzuweisung.

Der Abfrageoptimierer verwendet ein hohes Perzentil der Anforderungen für die Größenanpassung vergangener Speicherzuweisungen für Die Ausführung des zwischengespeicherten Plans, um die Größen der Speicherzuweisung zu berechnen, wobei die im Abfragespeicher gespeicherten Daten verwendet werden. Die Perzentilanpassung, mit der die Speicherzuweisungsanpassungen durchgeführt werden, basiert auf dem aktuellen Ausführungsverlauf. Im Laufe der Zeit reduziert die gewährte Speicherzuweisung die Verschüttungen und die Verschwendung von Arbeitsspeicher.

Persistenz gilt auch für DOP-Feedback und CE-Feedback, die ebenfalls in diesem Artikel beschrieben werden.

Aktivieren des Feedbacks zur Speicherzuweisung: Persistenz und Perzentil

Verwenden Sie den Datenbankkompatibilitätsgrad 140 oder höher für die Datenbank, mit der Sie beim Ausführen der Abfrage verbunden sind, um die Persistenz und perzentilen Speicherzuteilungsfeedback zu aktivieren. Peristence und perzentiles Feedback sind standardmäßig aktiviert.

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

Der Abfragespeicher muss für jede Datenbank aktiviert werden, in der der Persistenzteil dieses Features verwendet wird.

Perzentil deaktivieren

Führen Sie im Kontext der entsprechenden Datenbank Folgendes aus, um das Perzentil für die Speicherzuweisung für alle Abfrageausführungen zu deaktivieren, die aus der Datenbank stammen:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE = OFF;

Die Standardeinstellung für MEMORY_GRANT_FEEDBACK_PERCENTILE ist OFF.

Deaktivieren der Persistenz

Um die Persistenz für das Feedback zur Speicherzuweisung für alle Abfrageausführungen, die von der Datenbank stammen, zu deaktivieren, gehen Sie folgendermaßen vor.

Führen Sie Folgendes im Kontext der entsprechenden Datenbank aus:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

Durch das Deaktivieren der Persistenz für das Feedback zur Speicherzuweisung wird auch vorhandenes gesammeltes Feedback entfernt.

Die Standardeinstellung für MEMORY_GRANT_FEEDBACK_PERSISTENCE ist ON.

Überlegungen zum Feedback zur Speicherzuweisung

Sie können Ihre aktuellen Einstellungen anzeigen, indem Sie sys.database_scoped_configurations abfragen.

Hinweis

Dass dieses Feature nicht funktioniert, wenn beide BATCH_MODE_MEMORY_GRANT_FEEDBACK und ROW_MODE_MEMORY_GRANT_FEEDBACK auf OFF festgelegt sind.

Da Feedbackdaten jetzt im Abfragespeicher beibehalten werden, steigt die Abfragespeicher Nutzungsanforderungen etwas.

Bei der perzentilbasierten Speicherzuweisung wird vorrangig auf die Verringerung von Überläufen geachtet. Da sie nicht mehr nur auf der letzten Ausführung, sondern auf der Beobachtung mehrerer vorheriger Ausführungen basiert, kann sich dadurch die Speichernutzung für schwankende Arbeitsauslastungen mit einer breiten Varianz in den Speicherzuweisungsanforderungen zwischen den Ausführungen erhöhen.

Ab SQL Server 2022 (16.x) ist bei aktivierter Abfragespeicher für sekundäre Replikate das Feedback zur Speicherzuweisung replikatfähig für sekundäre Replikate in Verfügbarkeitsgruppen. Feedback zur Speicherzuweisung kann Feedback auf ein primäres Replikat und ein sekundäres Replikat unterschiedlich anwenden. Das Feedback zur Speicherzuweisung wird jedoch nicht auf sekundären Replikaten beibehalten, und beim Failover wird das Feedback zur Speicherzuweisung des alten primären Replikats auf das neue primäre Replikat angewendet. Jedes Feedback, das auf das sekundäre Replikat angewendet wird, wenn es zum primären Replikat wird, geht verloren. Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.

Feedback zum Grad der Parallelität

Gilt für: SQL Server 2022 (16.x) und höher

SQL Server 2022 (16.x) wurde ein neues Feature namens Grad an Parallelität (DOP) eingeführt, um die Abfrageleistung zu verbessern, indem Parallelitätsineffizienzen für wiederholte Abfragen basierend auf verstrichener Zeit und Wartezeiten identifiziert werden. DOP-Feedback ist Teil der intelligenten Abfrageverarbeitungs-Feature-Familie und richtet sich an die suboptimale Verwendung von Parallelismus bei wiederholten Abfragen. Dieses Szenario trägt zur Optimierung der Ressourcennutzung und der Verbesserung der Skalierbarkeit von Workloads bei, wenn übermäßige Parallelität zu Leistungsproblemen führen kann. Anstatt eine allumfassende Standardeinstellung oder manuelle Anpassungen für jede Abfrage zu verursachen, passt DOP-Feedback DOP selbst an, um die oben beschriebenen Probleme zu vermeiden.

Anstatt die Mühe einer allumfassenden Standardeinstellung oder manuellen Anpassungen für jede Abfrage zu verursachen, passt DOP-Feedback DOP selbst an, um übermäßige Parallelität zu vermeiden. Wenn die Parallelitätsnutzung als ineffizient eingestuft wird, senkt DOP-Feedback den DOP für die nächste Ausführung der Abfrage, unabhängig von der konfigurierten DOP, und überprüft, ob es hilfreich ist.

Parallelismus eignet sich häufig für Berichte und analytische Abfragen oder Abfragen, in denen ansonsten große Datenmengen verarbeitet werden. Umgekehrt können in OLTP-zentrierten Abfragen, die parallel ausgeführt werden, Leistungsprobleme auftreten, wenn die Zeit, die auf die Koordination aller Bedrohungen verwendet wird, die Vorteile der Verwendung eines parallelen Plans übersteigt. Weitere Informationen finden Sie unter Parallele Planausführung.

  • Um DOP-Feedback zu aktivieren, aktivieren Sie die DOP_FEEDBACKDatenbankbereichskonfiguration in einer Datenbank.

  • Die Abfragespeicher muss für jede Datenbank, in der DOP-Feedback verwendet wird, und im Status „Lese-Schreibzugriff“ aktiviert sein. Feedback wird in der sys.query_store_plan_feedback Katalogansicht beibehalten, wenn wir einen stabilen Wert an Parallelitätsfeedback erreichen.

  • DOP-Feedback ist für Abfragen verfügbar, die im Datenbankkompatibilitätsgrad 160 (eingeführt mit SQL Server 2022 (16.x)) oder höher ausgeführt werden.

  • Nur verifiziertes Feedback wird beibehalten. Wenn die angepasste DOP zu einer Leistungsregression führt, geht DOP-Feedback zurück zur letzten bekannten guten DOP. In diesem Zusammenhang gilt auch eine abgebrochene Abfrage eines Benutzers als Regression. Das DOP-Feedback kompiliert Pläne nicht neu.

  • Stabiles Feedback wird bei der Neukompilierung des Plans wiederholt und kann nach oben oder unten, aber nie über die MAXDOP-Einstellung (einschließlich eines MAXDOP-Hinweises) neu justiert werden.

  • Um DOP-Feedback auf Datenbankebene zu deaktivieren, verwenden Sie die ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFFDatenbankbereichskonfiguration.

  • Um das DOP-Feedback auf Abfrageebene zu deaktivieren, verwenden Sie den Abfragehinweis DISABLE_DOP_FEEDBACK.

  • Ab SQL Server 2022 (16.x) ist das DOP-Feedback auch für sekundäre Replikate in Verfügbarkeitsgruppen repliziert, wenn Abfragespeicher für sekundäre Replikate aktiviert ist. DOP-Feedback kann Feedback auf ein primäres Replikat und ein sekundäres Replikat unterschiedlich anwenden. DoP-Feedback wird jedoch nicht auf sekundären Replikaten beibehalten, und beim Failover wird das DOP-Feedback des alten primären Replikats nicht auf das neue primäre Replikat angewendet. Beim Failover geht feedback, das auf primäre oder sekundäre Replikate angewendet wird, verloren. Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.

DOP-Feedbackimplementierung

DOP-Feedback identifiziert Parallelitätsineffizienzen für wiederholte Abfragen basierend auf verstrichener Zeit und Wartezeiten. Wenn die Parallelitätsnutzung als ineffizient eingestuft wird, wird durch DOP-Feedback der DOP für die nächste Ausführung der Abfrage von der konfigurierten DOP abgesenkt, und es wird überprüft, ob dies hilfreich ist.

Zur Bewertung der Abfrageberechtigung wird die verstrichene Abfragezeit über einige Ausführungen gemessen. Die insgesamt verstrichene Zeit für jede Abfrage wird angepasst, indem Puffer Latch, Puffer-IO und Netzwerk-IO ignoriert werden, die außerhalb der parallelen Abfrageausführung liegen. Das Ziel des DOP-Feedbackfeatures besteht darin, die Parallelität insgesamt zu erhöhen und Wartezeiten erheblich zu reduzieren, auch wenn dadurch die verstrichene Abfragezeit geringfügig erhöht wird.

Nur verifiziertes Feedback wird beibehalten. Wenn die angepasste DOP zu einer Leistungsregression führt, geht DOP-Feedback zurück zur letzten bekannten guten DOP. In diesem Zusammenhang gilt auch eine abgebrochene Abfrage eines Benutzers als Regression.

Hinweis

DOP-Feedback kompiliert Pläne nicht neu.

DOP-Feedback-Überlegungen

Der Mindest-DOP für jede Abfrage, die mit DOP-Feedback angepasst wurde, ist 2. Serielle Ausführungen sind außerhalb des Bereichs für DOP-Feedback.

Feedbackinformationen können mithilfe der sys.query_store_plan_feedback Katalogansicht nachverfolgt werden.

Wenn für eine Abfrage durch den Abfragespeicher ein Abfrageplan erzwungen wird, kann das DOP-Feedback für diese Abfrage weiter verwendet werden.

Wenn eine Abfrage den MAXDOP-Hinweis verwendet, entweder als hartcodierte Abfragehinweise oder über den Abfragespeicher Hinweismechanismus, und der MAXDOP-Hinweis größer als 2 ist, wird das DOP-Feedback den DOP mit dem Hinweiswert als Obergrenze senken. Weitere Informationen finden Sie unter Hinweise (Transact-SQL) – Abfrage und Abfragespeicherhinweise.

Erweiterte Ereignisse für DOP-Feedback

Für das Feature sind folgende erweiterte Ereignisse (Extended Events, XEs) verfügbar:

  • dop_feedback_eligible_query: Tritt auf, wenn der Abfrageplan für DOP-Feedback infrage kommt. Zusätzliche Ereignisse können ausgelöst werden, wenn ein erneuter Kompilierungs- oder ein SQL Server Instanzneustart auftritt.
  • dop_feedback_provided: Tritt auf, wenn ein DOP-Feedback Daten für eine bestimmte Abfrage bereitgestellt hat. Dieses Ereignis enthält Baseline-Statistiken, wenn Feedback zum ersten Mal bereitgestellt wird, und vorherige Feedbackstatistiken, wenn nachfolgendes Feedback bereitgestellt wird.
  • dop_feedback_validation: Tritt auf, wenn die Validierung für die Abfragelaufzeitstatistiken für eine Baseline- oder vorherige Feedbackstatistiken erfolgt.
  • dop_feedback_stabilized: Tritt auf, wenn DOP-Feedback für eine Abfrage stabilisiert wird.
  • dop_feedback_reverted: Tritt auf, wenn ein DOP-Feedback wiederhergestellt wird. Das Ereignis wird ausgelöst, wenn die Feedbacküberprüfung beim ersten bereitgestellten Feedback fehlschlägt. Das System wird wieder in den Zustand ohne Feedback zurückgesetzt.
  • dop_feedback_analysis_stopped: Tritt auf, wenn die DOP-Feedbackanalyse für eine Abfrage beendet wird.

Feedback zur Kardinalitätsschätzung (Kardinalitätsschätzung)

Gilt für: SQL Server 2022 (16.x) und höher. Derzeit in der Vorschauversion für Azure SQL Datenbank und Azure SQL Managed Instance.

Ab SQL Server 2022 (16.x)) ist das CE-Feedback (Cardinality Estimation) Teil der intelligenten Abfrageverarbeitungsfamilie und adressiert suboptimale Abfrageausführungspläne für wiederholte Abfragen, wenn diese Probleme auf falschen ANNAHMEN des CE-Modells resultieren. Dieses Szenario hilft bei der Reduzierung von Regressionsrisiken im Zusammenhang mit der standardmäßigen Kardinalitätsschätzung beim Upgrade von älteren Versionen der Datenbank-Engine.

Da die große Vielzahl an Arbeitsauslastungen und Datenverteilungen von Kunden nicht durch einen einzelnen Satz von Modellen und Annahmen der Kardinalitätsschätzung abgedeckt werden kann, bietet das Feedback zur Kardinalitätsschätzung eine anpassbare Lösung basierend auf Merkmalen zur Abfragelaufzeit. Das Feedback zur Kardinalitätsschätzung identifiziert und verwendet eine Modellannahme, die besser zu einer bestimmten Abfrage- und Datenverteilung passt, um die Qualität des Abfrageausführungsplans zu verbessern. Das Feedback wird angewendet, wenn erhebliche Modellschätzungsfehler gefunden werden, die zu Leistungsverlusten führen.

  • Ab SQL Server 2022 (16.x) ist das CE-Feedback für sekundäre Replikate in Verfügbarkeitsgruppen nicht repliziert, wenn Abfragespeicher für sekundäre Replikate aktiviert ist. CE-Feedback profitiert derzeit nur von primären Replikaten. Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.

Grundlegendes zur Kardinalitätsschätzung

Die Kardinalitätsschätzung (Cardinality Estimation, CE) gibt an, wie der Abfrageoptimierer die Gesamtanzahl von Zeilen schätzen kann, die auf jeder Ebene eines Abfrageplans verarbeitet wurden. Die Kardinalitätsschätzung in SQL Server erfolgt in erster Linie mithilfe von Histogrammen, die gleichzeitig mit Indizes oder Statistiken erstellt werden. Der Vorgang kann entweder manuell oder automatisch ausgeführt werden. In manchen Fällen verwendet SQL Server auch Einschränkungsinformationen und logische Umschreibungen von Abfragen, um die Kardinalität zu bestimmen.

Unterschiedliche Versionen der Datenbank-Engine verwenden unterschiedliche Modellannahmen der Kardinalitätsschätzung, basierend darauf, wie Daten verteilt und abgefragt werden. Weitere Informationen finden Sie unter Versionen der Kardinalitätsschätzung.

Implementierung des Feedbacks zur Kardinalitätsschätzung

Das Feedback zur Kardinalitätsschätzung lernt im Lauf der Zeit, welche Modellannahmen der Kardinalitätsschätzung optimal sind, und wendet dann die Annahme an, die sich im Verlauf als am zutreffendsten erwiesen hat:

  1. Das Feedback zur Kardinalitätsschätzung identifiziert modellbezogene Annahmen und wertet aus, ob sie für wiederholte Abfragen genau sind.

  2. Wenn eine Annahme falsch aussieht, wird eine nachfolgende Ausführung derselben Abfrage mit einem Abfrageplan getestet, der die betroffene Modellannahme der Kardinalitätsschätzung anpasst und überprüft, ob diese Anpassung hilfreich ist.

  3. Wenn die Planqualität verbessert wird, wird der alte Abfrageplan durch einen Abfrageplan ersetzt, der den entsprechenden USE HINT-Abfragehinweis verwendet, der das Schätzungsmodell anpasst. Die Implementierung erfolgt den Mechanismus des Abfragespeicherhinweises.

Nur verifiziertes Feedback wird beibehalten. Feedback zur Kardinalitätsschätzung wird für diese Abfrage nicht verwendet, wenn die angepasste Modellannahme zu einer Leistungsregression führt. In diesem Zusammenhang gilt auch eine abgebrochene Abfrage eines Benutzers als Regression.

Szenarien mit Feedback zur Kardinalitätsschätzung

Das Feedback zur Kardinalitätsschätzung befasst sich mit wahrgenommenen Regressionsproblemen, die sich aus falschen Modellannahmen der Kardinalitätsschätzung ergeben, wenn der Standardwert für die Kardinalitätsschätzung (CE120 oder höher) verwendet wird. Das Feedback kann selektiv unterschiedliche Modellannahmen verwenden. Zu den Szenarien gehören Korrelation, Join Containment und Optimiererzeilenziel.

Korrelation

Bei der Schätzung der Selektivität von Prädikaten in einer bestimmten Tabelle oder Sicht oder der Schätzung der Anzahl von Zeilen, die das angegebene Prädikat erfüllen, verwendet der Abfrageoptimierer Korrelationsmodellannahmen. Folgende Annahmen für Prädikate können gelten:

  • Vollständig unabhängig (Standard für CE70): Die Kardinalität wird durch Multiplikation der Selektivitätswerte aller Prädikate berechnet.

  • Teilweise korreliert (Standard für CE120 und höher): Die Kardinalität wird anhand einer Variation im exponentiellen Backoff berechnet, und die Selektivitätswerte werden vom am meisten selektiven bis zum am wenigsten selektiven Prädikat sortiert.

  • Vollständig korreliert: Die Kardinalität wird anhand der Mindestwerte für die Selektivität für alle Prädikate berechnet.

Das folgende Beispiel verwendet eine partielle Korrelation, wenn die Datenbankkompatibilität auf 120 oder höher festgelegt ist:

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

Wenn die Datenbankkompatibilität auf 160 festgelegt ist und die Standardkorrelation verwendet wird, versucht das Feedback zur Kardinalitätsschätzung, die Korrelation schrittweise in die richtige Richtung zu verschieben, basierend darauf, ob die Kardinalität im Vergleich zur tatsächlichen Anzahl von Zeilen unter- oder überschätzt wurde. Verwenden Sie die vollständige Korrelation, wenn eine tatsächliche Anzahl von Zeilen größer als die geschätzte Kardinalität ist. Verwenden Sie die vollständige Unabhängigkeit, wenn eine tatsächliche Anzahl von Zeilen kleiner als die geschätzte Kardinalität ist.

Weitere Informationen finden Sie unter Versionen der Kardinalitätsschätzung.

Joineinschluss

Wenn der Abfrageoptimierer die Selektivität von Joinprädikat und anwendbaren Filterprädikaten schätzt, verwendet er Modellannahmen zum Einschluss. Diese Annahmen sind:

  • Einfacher Einschluss (Standard für CE70): Hierbei wird davon ausgegangen, dass Joinprädikate vollständig korreliert ist. Dabei wird zuerst die Filterselektivität berechnet und dann die Joinselektivität einbezogen.

  • Basiseinschluss (Standard für CE120 und höher): Hierbei wird keine Korrelation zwischen Joinprädikaten und Downstreamfiltern angenommen.

Dabei wird zuerst die Joinselektivität berechnet und dann die Filterselektivität einbezogen.

Das folgende Beispiel verwendet den Basiseinschluss, wenn die Datenbankkompatibilität auf 120 oder höher festgelegt ist:

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Weitere Informationen finden Sie unter Versionen der Kardinalitätsschätzung.

Zeilenziel des Optimierers

Wenn der Abfrageoptimierer die Kardinalität eines Ausführungsplans schätzt, geht er in der Regel davon aus, dass alle qualifizierten Zeilen aus allen Tabellen verarbeitet werden müssen. Einige Abfragemuster führen jedoch dazu, dass der Abfrageoptimierer nach einem Plan sucht, der weniger Zeilen zurückgibt, um die E/A-Last zu reduzieren. Wenn die Abfrage mit dem Schlüsselwort TOP, IN oder EXISTS, dem Abfragehinweis FAST oder einer SET ROWCOUNT-Anweisung eine Zielanzahl von Zeilen (Zeilenziel) angibt, die zur Laufzeit erwartet werden, wird dieses Zeilenziel als Teil des Abfrageoptimierungsprozesses verwendet, wie im folgenden Beispiel gezeigt:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Bei Anwendung des Zeilenzielplans wird die geschätzte Anzahl von Zeilen im Abfrageplan reduziert, da der Abfrageoptimierer davon ausgeht, dass eine kleinere Anzahl von Zeilen verarbeitet werden muss, um das Zeilenziel zu erreichen.

Das Zeilenziel ist zwar eine vorteilhafte Optimierungsstrategie für bestimmte Abfragemuster, wenn Daten nicht einheitlich verteilt sind, allerdings werden möglicherweise mehr Seiten überprüft als geschätzt, was bedeutet, dass das Zeilenziel ineffizient wird. Das Feedback zur Kardinalitätsschätzung kann die Zeilenzielüberprüfung deaktivieren und einen Suchvorgang aktivieren, wenn diese Ineffizienz erkannt wird.

Im Ausführungsplan gibt es kein spezifisches Attribut für CE-Feedback, aber es wird ein Attribut für den Abfragespeicher-Hinweis aufgeführt. Suchen Sie nach dem , der QueryStoreStatementHintSource sein soll CE feedback.

Überlegungen zum CE-Feedback

Legen Sie zum Aktivieren des Feedbacks zur Kardinalitätsschätzung den Datenbank-Kompatibilitätsgrad 160 für die Datenbank fest, mit der Sie beim Ausführen der Abfrage verbunden sind. Die Abfragespeicher muss aktiviert sein und muss sich im READ_WRITE Modus für jede Datenbank befinden, in der CE-Feedback verwendet wird.

Die Feedbackaktivität zur Kardinalitätsschätzung ist über die XEvents query_feedback_analysis und query_feedback_validation sichtbar.

Hinweise, die vom Feedback zur Kardinalitätsschätzung festgelegt werden, können mithilfe der Katalogsicht sys.query_store_query_hints nachverfolgt werden.

Feedbackinformationen können mithilfe der sys.query_store_plan_feedback Katalogansicht nachverfolgt werden.

Um CE-Feedback auf Datenbankebene zu deaktivieren, verwenden Sie die ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFFDatenbankbereichskonfiguration.

Um das Feedback zur Kardinalitätsschätzung auf Abfrageebene zu deaktivieren, verwenden Sie den Abfragehinweis DISABLE_CE_FEEDBACK.

Wenn für eine Abfrage durch den Abfragespeicher ein Abfrageplan erzwungen wird, wird das Feedback zur Kardinalitätsschätzung für diese Abfrage nicht verwendet.

Wenn eine Abfrage hartcodierte Abfragehinweise enthält oder benutzerseitig festgelegte Abfragespeicherhinweise verwendet, wird das Feedback zur Kardinalitätsschätzung für diese Abfrage nicht verwendet. Weitere Informationen finden Sie unter Hinweise (Transact-SQL) – Abfrage und Abfragespeicherhinweis.

Feedback und Melden von Problemen

Für Feedback oder Fragen per E-Mail CEFfeedback@microsoft.com

Nächste Schritte