Sdílet prostřednictvím

Vynucení optimalizovaného plánu pomocí úložiště dotazů

Platí pro: SQL Server 2022 (16.x) Azure SQL DatabaseSQL Database pro Microsoft Fabric

Optimalizace dotazů je vícefázový proces generování "dostatečně dobrého" plánu provádění dotazů. V některých případech může kompilace dotazů, součást optimalizace dotazů, představovat velké procento celkové doby provádění dotazů a spotřebovávat významné systémové prostředky. Optimalizované plánování je součástí inteligentní řady funkcí pro zpracování dotazů. Optimalizovaný plán vynucuje snížení režie kompilace pro opakování vynucených dotazů a vyžaduje povolení úložiště dotazů a v režimu čtení zápisu. Jakmile se vygeneruje plán spouštění dotazů, uloží se konkrétní kroky kompilace pro opakované použití jako skript pro přehrání optimalizace. Skript pro přehrání optimalizace je uložen jako součást komprimovaného souboru XML plánu showplan v úložiště dotazů, ve skrytém atributu OptimizationReplay.

Optimalizovaný plán pro vynucení implementace

Když dotaz poprvé projde procesem kompilace, prahová hodnota založená na odhadu času stráveného optimalizací (na základě vstupního stromu optimalizátoru dotazů) určuje, jestli se vytvoří skript pro přehrání optimalizace.

Po dokončení kompilace bude k dispozici několik metrik modulu runtime pro vyhodnocení, zda byl předchozí odhad správný. Pokud databázový systém potvrdí, že došlo k překročení prahové hodnoty, je skript pro zopakování optimalizace způsobilý k uložení. Mezi tyto metriky modulu runtime patří počet objektů, ke které se přistupuje, počet spojení, počet úloh optimalizace spuštěných během optimalizace a skutečný čas optimalizace.

Porovnává se potenciální přínos použití skriptu pro přehrání optimalizace s režií spojenou s ukládáním tohoto skriptu. Odhad relativního času k přehrání skriptu optimalizace se porovná s časem stráveným během provádění normálního optimalizačního procesu. Tento odhad vychází z počtu úloh optimalizace uložených ve skriptu pro přehrání optimalizace a počtu úloh optimalizace spuštěných během normální kompilace. Pokud provedení skriptu pro opakovanou optimalizaci vykazuje významnou výhodu při zkrácení doby kompilace, skript pro opakovanou optimalizaci je zachován.

Considerations

Pokud je povolená optimalizovaná funkce vynucení plánu, kritéria způsobilosti pro optimalizované vynucení plánu jsou:

  1. Nárok mají pouze plány dotazů, které procházejí úplnou optimalizací, které je možné ověřit přítomností StatementOptmLevel="FULL" vlastnosti.

  2. Příkazy s nápovědou rekompilu a distribuovanými dotazy nejsou způsobilé.

Pokud však úložiště dotazů nezávisle zachytí plán dotazu, který byl vymezen optimalizovaným vynucením plánu, vytvoří se skript pro opakované sestavení optimalizace pro druhou rekompilaci stejného dotazu podle standardních událostí rekompilace. Přečtěte si další informace o rekompilaci v tématu Rekompilace plánů provádění.

I když byl vygenerován skript pro přehrání optimalizace, nemusí být uložen v úložišti dotazů, pokud nejsou splněna kritéria zásad zachycení nakonfigurovaných v úložišti dotazů, zejména počet spuštění tohoto příkazu a jeho kumulovaná doba kompilace a doba spuštění. V tomto případě se neplatný optimalizovaný skript pro přehrání odebere asynchronně z paměti.

Povolení a zakázání optimalizovaného prosazení plánu

Můžete povolit nebo zakázat optimalizovaný plán vynucení pro databázi. Pokud je pro databázi povolené optimalizované vynucení plánu, můžete ho zakázat pro jednotlivé dotazy pomocí nápovědy DISABLE_OPTIMIZED_PLAN_FORCING k dotazu. Můžete také zakázat optimalizovaný plán vynucení pro plán dotazu, který je vynucený v úložišti dotazů.

Povolení nebo zakázání vynucení optimalizovaného plánu pro databázi

