Optimerad plan framtvingas med Query Store

Gäller för: SQL Server 2022 (16.x) Azure SQL DatabaseSQL-databas i Microsoft Fabric

Frågeoptimering är en process i flera faser för att generera en "tillräckligt bra" frågekörningsplan. I vissa fall kan frågekompilering, som är en del av frågeoptimeringen, representera en stor procentandel av den totala frågekörningstiden och förbruka betydande systemresurser. Optimerad plan framtvingning är en del av den intelligenta frågebehandlingsfamiljen av funktioner. Optimering av plan tvingning minskar kompileringskostnaderna för att upprepa tvångsfrågor och kräver att Query Store är aktiverad och i läs/skrivläge. När frågekörningsplanen har genererats lagras specifika kompileringssteg för återanvändning som ett optimeringsreprisskript. Ett optimeringsreprisskript lagras som en del av den komprimerade showplan-XML:en i Query Storei ett dolt OptimizationReplay-attribut.

Optimerad plan tvingar implementering

När en fråga först går igenom kompileringsprocessen avgör ett tröskelvärde baserat på uppskattningen av den tid som ägnas åt optimering (baserat på frågeoptimerarens indataträd) om ett optimeringsreprisskript skapas.

När kompilering har slutförts blir flera körningsmått tillgängliga för att bedöma om den tidigare uppskattningen var korrekt. Om databasmotorn bekräftar att tröskelvärdet har överskridits är optimeringsreprisskriptet berättigat till beständighet. Dessa körningsmått omfattar antalet objekt som används, antalet kopplingar, antalet optimeringsuppgifter som körs under optimeringen och den faktiska optimeringstiden.

Den potentiella fördelen med att använda ett optimeringsreprisskript jämförs också med omkostnaderna med att lagra optimeringsreprisskriptet. En uppskattning av den relativa tiden för att spela upp optimeringsreprisskriptet jämförs med den tid som användes för att utföra den normala optimeringsprocessen. Den här uppskattningen baseras på antalet optimeringsuppgifter som lagras i optimeringsreprisskriptet och antalet optimeringsuppgifter som utförs under normal kompilering. Om uppspelning av optimeringsreprisskriptet visar betydande fördelar med att minska kompileringstiden sparas optimeringsreprisskriptet.

Considerations

När funktionen för att tvinga optimerad plan är aktiverad är berättigandekriterierna för att framtvinga en optimerad plan:

1. Endast frågeplaner som genomgår fullständig optimering är berättigade, vilket kan verifieras genom förekomsten av StatementOptmLevel="FULL" egenskapen.

2. Satser med RECOMPILE-hint och distribuerade frågor är inte tillämpliga.

Men om Query Store oberoende samlar in en frågeplan som begränsades av optimerad plan framtvingning, skapas optimeringsreprisskriptet för en andra omkompilering av samma fråga, med förbehåll för standardomkompileringshändelser. Läs mer om återkompilering i Återkompilera exekveringsplaner.

Även om ett optimeringsreprisskript genererades kanske det inte sparas i Frågearkivet om Frågearkivets konfigurerade kriterier för policyer för fångst inte uppfylls, särskilt med hänsyn till antalet körningar av instruktionen och dess sammanlagda kompilerings- och exekveringstider. I det här fallet tas det ogiltiga optimeringsreprisskriptet bort från minnet asynkront.

Aktivera och inaktivera framtvingande av optimerad plan

Du kan aktivera eller inaktivera optimerad planförvaltning för en databas. När tvingad optimering av planer är aktiverad för en databas kan du stänga av den för enskilda frågor med hjälp av frågeanvisningen DISABLE_OPTIMIZED_PLAN_FORCING. Du kan också inaktivera optimerad planstyrning av en frågeplan som tvingas i Query Store.

Aktivera eller inaktivera optimerad planstyrning för en databas

