Anvisningar för Query Store

Gäller för: SQL Server 2022 (16.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL Database i Förhandsversion av Microsoft Fabric

Den här artikeln beskriver hur du använder frågetips med hjälp av Query Store. Query Store-tips är en lätthanterad metod för att forma frågeplaner utan att ändra programkod.

• Mer information om hur du konfigurerar och administrerar med Query Store finns i Övervaka prestanda med hjälp av Query Store.
• Information om hur du identifierar användbar information och finjusterar prestanda med Query Store finns i Finjustera prestanda med Query Store-.
• Information om hur du kör Query Store i Azure SQL Database finns i Använda Query Store i Azure SQL Database.

Titta på den här videon för en översikt över Query Store-tips:

Overview

Helst väljer frågeoptimeraren en optimal exekveringsplan för en fråga.

Om en optimal plan inte har valts kanske en utvecklare eller databasadministratör (DBA) vill optimera manuellt för specifika villkor. Frågetips anges via OPTION-satsen och kan användas för att påverka frågekörningsbeteendet. Frågetips hjälper till att tillhandahålla lokaliserade lösningar på olika prestandarelaterade problem, men de kräver en omskrivning av den ursprungliga frågetexten. Databasadministratörer och utvecklare kanske inte alltid kan göra ändringar direkt i Transact-SQL kod för att lägga till ett frågetips. Transact-SQL kan hårdkodas i ett program eller genereras automatiskt av programmet. Tidigare kan en utvecklare behöva förlita sig på planguider, vilket kan vara komplext att använda.

Frågelager-hints löser det här problemet genom att låta dig infoga en fråge-hint i en fråga utan att ändra frågans Transact-SQL-text direkt. Information om vilka frågetips som kan användas finns i frågetips som stöds.

När du ska använda Query Store-tips

Den här funktionen utökar och är beroende av Query Store, som namnet antyder. Query Store möjliggör insamling av frågeförfrågningar, exekveringsplaner och associerad körningsstatistik. Query Store förenklar kundupplevelsen för övergripande prestandajustering avsevärt. SQL Server 2016 (13.x) introducerade först Query Store, och nu är det aktiverat som standard i SQL Server 2022 (16.x), Azure SQL Managed Instance, Azure SQL Database och SQL Database i Förhandsversion av Microsoft Fabric.

Exempel där Query Store-tips kan hjälpa dig med prestandaproblem på frågenivå:

• Kompilera om en fråga för varje körning.
• Begränsa storleken på minnestilldelningen för en bulk-införingsoperation.
• Begränsa den maximala graden av parallellitet vid uppdatering av statistik.
• Använd en Hash-koppling i stället för en kapslad loopkoppling.
• Använd kompatibilitetsnivå 110 för en specifik fråga samtidigt som du behåller allt annat i databasen på kompatibilitetsnivå 150.
• Inaktivera radmålsoptimering för en SELECT TOP fråga.

Så här använder du Query Store-tips:

1. Identifiera Query Store-query_id för frågeuttryck som du vill ändra. Du kan göra detta på olika sätt:

   • Utföra frågor mot frågelagrets katalogvyer (Transact-SQL).
   • Använda inbyggda Query Store-rapporter i SQL Server Management Studio.
   • Använda Azure Portal Query Performance Insight för Azure SQL Database.

2. Kör sys.sp_query_store_set_hints med den query_id- och frågetipssträng som du vill tillämpa på frågan. Den här strängen kan innehålla en eller flera frågetips. Fullständig information finns i sys.sp_query_store_set_hints.

När det har skapats bevaras Query Store-tips och klarar omstarter och driftavbrott. Query Store-tips åsidosätter hårdkodade tips på instruktionsnivå och befintliga tips i planguiden.

Om en frågehint motsäger det som är möjligt för frågeoptimering, blockeras inte frågekörningen och hinten tillämpas inte. I de fall där ett tips skulle orsaka att en fråga misslyckas ignoreras tipset och den senaste felinformationen kan visas i sys.query_store_query_hints.

Innan du använder Query Store-tips

Tänk på följande innan du börjar använda Query Store-tips.

• Slutför statistikunderhåll och indexunderhåll (om det behövs) innan du utvärderar frågor för potentiella nya Query Store-tips. Statistikunderhåll och i mindre grad indexunderhåll kan lösa problemet som annars kräver ett frågetips.
• Innan du använder Query Store-tips testar du programdatabasen på den senaste kompatibilitetsnivån för att se om det löser problemet som kräver ett frågetips.
  • Till exempel introducerades parameterkänslig plansoptimering (PSP) i SQL Server 2022 (16.x) under kompatibilitetsnivå 160. Den använder flera aktiva planer per fråga för att hantera icke-enhetliga datadistributioner. Om din miljö inte kan använda den senaste kompatibilitetsnivån kan Query Store-hintar med hjälp av RECOMPILE-anvisningen användas på alla stödda kompatibilitetsnivåer.
• Query Store-hints åsidosätter Databasmotorns standardbeteende för frågeplan. Du bör bara använda Query Store-tips när det är nödvändigt för att åtgärda prestandarelaterade problem.
• Du bör omvärdera Query Store-tips, tips på uttalsnivå, planguider och tvingade planer för Query Store när datavolym och distribution förändras och under databasmigrationsprojekt. Ändringar i datavolym och -distribution kan leda till att Query Store hints genererar suboptimala exekveringsplaner.

Lagrade procedurer för Query Store-hintsystemet

Om du vill skapa eller uppdatera tips använder du sys.sp_query_store_set_hints. Tips ska anges i ett giltigt strängformat N'OPTION (...)'.

• När du skapar ett Query Store-tips skapas ett nytt Query Store-tips om det inte finns någon Query Store-ledtråd för en viss query_id.
• När du skapar eller uppdaterar ett Query Store-tips, om det redan finns ett Query Store-tips för en specifik query_idåsidosätter det senaste angivna värdet tidigare angivna värden för den associerade frågan.
• Om det inte finns någon query_id utlöses ett fel.

En fullständig lista över tips som stöds som ett Query Store-tips finns i sys.sp_query_store_set_hints.

Om du vill ta bort tips som är associerade med en query_idanvänder du sys.sp_query_store_clear_hints.

XML-attribut för exekveringsplan

När tips tillämpas visas följande resultatuppsättning i elementet StmtSimple i körningsplanen i XML-format:

Query Store-tips och funktionskompatibilitet

• Query Store-hintar åsidosätter andra hårdkodade uttalsnivå-hintar och planguider.
• Förutom tipset ABORT_QUERY_EXECUTION körs alltid frågor med Query Store-tips. Motsatta Query Store-tips ignoreras som annars skulle orsaka ett fel.
• Om ledtrådar från Query Store motsäger varandra blockeras inte frågekörningen i databasmotorn och ledtråden från Query Store tillämpas inte.
• Query Store-hints stöds inte för uttryck som kvalificerar sig för enkel parameterisering.
• Tipset RECOMPILE är inte kompatibelt med tvingad parameterisering som angetts på databasnivå. Om en databas har framtvingad parameteriseringsuppsättning och tipset RECOMPILE är en del av frågearkivets tips för en fråga ignorerar databasmotorn tipset RECOMPILE och tillämpar andra tips om de anges.
  • Databasmotorn utfärdar en varning (felkod 12461) som anger att tipset RECOMPILE ignorerades.
  • Mer information om användningsfallsöverväganden för tvingad parameterisering finns i Riktlinjer för användning av tvingad parameterisering.
• Manuellt skapade Query Store-tips är undantagna från rensning av Query Store. Tipset och frågan rensas inte av principen för automatisk kvarhållningsinsamling.
  • Frågor kan tas bort manuellt av användare. Det tar också bort det associerade Query Store-tipset.
  • Query Store-hints som automatiskt genereras av CE Feedback omfattas av rensning genom den automatiska kvarhållningen av inspelningspolicyn.
  • DOP-feedback och minnesbidragsfeedback formar frågebeteendet utan att använda Query Store-tips. När frågor rensas av principen för automatisk kvarhållningsinsamling rensas även DOP-feedback och feedbackdata för minnesbeviljande.
  • Om du skapar samma Query Store-hint som CE-feedback implementeras manuellt, är frågan med denna hint inte längre föremål för att rensas bort av den automatiska kvarhållningspolicyn.

Query Store-anvisningar och sekundära repliker

Frågearkivtips har ingen effekt på sekundära repliker, om inte Query Store är aktiverat för dessa repliker. Mer information finns i Query Store för läsbara sekundärfiler.

• I SQL Server 2022 (16.x) och tidigare versioner kan Query Store-tips endast tillämpas på den primära repliken.
• I SQL Server 2022 (16.x) och senare versioner, när Query Store för sekundära repliker är aktiverat, kan Query Store-tips tillämpas på sekundära repliker i tillgänglighetsgrupper.
• Du kan lägga till ett Query Store-tips som endast gäller för en specifik replikgrupp när du har Query Store för sekundära repliker aktiverat. Det gör du genom att använda parametern @replica_group_id när du anropar sys.sp_query_store_set_query_hints. Däremot kan du ta bort ett Query Store-tips från en specifik replikgrupp med hjälp av sys.sp_query_store_clear_query_hints.
• Hitta de tillgängliga replikgrupperna genom att fråga sys.query_store_replicas.
• Hitta planer som tvingas på sekundära repliker med sys.query_store_plan_forcing_locations.

Examples

A. Demo av tips för Query Store

Följande genomgång av Query Store-tips i Azure SQL Database använder en importerad databas via en BACPAC-fil (.bacpac). Lär dig hur du importerar en ny databas till en Azure SQL Database-server i Snabbstart: Importera en bacpac-fil till en databas i Azure SQL Database eller 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. Identifiera en fråga i Query Store

Följande exempel frågar sys.query_store_query_text och sys.query_store_query för att returnera query_id för ett kört frågetextfragment.

I den här demonstrationen finns frågan som vi försöker finjustera i SalesLT exempeldatabas:

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;

Query Store återspeglar inte omedelbart frågedata till sina systemvyer.

Identifiera frågan i frågearkivets systemkatalogvyer:

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

I följande exempel identifierades det tidigare frågeexemplet i SalesLT-databasen som query_id 39.

När du har identifierat det använder du tipset för att framtvinga en maximal minnesanvändningsstorlek i procent av den konfigurerade minnesgränsen för query_id:

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

Du kan också använda frågeanvisningar med följande syntax, till exempel alternativet att framtvinga äldre kardinalitetsestimator:

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

Du kan använda flera frågetips med en kommaavgränsad lista:

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

Granska Query Store-tipset 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;

Ta slutligen bort tipset från query_id 39 med hjälp av sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Related content

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• Spara en körningsplan i XML-format
• Visa och spara exekveringsplaner
• Frågetips (Transact-SQL)
• Metodtips för övervakning av arbetsbelastningar med Query Store
• Query Store tipsar om metodtips
• Övervaka prestanda med hjälp av Query Store-
• Konfigurera maxgraden av parallellitet (MAXDOP) i Azure SQL Database