Freigeben über

Abfragespeicherhinweise

  • Artikel

Gilt für: SQL Server 2022 (16.x) Azure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

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 eine Funktion, die in SQL Server 2022 (16.x) 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 eine Übersicht ü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 allgemeine Leistungsoptimierung der Kundenfreundlichkeit 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

Beispiele, bei denen Query Store-Hinweise bei Leistungsproblemen auf Abfrageebene helfen können:

  • Erneutes Kompilieren einer Abfrage bei jeder Ausführung
  • Eine Obergrenze für die Speicherzuteilungsgröße bei Masseneinfügungsoperationen festlegen.
  • Begrenzen des maximalen Grads der Parallelität bei der Aktualisierung von Statistiken.
  • Verwenden Sie einen Hash Join anstelle eines Nested Loops Join.
  • 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 den 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 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.

  • Wenn Sie einen Abfragespeicherhinweis erstellen und für einen bestimmten Wert query_id kein solcher Hinweis existiert, wird ein neuer Abfragespeicherhinweis 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 die mit query_id zugeordneten 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. „Benutzende“)

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.
  • Mit Ausnahme des ABORT_QUERY_EXECUTION-Hinweises werden Abfragen mit Query Store-Hinweisen immer ausgeführt. Entgegenstehende Hinweise aus dem Abfragespeicher 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 Parameterisierung, die auf Datenbankebene festgelegt ist. Wenn die Datenbank den Parameterisierungssatz erzwungen hat und der RECOMPILE Hinweis Teil des Hinweis-Zeichenfolgensatzes im Abfragespeicher für eine Abfrage ist, ignoriert SQL Server den RECOMPILE Hinweis und wendet alle anderen Hinweise an, wenn sie angewendet werden.
  • Manuell erstellte Query Store-Hinweise sind von der Bereinigung ausgenommen. Der Hinweis und die Abfrage werden nicht durch die automatische Aufbewahrung der Erfassungsrichtlinie aus dem Abfragespeicher bereinigt.
    • Abfragen können von Benutzern manuell entfernt werden, was auch den zugeordneten Abfragespeicher-Hinweis entfernen würde.
    • Abfragespeicherhinweise, die automatisch aus CE-Feedback generiert werden, unterliegen der automatischen Aufbewahrung der Erfassungsrichtlinie.
    • DOP-Feedback und Speicherzuweisungs-Feedback ändern das Abfrageverhalten, ohne Abfragespeicherhinweise zu verwenden. Wenn Abfragen durch die automatische Aufbewahrung der Erfassungsrichtlinie bereinigt werden, werden auch die Daten aus dem DOP-Feedback und dem Speicherzuweisungs-Feedback bereinigt.
    • Sie können manuell denselben Abfragespeicherhinweis erstellen, der CE-Feedback implementiert. In diesem Fall unterliegt die Abfrage mit dem Hinweis nicht mehr der automatischen Aufbewahrung der Erfassungsrichtlinie.

Abfragespeicherhinweise und Verfügbarkeitsgruppen

Abfragespeicher-Hinweise haben keine Auswirkungen auf sekundäre Replikate, es sei denn, der Abfragespeicher für sekundäre Replikate ist aktiviert. Weitere Informationen finden Sie unter Query Store für sekundäre Replikas.

  • Vor SQL Server 2022 (Version 16.x) können Abfragespeicher-Hinweise auf das primäre Replikat einer Verfügbarkeitsgruppe angewendet werden.
  • Ab SQL Server 2022 (16.x) gelten Abfragespeicherhinweise auch für sekundäre Replikate in Verfügbarkeitsgruppen, wenn der Abfragespeicher für die sekundären Replikate aktiviert wurde.
  • 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 mit sys.query_store_plan_forcing_locations Pläne, die für sekundäre Replikate erzwungen werden.

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 setzen das SQL Server-Abfrageplanverhalten außer Kraft. 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.

Nachdem Sie den Hinweis ermittelt haben, wenden Sie ihn an, um eine maximale Speicherzuweisung in Prozent des konfigurierten Arbeitsspeicherlimits für die query_id festzulegen.

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