Freigeben über

Abfragespeicherhinweise

Gilt für: SQL Server 2022 (16.x) und spätere Versionen Azure SQL-DatenbankAzure SQL verwaltete InstanzSQL-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.

Caution

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:

Overview

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

Wenn kein optimaler Plan ausgewählt ist, möchte ein Entwickler oder Datenbankadministrator (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. Datenbankadministratoren und Entwickler können möglicherweise nicht immer Direktänderungen an Transact-SQL Code vornehmen, um einen Abfragehinweis hinzuzufü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.

Abfragespeicherhinweise lösen dieses Problem, indem sie es Ihnen ermöglichen, einen Abfragehinweis in eine Abfrage einzufügen, ohne den Transact-SQL-Text direkt zu ändern. 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 zuerst den Abfragespeicher eingeführt, und jetzt ist er in SQL Server 2022 (16.x), azure SQL Managed Instance, Azure SQL Database und SQL-Datenbank in Microsoft Fabric 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 Sie die 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. Dies können Sie auf verschiedene Arten tun:

  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.

Wenn ein Abfragehinweis im Widerspruch zu dem steht, was für die Abfrageoptimierung möglich ist, wird die Abfrageausführung nicht blockiert und der Hinweis nicht angewendet. In den Fällen, in denen ein Hinweis dazu führen würde, dass eine Abfrage fehlschlägt, wird der Hinweis ignoriert, und die neuesten Fehlerdetails können in sys.query_store_query_hints angezeigt werden.

Vor der Verwendung von Abfragespeicherhinweisen

Beachten Sie Folgendes, bevor Sie mit der Verwendung von Abfragespeicherhinweisen beginnen.

  • Schließen Sie die Statistikwartung und die Indexwartung (sofern erforderlich) ab, bevor Sie Abfragen für potenzielle neue Abfragespeicherhinweise auswerten. Die Wartung von Statistiken und in geringerem Maße die Indexwartung können das Problem beheben, das andernfalls einen Abfragehinweis erfordert.
  • Testen Sie vor der Verwendung von Abfragespeicherhinweisen die Anwendungsdatenbank auf der neuesten Kompatibilitätsstufe , um festzustellen, ob das Problem gelöst wird, das einen Abfragehinweis erfordert.
    • Beispiel: Die Optimierung des parameterabhängigen Plans (Parameter Sensitive Plan, PSP) in SQL Server 2022 (16.x) wurde unter Kompatibilitätsgrad 160 eingeführt. Es verwendet mehrere aktive Pläne pro Abfrage, um nicht uniforme Datenverteilungen zu adressieren. Wenn Ihre Umgebung die neueste Kompatibilitätsstufe nicht nutzen kann, können Abfragespeicherhinweise mithilfe des RECOMPILE Hinweises auf jeder unterstützenden Kompatibilitätsstufe eingesetzt werden.
  • Abfragespeicherhinweise überschreiben das standardmäßige Abfrageplanverhalten der Datenbank-Engine. Sie sollten Abfragespeicherhinweise nur verwenden, wenn dies erforderlich ist, um leistungsbedingte Probleme zu beheben.
  • Sie sollten Abfragespeicherhinweise, Hinweise auf Anweisungsebene, Planhinweislisten und vom Abfragespeicher erzwungene Pläne jederzeit bei Änderungen der Datenmengen und -verteilungen und bei Datenbankmigrationsprojekten neu bewerten. Änderungen in Datenvolume und Verteilung können dazu führen, dass Abfragespeicherhinweise suboptimale Ausführungspläne generieren.

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.

Eine vollständige Liste der Hinweise, die als Abfragespeicherhinweis unterstützt werden, finden Sie unter sys.sp_query_store_set_hints.

Verwenden Sie query_id, um die mit zugeordneten Hinweise zu entfernen.

Tip

Möglicherweise müssen Sie Hinweise für alle query_id Werte festlegen oder löschen, die einem Abfragehash entsprechen.

dbo.sp_query_store_modify_hints_by_query_hash ist ein Beispiel für eine gespeicherte Prozedur, die die gespeicherte Systemprozedur sys.sp_query_store_set_hints oder sys.sp_query_store_clear_hints in einer Schleife aufruft, um dies zu erreichen.

XML-Attribute des Ausführungsplans

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

Attribute Description
QueryStoreStatementHintText Tatsächlich angewendete Query Store-Hinweise auf die Abfrage
QueryStoreStatementHintId Eindeutiger Bezeichner eines Abfragehinweises
QueryStoreStatementHintSource Quelle des Abfragespeicherhinweiss (z. B User. )

Note

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.
  • Außer dem ABORT_QUERY_EXECUTION Hinweis 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 das Datenbankmodul die Abfrageausführung nicht, und der Abfragespeicherhinweis wird nicht angewendet.
  • Abfragespeicherhinweise werden nicht für Anweisungen unterstützt, die für die einfache Parametrisierung qualifiziert sind.
  • Der RECOMPILE Hinweis ist nicht mit erzwungener Parameterisierung kompatibel, die auf Datenbankebene festgelegt ist. Wenn eine Datenbank den Parameterisierungssatz erzwungen hat und der RECOMPILE Hinweis Teil der Abfragespeicherhinweise für eine Abfrage ist, ignoriert das Datenbankmodul den RECOMPILE Hinweis und wendet alle anderen Hinweise an, wenn sie angegeben sind.
  • Manuell erstellte Abfragespeicherhinweise sind von der Bereinigung des Abfragespeichers ausgenommen. Der Hinweis und die Abfrage werden von der Richtlinie für automatische Aufbewahrung nicht bereinigt.
    • Abfragen können von Benutzern manuell entfernt werden. Dadurch wird auch der zugehörige Abfragespeicherhinweis entfernt.
    • 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 Richtlinie für automatische Aufbewahrung bereinigt werden, werden auch die Daten aus dem DOP-Feedback und dem Speicherzuweisungsfeedback bereinigt.
    • Wenn Sie denselben Abfragespeicherhinweis, der CE-Feedback implementiert, manuell erstellen, unterliegt die Abfrage mit dem Hinweis nicht mehr der Richtlinie für automatische Aufbewahrung.

Abfragespeicherhinweise und sekundäre Replikate

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 im Abfragespeicher für lesbare Secondärdateien.

  • In SQL Server 2022 (16.x) und früheren Versionen können Abfragespeicherhinweise nur auf das primäre Replikat angewendet werden.
  • In SQL Server 2025 (17.x) und höheren Versionen können Abfragespeicherhinweise auf sekundäre Replikate in Verfügbarkeitsgruppen angewendet werden, wenn der Abfragespeicher für sekundäre Replikate aktiviert ist. Vollständige Plattformunterstützung finden Sie im Query Store für lesbare sekundäre Instanzen.

Wo der Abfragespeicher für sekundäre Replikate unterstützt wird:

Examples

A. Demo zu Abfragespeicherhinweisen

In der folgenden exemplarischen Vorgehensweise für Abfragespeicherhinweise in Azure SQL-Datenbank wird eine importierte Datenbank über eine BACPAC-Datei (.bacpac) verwendet. Erfahren Sie, wie Sie eine neue Datenbank in einen Azure SQL-Datenbankserver importieren, siehe Schnellstart: Importieren einer Bacpac-Datei in eine Datenbank in azure SQL-Datenbank oder in azure SQL Managed Instance.

-- ************************************************************************ --
-- 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;

Verwandte Inhalte

  • Last updated on