Hinweise zu Abfragespeicher

Gilt für: SQL Server 2022 (16.x) Azure SQL-DatenbankAzure SQL Managed Instance

In diesem Artikel wird erläutert, wie Abfragehinweise mithilfe des Abfragespeichers angewendet werden. Mit Abfragespeicherhinweisen können Sie Abfragepläne auf einfache Weise strukturieren, ohne den Anwendungscode ändern zu müssen.

Abfragespeicherhinweise sind in Azure SQL-Datenbank und Azure SQL Managed Instance verfügbar. Abfragespeicher Hinweise sind auch ein Feature, das in SQL Server in SQL Server 2022 (16.x) eingeführt wurde.

• Weitere Informationen zum Konfigurieren und Verwalten mit dem Abfragespeicher finden Sie unter Überwachen der Leistung mit dem Abfragespeicher.
• Informationen zum Ermitteln von handlungsrelevanten Informationen und zur Optimierung der Leistung mit dem Abfragespeicher finden Sie unter Optimieren der Leistung mit dem Abfragespeicher.
• Informationen zum Betrieb des Abfragespeichers in Azure SQL-Datenbank finden Sie unter Verwenden des Abfragespeichers in Azure SQL-Datenbank.

Achtung

Da der SQL Server-Abfrageoptimierer in der Regel den besten Ausführungsplan für eine Abfrage auswählt, wird empfohlen, dass nur erfahrene Entwickler und Datenbankadministratoren Hinweise verwenden, wenn alle anderen Möglichkeiten sich als unbefriedigend erwiesen haben. Weitere Informationen finden Sie unter Abfragehinweise.

Übersicht

Im Idealfall wählt der Abfrageoptimierer für eine Abfrage einen optimalen Ausführungsplan aus. Ist dies nicht der Fall, möchten Entwickler oder Datenbankadministratoren möglicherweise die Ausführung der Abfrage für bestimmte Bedingungen manuell optimieren. Abfragehinweise werden über die OPTION-Klausel angegeben und können zur Beeinflussung des Ausführungsverhaltens der Abfrage genutzt werden. Mit Abfragehinweisen können Sie zwar verschiedene leistungsbezogene Probleme lokal lösen, doch müssen Sie dafür den ursprünglichen Abfragetext umschreiben. Vermutlich sind Datenbankadministratoren und Entwickler nicht immer in der Lage, Transact-SQL-Code direkt zu ändern, um einen Abfragehinweis einzufügen. Möglicherweise ist der Transact-SQL-Code in der Anwendung hartcodiert oder wurde von der Anwendung automatisch generiert. Bisher mussten Entwickler auf Planhinweislisten zurückgreifen, die häufig sehr komplex sind.

Informationen dazu, welche Abfragehinweise angewendet werden können, finden Sie unter Unterstützte Abfragehinweise.

Wann sollten Abfragespeicherhinweise verwendet werden?

Wie der Name schon sagt, hängt das Feature vom Abfragespeicher ab und erweitert diesen. Mit dem Abfragespeicher können Abfragen, Ausführungspläne und die entsprechenden Laufzeitstatistiken erfasst werden. Der Abfragespeicher wurde in SQL Server 2016 (13.x) eingeführt und ist in Azure SQL-Datenbank standardmäßig aktiviert. Er vereinfacht die Vorgehensweise bei der Leistungsoptimierung für den Benutzer erheblich.

Die Abfrage wird zunächst ausgeführt und vom Abfragespeicher erfasst. Anschließend erstellt der Datenbankadministrator für eine Abfrage einen Abfragespeicherhinweis. Nun wird die Abfrage unter Verwendung des Abfragespeicherhinweises ausgeführt.

Bei folgenden Beispielen können Abfragespeicherhinweise bei Leistungsproblemen auf Abfrageebene hilfreich sein:

• Erneutes Kompilieren einer Abfrage bei jeder Ausführung
• Festlegen einer Obergrenze für die Arbeitsspeicherzuweisung bei Masseneinfügungsvorgängen
• Beschränken Sie den maximalen Grad an Parallelität beim Aktualisieren von Statistiken.
• Verwenden eines Hashjoins anstelle eines Joins geschachtelter Schleifen
• Verwenden des Kompatibilitätsgrads 110 für eine bestimmte Abfrage bei Beibehaltung des Kompatibilitätsgrads 150 für alle anderen Vorgänge in der Datenbank
• Deaktivieren der Zeilenzieloptimierung für eine SELECT TOP-Abfrage

So verwenden Sie Abfragespeicherhinweise

1. Identifizieren Sie die Abfragespeicher-query_id der Abfrageanweisung, die Sie ändern möchten. Hierzu stehen drei Möglichkeiten zur Verfügung: 1.1. Abfragen der Abfragespeicher-Katalogsichten. 1.2. Verwenden von Abfragespeicherberichten in SQL Server Management Studio. 1.3. Verwenden von Query Performance Insight für Azure SQL-Datenbank im Azure-Portal.
2. Führen Sie sys.sp_query_store_set_hints mit dem Wert für query_id und der Abfragehinweiszeichenfolge aus, die Sie auf die Abfrage anwenden möchten. Die Zeichenfolge kann einen oder mehrere Abfragehinweise enthalten. Ausführliche Informationen hierzu finden Sie unter sys.sp_query_store_set_hints.

Nach der Erstellung werden Abfragespeicherhinweise auch nach einem Neustart oder Failover beibehalten. Abfragespeicherhinweise überschreiben hartcodierte Hinweise auf Anweisungsebene sowie Hinweise einer vorhandenen Planhinweisliste.

Wenn ein Abfragehinweis im Widerspruch zu den Möglichen für die Abfrageoptimierung steht, wird die Abfrageausführung nicht blockiert, und der Hinweis wird nicht angewendet. Sollte ein Hinweis zu einem Fehler in der Abfrageausführung führen, wird er ignoriert. Die aktuellen Fehlerdetails werden dann in sys.query_store_query_hints angezeigt.

Das folgende Video bietet einen Überblick über Abfragespeicherhinweise:

Gespeicherte Systemprozeduren für Abfragespeicherhinweise

Verwenden Sie zum Erstellen oder Aktualisieren von Hinweisen sys.sp_query_store_set_hints. Hinweise werden als gültige Zeichenfolge im Format N'OPTION (...)' angegeben.

• Ist beim Erstellen eines Abfragespeicherhinweises für einen bestimmten Wert für query_id kein Abfragespeicherhinweis vorhanden, wird ein neuer erstellt.
• Wenn beim Erstellen oder Aktualisieren eines Abfragespeicher Hinweises bereits ein Abfragespeicher Hinweis für einen bestimmten query_idvorhanden ist, überschreibt der letzte bereitgestellte Wert die zuvor angegebenen Werte für die zugeordnete Abfrage.
• Wenn kein query_id vorhanden ist, wird ein Fehler ausgelöst.

Hinweis

Eine vollständige Liste der unterstützten Hinweise finden Sie unter sys.sp_query_store_set_hints.

Verwenden Sie sys.sp_query_store_clear_hints, um query_id zugeordnete Hinweise zu entfernen.

XML-Attribute des Ausführungsplans

Wenn Hinweise angewendet werden, wird das folgende Resultset im StmtSimple -Element des Ausführungsplans im XML-Format angezeigt:

Attribut	Beschreibung
QueryStoreStatementHintText	Tatsächlicher Abfragespeicherhinweis, der auf die Abfrage angewendet wird
QueryStoreStatementHintId	Eindeutiger Bezeichner eines Abfragehinweises
QueryStoreStatementHintSource	Quelle des Abfragespeicherhinweises (z. B. „Benutzer“)

Hinweis

Diese XML-Elemente sind über die Ausgabe der Transact-SQL-Befehle SET STATISTICS XML und SET SHOWPLAN XML verfügbar.

