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 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:

Überblick

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

Wenn kein optimaler Plan ausgewählt ist, möchte ein Entwickler oder DBA möglicherweise manuell für bestimmte Bedingungen optimieren. Abfragehinweise werden über die OPTION Klausel angegeben und können verwendet werden, um das Verhalten der Abfrageausführung zu beeinflussen. 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 einzuleiten. Transact-SQL kann hartcodiert in einer Anwendung oder automatisch von der Anwendung generiert werden. Bisher muss sich ein Entwickler möglicherweise auf Planhandbücher verlassen, die für die Verwendung komplex sein können.

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. 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 verwaltete Instanz und Azure SQL-Datenbank standardmäßig aktiviert.

The workflow for Query Store Hints.

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 der 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. Dies können Sie auf verschiedene Arten tun:
    • 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.

Wenn ein Abfragehinweis dem entspricht, was für die Abfrageoptimierung möglich ist, 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.

Gespeicherte Systemprozeduren für Abfragespeicherhinweise

Verwenden Sie zum Erstellen oder Aktualisieren von Hinweisen sys.sp_query_store_set_hints. Hinweise werden in einem gültigen Zeichenfolgenformat N'OPTION (...)'angegeben.

  • Wenn Sie einen Abfragespeicher Hinweis erstellen, wird ein neuer Abfragespeicher Hinweis erstellt, wenn kein Abfragespeicher Hinweis für einen bestimmten query_idvorhanden ist.
  • Wenn Sie einen Abfragespeicher Hinweis erstellen oder aktualisieren, wenn bereits ein Abfragespeicher Hinweis für einen bestimmten query_idvorhanden ist, überschreibt der letzte bereitgestellte Wert zuvor angegebene Werte für die zugeordnete Abfrage.
  • query_id Wenn kein Fehler 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

  • Abfragespeicher Hinweise überschreiben andere hartcodierte Hinweise und Planführungslinien auf Anweisungsebene.
  • Abfragen werden immer ausgeführt. Entgegengesetzte Abfragespeicher Hinweise werden ignoriert, die andernfalls einen Fehler verursachen 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 die Datenbank den Parameterisierungssatz 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 alle anderen Hinweise an, wenn sie angewendet werden.
  • Manuell erstellte Abfragespeicher Hinweise sind von sauber up ausgenommen. Der Hinweis und die Abfrage werden nicht von Abfragespeicher durch die automatische Aufbewahrung der Erfassungsrichtlinie sauber.
    • Abfragen können von Benutzern manuell entfernt werden, was auch den zugeordneten Abfragespeicher Hinweis entfernen würde.
    • Abfragespeicher Hinweise automatisch von der CE-Feedback unterliegt sauber durch die automatische Aufbewahrung der Erfassungsrichtlinie.
    • DOP-Feedback und Speicher gewähren Feedback-Shape-Abfrageverhalten, ohne Abfragespeicher Hinweise zu verwenden. Wenn Abfragen durch die automatische Aufbewahrung der Erfassungsrichtlinie sauber werden, werden auch DOP-Feedback- und Speicherzuteilungsfeedbackdaten sauber.
    • Sie können manuell denselben Abfragespeicher Hinweis erstellen, den CE-Feedback implementiert hat, 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 replizierbar, wenn Abfragespeicher für sekundäre Replikate aktiviert sind.
  • Sie können einem bestimmten Replikat oder Replikatsatz 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 festgelegt, der @query_hint_scope 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ätsstufe, bevor Sie Abfragespeicher Hinweise verwenden.
    • Beispielsweise wurde die Optimierung für parametersensitiven Plan (Parameter Sensitive Plan, PSP) in SQL Server 2022 (16.x) (Kompatibilitätsebene 160) eingeführt, die mehrere aktive Pläne pro Abfrage verwendet, um nichtuniforme Datenverteilungen zu adressieren. Wenn Ihre Umgebung die neueste Kompatibilitätsstufe nicht verwenden kann, können Abfragespeicher Hinweise mithilfe des RECOMPILE-Hinweises auf allen unterstützten Kompatibilitätsstufen verwendet werden.
  • Abfragespeicherhinweise überschreiben SQL Server-Abfrageplanverhalten. Es wird empfohlen, nur Abfragespeicher Hinweise zu verwenden, wenn es erforderlich 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 Abfragespeicher Hinweise zu suboptimalen Ausführungsplänen generiert werden.

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 den Systemansichten 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;