Optimerad plan framtvingning är aktiverad som standard för nya databaser som skapats i SQL Server 2022 (16.x) och senare. Query Store måste aktiveras för varje databas där framtvingande av optimerad plan används. Uppgraderade instanser med befintliga databaser, eller databaser som återställts från en lägre version av SQL Server, har optimerad plan framtvingning aktiverad som standard.

Om du vill aktivera optimerad plan framtvingning på databasnivå använder du den databasomfattande konfigurationen ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Du måste aktivera Query Store om det inte redan är aktiverat. Hitta exempelkod i exempel A eller läs mer om Query Store i Övervaka prestanda med hjälp av Query Store.

Om du vill inaktivera optimerad planforcering på databasnivå använder du konfigurationen ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF med databasomfattning.

Inaktivera framtvingad optimeringsplan med en frågehint

När funktionen för att tvinga optimerad plan är aktiverad i en databas kan du inaktivera optimering av plan för en enskild fråga med hjälp av DISABLE_OPTIMIZED_PLAN_FORCING.

Hitta ett exempel på hur du använder det här frågetipset i Exempel E.

Tvinga en plan med Query Store, men inaktivera optimerad planstyrning

Den sp_query_store_force_plan proceduren innehåller en disable_optimized_plan_forcing parameter. För att kunna använda den här parametern krävs en extra parameter av den sp_query_store_force_plan lagrade proceduren. Den extra parametern kallas @replica_group_id. Som standard har den primära @replica_group_id värdet en (1) även om det inte finns några konfigurerade sekundära repliker.

Hitta ett exempel på hur du tillämpar lämpliga parametrar på den sp_query_store_force_plan lagrade proceduren i exempel C.

Katalogvyn sys.query_store_plan innehåller kolumner som anger om planen har ett associerat optimeringsreprisskript och lägger till ett nytt tillstånd i den befintliga felorsakskolumnen som är specifik för tillhörande optimeringsreprisskript. Läs mer i sys.query_store_plan.

Examples

Kodexemplen i den här artikeln använder AdventureWorks2025- eller AdventureWorksDW2025-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

A. Aktivera Query Store och optimerad planforcering för en databas

Följande kod aktiverar Query Store på en databas och aktiverar sedan optimerad planforcing på databasen. Läs mer om alternativ som aktiverar Query Store i ALTER DATABASE SET-alternativ.

Innan du kör koden ansluter du till rätt användardatabas.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Markera alla frågor som har ett optimeringsreprisskript

Följande exempelkod väljer alla query_ids som har ett optimeringsreprisskript i Query Store. Anslut till lämplig användardatabas innan du kör exempelkoden.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Framtvinga en plan och inaktivera tvingande av optimerad plan i Query Store

Den följande koden tvingar en plan i Query Store, men inaktiverar tvingandet av en optimerad plan. Innan du kör följande kod, ersätt @query_id och @plan_id med en kombination som är lämplig för din instans. Den sp_query_store_force_plan lagrade proceduren förväntar att parametern @replica_group_id överlämnas som det tredje värdet för att inaktivera tvingande optimeringsplaner i Query Store. Detta kan användas för att inaktivera optimerad planering för en särskild framtvingad plan på en specifik replika. Värdet @replica_group_id = 1 används för att inaktivera funktionen på den primära repliken.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

Läs mer i sp_query_store_force_plan.

D. Markera alla frågeuttryck där Query Store har inaktiverat framtvingande av optimerad plan

I följande exempel hämtas alla planer som tvingats fram i Query Store där is_optimized_plan_forcing_disabled är inställt på 1. Innan du kör koden ansluter du till rätt användardatabas.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Inaktivera tvingande av optimerad plan för en fråga

I följande exempel inaktiveras optimerad plan för att tvinga fram en fråga med hjälp av DISABLE_OPTIMIZED_PLAN_FORCING.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Relaterat innehåll

• sys.query_store_plan (Transact-SQL)
• ÄNDRA DATABASOMFATTNINGSKONFIGURATION (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• Intelligent frågebearbetning i SQL-databaser