Abfragespeicherhinweise und Featureinteroperabilität

• Abfragespeicherhinweise überschreiben andere hartcodierte Hinweise auf Anweisungsebene sowie Planhinweislisten.
• Abfragen werden immer ausgeführt, und Abfragespeicher Hinweise ignoriert werden, die andernfalls zu einem Fehler führen würden.
• Wenn Abfragespeicher Hinweise widersprechen, blockiert SQL Server die Abfrageausführung nicht, und Abfragespeicher Hinweis wird nicht angewendet.
• Einfache Parametrisierung – Abfragespeicherhinweise werden nicht für Anweisungen unterstützt, die für einfache Parametrisierung infrage kommen.
• Erzwungene Parametrisierung – Der RECOMPILE-Hinweis ist nicht kompatibel mit erzwungener Parametrisierung, die auf Datenbankebene festgelegt ist. Wenn für die Datenbank eine erzwungene Parametrisierung festgelegt ist und der RECOMPILE-Hinweis Teil der hinweist-Zeichenfolge ist, die in Abfragespeicher für eine Abfrage festgelegt ist, ignoriert SQL Server den RECOMPILE-Hinweis und wendet alle anderen Hinweise an, wenn sie angewendet werden.
  • Darüber hinaus gibt SQL Server eine Warnung (Fehlercode 12461) aus, die besagt, dass der RECOMPILE-Hinweis ignoriert wurde.
  • Weitere Informationen zu Überlegungen für Anwendungsfälle zur erzwungenen Parametrisierung finden Sie unter Richtlinien für die Verwendung der erzwungenen Parametrisierung.
• Manuell erstellte Abfragespeicher Hinweise sind von der Bereinigung ausgenommen. Der Hinweis und die Abfrage werden nicht von Abfragespeicher durch die automatische Aufbewahrung der Erfassungsrichtlinie bereinigt.
  • Abfragen können von Benutzern manuell entfernt werden, wodurch auch der zugehörige Abfragespeicher-Hinweis entfernt würde.
  • Abfragespeicher Hinweise, die automatisch vom CE-Feedback generiert werden, unterliegen der automatischen Aufbewahrung der Erfassungsrichtlinie sauber.
  • DOP-Feedback und Speicherzuweisung Von Feedback-Shape-Abfrageverhalten ohne verwendung Abfragespeicher Hinweise. Wenn Abfragen durch automatische Aufbewahrung der Erfassungsrichtlinie bereinigungen, werden auch DOP-Feedback- und Speicherzuweisungsdaten bereinigt.
  • Sie können manuell denselben Abfragespeicher Hinweis erstellen, dass CE-Feedback implementiert wurde, und dann unterliegt die Abfrage mit dem Hinweis nicht mehr sauber durch die automatische Aufbewahrung der Erfassungsrichtlinie.

Abfragespeicher Hinweise und Verfügbarkeitsgruppen

Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.

• Vor SQL Server 2022 (16.x) können Abfragespeicher Hinweise auf das primäre Replikat einer Verfügbarkeitsgruppe angewendet werden.
• Ab SQL Server 2022 (16.x) sind Abfragespeicher Hinweise auch für sekundäre Replikate in Verfügbarkeitsgruppen replikatfähig, wenn Abfragespeicher für sekundäre Replikate aktiviert ist.
• Sie können einem bestimmten Replikat oder Replikatsatz einen Abfragespeicher Hinweis hinzufügen, wenn sie Abfragespeicher für sekundäre Replikate aktiviert haben. In sys.sp_query_store_set_query_hints wird dies durch den Parameter festgelegt, der @query_hint_scope in SQL Server 2022 (16.x) eingeführt wurde.
• Suchen Sie die verfügbaren Replikatsätze, indem Sie sys.query_store_replicas abfragen.
• Suchen von Plänen, die für sekundäre Replikate mit sys.query_store_plan_forcing_locations erzwungen wurden.