Optimalizované vynucení plánu je ve výchozím nastavení povolené pro nové databáze vytvořené v SQL Serveru 2022 (16.x) a vyšší. Úložiště dotazů musí být povolené pro každou databázi, ve které se používá optimalizovaný plán vynucení. Upgradované instance s existujícími databázemi nebo databázemi obnovenými z nižší verze SQL Serveru mají ve výchozím nastavení optimalizovaný plán vynucení.

Pokud chcete povolit optimalizovaný plán vynucení na úrovni databáze, použijte konfiguraci s vymezeným oborem ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON databáze. Úložiště dotazů musíte povolit, pokud ještě není povolené. Vyhledejte ukázkový kód v příkladu A nebo si přečtěte další informace o úložišti dotazů při monitorování výkonu pomocí úložiště dotazů.

Chcete-li zakázat optimalizované nucení plánu na úrovni databáze, použijte databázovou konfiguraci s vymezením oboru ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.

Zakázání vynucení optimalizovaného plánu pomocí pokynu k dotazu

Pokud je v databázi povolena funkce optimalizovaného vynucení plánu, můžete pomocí nápovědy dotazu zakázat optimalizované vynucení plánu pro jednotlivý dotaz.

Vyhledejte příklad použití tohoto tipu dotazu v příkladu E.

Vynucení plánu pomocí úložiště dotazů, ale zakázání automatického vynucení optimalizovaného plánu

Procedura sp_query_store_force_plan obsahuje disable_optimized_plan_forcing parametr. Aby bylo možné tento parametr použít, vyžaduje uložená procedura sp_query_store_force_plan další parametr. Další parametr je volána @replica_group_id. Ve výchozím nastavení má primární @replica_group_id hodnota jednu (1) i v případě, že neexistují žádné nakonfigurované sekundární repliky.

Najděte příklad použití příslušných parametrů pro uloženou proceduru v sp_query_store_force_plan C.

Zobrazení sys.query_store_plan katalogu obsahuje sloupce, které označují, jestli má plán přidružený skript pro přehrání optimalizace, a přidá do existujícího sloupce důvodu selhání nový stav specifický pro přidružený skript pro přehrání optimalizace. Další informace najdete v sys.query_store_plan.

Examples

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2025 nebo AdventureWorksDW2025, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .

A. Povolení úložiště dotazů a vynucení optimalizovaného plánu pro databázi

Následující kód povolí Úložiště dotazů na databázi, poté povolí optimalizované vynucení plánu v databázi. Přečtěte si další informace o možnostech povolení úložiště dotazů v možnostech ALTER DATABASE SET.

Před spuštěním kódu se připojte k příslušné uživatelské databázi.

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. Vyberte všechny dotazy, které mají skript pro přehrání optimalizace.

Následující ukázkový kód vybere všechny query_ids, které mají skript pro přehrání optimalizace v úložišti dotazů. Před spuštěním ukázkového kódu se připojte k příslušné uživatelské databázi.

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. Vynucení plánu a zakázání optimalizovaného způsobu vynucení plánu v úložišti dotazů

Následující kód vynutí plán v Query Store, ale zakáže optimalizované vynucování plánu. Před spuštěním následujícího kódu nahraďte @query_id a @plan_id nahraďte kombinací odpovídající vaší instanci. Uložená sp_query_store_force_plan procedura očekává, že @replica_group_id parametr je předán jako třetí hodnota parametru při pokusu o zakázání vynucení optimalizovaného plánu v úložišti dotazů. To lze použít k zakázání vynucení optimalizovaného plánu pro konkrétní vynucený plán na určité replice. Hodnota @replica_group_id = 1 slouží k zakázání funkce na primární replice.

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

Další informace najdete v sp_query_store_force_plan.

D. Vyberte všechny dotazy, ve kterých je vynucení optimalizovaného plánu zakázáno pomocí Query Store.

Následující příklad dotazuje všechny plány, které byly vynuceny v Query Store, kde je is_optimized_plan_forcing_disabled nastaveno na 1. Před spuštěním kódu se připojte k příslušné uživatelské databázi.

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. Zakázání optimalizovaného vynucení plánu pro dotaz

Následující příklad zakáže optimalizovaný plán vynucení dotazu pomocí DISABLE_OPTIMIZED_PLAN_FORCING dotazu.

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

Související obsah

  • Last updated on