Freigeben über

Hinweise zu Abfragespeicher

  • Artikel

Gilt für: SQL Server 2022 (16.x) Azure SQL-Datenbank Azure 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 ebenfalls ein Feature, das in SQL Server 2022 (16.x) in SQL Server eingeführt wurde.

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.

Das folgende Video bietet einen Überblick über Abfragespeicherhinweise:

Übersicht

Im Idealfall wählt der Abfrageoptimierer für eine Abfrage einen optimalen Ausführungsplan aus.

Wenn kein optimaler Plan ausgewählt ist, möchte eine Fachkraft in der Entwicklung oder DBA möglicherweise manuell für bestimmte Bedingungen 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. Möglicherweise 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 möglicherweise 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 vereinfacht die Vorgehensweise bei der Leistungsoptimierung für den Benutzer erheblich. SQL Server 2016 (13.x) hat zunächst Abfragespeicher eingeführt und ist jetzt in SQL Server 2022 (16.x), Azure SQL Managed Instance und Azure SQL-Datenbank standardmäßig aktiviert.

Workflow für Abfragespeicherhinweise

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
  • Begrenzen des maximalen Grads der Parallelität bei der Aktualisierung 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. Dazu haben Sie verschiedene Möglichkeiten:
    • Abfragen der Abfragespeicher-Katalogsichten.
    • Verwenden von Abfragespeicherberichten in SQL Server Management Studio.
    • 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.

Widerspricht ein Abfragehinweis den Möglichkeiten der Abfrageoptimierung, wird die Ausführung der Abfrage 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.

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.
  • Ist beim Erstellen oder Aktualisieren eines Abfragespeicherhinweises für einen bestimmten Wert für query_id bereits ein Abfragespeicherhinweis vorhanden, überschreibt der letzte angegebene Wert frühere Werte für die entsprechende Abfrage.
  • Ist der Wert für query_id nicht vorhanden, 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

Bei der Anwendung von Hinweisen werden folgende Ergebnisse 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. Entgegengesetzte Abfragespeicher-Hinweise werden ignoriert, die andernfalls einen Fehler verursachen würden.
  • Wenn Abfragespeicherhinweise widersprechen, blockiert SQL Server nicht die Abfrageausführung, und der Abfragespeicherhinweis 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 die Datenbank die Parametrisierung erzwungen hat und der RECOMPILE-Hinweis Teil der in Abfragespeicher für eine Abfrage festgelegten Hinweiszeichenfolge ist, ignoriert SQL Server den RECOMPILE-Hinweis und wendet andere Hinweise an, wenn sie angewendet werden.
  • Manuell erstellte Abfragespeicher-Hinweise sind von der Bereinigung ausgenommen. Der Hinweis und die Abfrage werden nicht vom Abfragespeicher durch die automatische Aufbewahrung der Capture-Richtlinie bereinigt.
    • Abfragen können von Benutzern manuell entfernt werden, was auch den zugeordneten Abfragespeicher-Hinweis entfernen würde.
    • Die vom CE Feedback automatisch generierten Abfragespeicher-Hinweise werden durch die automatische Aufbewahrung der Capture-Richtlinie bereinigt.
    • DOP-Feedback und Speicherzuweisung-Feedback prägen das Abfrageverhalten ohne Abfragespeicher-Hinweise. Wenn Abfragen durch die automatische Aufbewahrung der Capture-Richtlinie bereinigt werden, werden auch Daten des DOP-Feedback und Speicherzuweisung-Feedback bereinigt.
    • Sie können manuell denselben Abfragespeicher Hinweis erstellen, den CE-Feedback implementiert hat, und dann unterliegt die Abfrage mit dem Hinweis nicht mehr der automatischen Aufbewahrung der Capture-Richtlinie.

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 replizierbar, wenn Abfragespeicher für sekundäre Replikate aktiviert ist.
  • Sie können einem bestimmten Replikat oder einer Replikatgruppe einen Abfragespeicher-Hinweis hinzufügen, wenn Abfragespeicher für sekundäre Replikate aktiviert sind. In sys.sp_query_store_set_query_hints wird dies durch den Parameter @query_hint_scope festgelegt, der in SQL Server 2022 (16.x) eingeführt wurde.
  • Suchen Sie die verfügbaren Replikatgruppen, indem Sie sys.query_store_replicas abfragen.
  • Suchen Sie Pläne, 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 der neuesten Kompatibilitätsebene, bevor Sie Abfragespeicher-Hinweise nutzen.
    • Beispielsweise wurde die PSP-Optimierung (Parameter Sensitive Plan) in SQL Server 2022 (16.x) (Kompatibilitätsstufe 160) eingeführt. Sie verwendet mehrere aktive Pläne pro Abfrage, um nicht einheitliche Datenverteilungen zu behandeln. 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 verwenden, 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 möglicherweise 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;

Der Abfragespeicher gibt Abfragedaten nicht sofort an seine Systemsichten zurück.

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;

Zugehöriger Inhalt