Bewährte Methoden für Abfragespeicherhinweise

• Schließen die Index- und Statistikwartung ab, bevor Sie Abfragen für potenzielle neue Abfragespeicherhinweise auswerten.
• Testen Sie Ihre Anwendungsdatenbank auf dem neuesten Kompatibilitätsgrad, bevor Sie Abfragespeicher Hinweise verwenden.
  • Beispielsweise wurde die PSP-Optimierung (Parameter Sensitive Plan) in SQL Server 2022 (16.x) (Kompatibilitätsgrad 160) eingeführt, die mehrere aktive Pläne pro Abfrage nutzt, um nicht einheitliche Datenverteilungen zu adressieren. Wenn Ihre Umgebung nicht die neueste Kompatibilitätsstufe verwenden kann, können Abfragespeicherhinweise, die den RECOMPILE-Hinweis verwenden, auf jeder unterstützten Kompatibilitätsstufe genutzt werden.
• Abfragespeicherhinweise überschreiben SQL Server-Abfrageplanverhalten. Es wird empfohlen, nur Abfragespeicherhinweise zu nutzen, wenn es notwendig ist, leistungsbezogene Probleme zu beheben.
• Es wird empfohlen, Abfragespeicherhinweise, Hinweise auf Anweisungsebene, Planhinweislisten und vom Abfragespeicher erzwungene Pläne bei Änderungen von Datenverteilungen und während Datenbankmigrationsprojekten immer neu zu bewerten. Änderungen in der Datenverteilung können dazu führen, dass Abfragespeicherhinweise suboptimale Ausführungspläne generieren.

Beispiele

A. Demo zu Abfragespeicherhinweisen

In der folgenden exemplarischen Vorgehensweise für Abfragespeicherhinweise in Azure SQL-Datenbank wird eine importierte Datenbank über eine BACPAC-Datei verwendet. Informationen zum Importieren einer neuen Datenbank in einen Azure SQL-Datenbankserver finden Sie unter Schnellstart: Importieren einer BACPAC-Datei in eine Datenbank.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identifizieren einer Abfrage im Abfragespeicher

Im folgenden Beispiel werden die Abfragen sys.query_store_query_text und sys.query_store_query ausgeführt, um die query_id für ein ausgeführtes Abfragetextfragment zu erhalten.

In dieser Demo befindet sich die Abfrage, die wir optimieren möchten, in der Beispieldatenbank SalesLT:

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Abfragespeicher spiegelt Abfragedaten nicht sofort in seinen Systemsichten wider.

Identifizieren Sie die Abfrage in den Systemkatalogsichten des Abfragespeichers:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

In den folgenden Beispielen wurde das vorherige Abfragebeispiel in der Datenbank SalesLT als query_id 39 identifiziert.

Wenden Sie nach der Identifizierung den Hinweis an, um eine maximale Speicherzuweisungsgröße in Prozent des konfigurierten Arbeitsspeicherlimits auf die query_id zu erzwingen:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Sie können Abfragehinweise auch mit der folgenden Syntax anwenden (z. B. die Option zum Erzwingen der Legacy-Kardinalitätsschätzung):

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Sie können mehrere Abfragehinweise mit einer durch Kommas getrennten Liste anwenden:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Überprüfen Sie den Abfragespeicherhinweis für query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

Entfernen Sie schließlich den Hinweis von query_id 39, indem Sie sp_query_store_clear_hints verwenden.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Siehe auch

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• Speichern eines Ausführungsplans im XML-Format
• Anzeigen und Speichern von Ausführungsplänen
• Hinweise (Transact-SQL) – Abfrage

Nächste Schritte

• Bewährte Methoden für den Abfragespeicher
• Bewährte Methoden für Abfragespeicherhinweise
• Überwachen der Leistung mit dem Abfragespeicher
• Konfigurieren des maximalen Parallelitätsgrads (MAXDOP) in Azure SQL Database