Volitelná optimalizace plánu parametrů (OPPO)

Platí na: SQL Server 2025 (17.x) Azure SQL Database SQL database in Microsoft Fabric

Optimalizace volitelného plánu parametrů (OPPO) zlepšuje kvalitu plánu dotazů pro dotazy, které obsahují volitelné parametry. V těchto dotazech závisí optimální plán provádění na tom, jestli je NULL hodnota parametru v době provádění. Termín volitelné parametry odkazuje na konkrétní variantu problému s plánem citlivým na parametry (PSP), ve kterém hodnota parametru v době provádění určuje, zda dotaz vyžaduje hledání nebo kontrolu.

Přehled

Dotazy, které používají volitelné parametry, často obsahují predikáty, které podmíněně používají filtry na základě toho, jestli je zadaná hodnota parametru. Běžný vzor je následující:

SELECT column1,
       column2
FROM Table1
WHERE (column1 = @p
       OR @p IS NULL);

Když @p IS NOT NULL, je hledání v indexu col1 často nejúčinnějším plánem provedení. Když @p IS NULL se predikát vyhodnotí jako TRUE, může být skenování vhodnější. Bez OPPO musí databázový stroj SQL Serveru zkompilovat a uložit do mezipaměti jeden plán provádění, který je platný pro oba případy. Vzhledem k tomu, že plán založený na vyhledávání není platný @p IS NULL, optimalizátor často zvolí plán založený na prohledávání pro všechny výkony. Tato volba může vést k neefektivním rozhodnutím ohledně plánu a nadměrnému využití prostředků při selektivním provádění.

Tradiční techniky nápovědy, jako OPTIMIZE FOR například nejsou v tomto scénáři efektivní, protože plán musí zůstat správný pro oba stavy parametrů.

OPPO používá infrastrukturní systém optimalizace adaptivního plánu (Multiplan), který byl zaveden společně s optimalizací plánu citlivého na parametry (PSP - Parameter Sensitive Plan). Tato infrastruktura generuje a ukládá do mezipaměti více plánů provádění pro jeden příkaz, což umožňuje OPPO provádět různé předpoklady na základě hodnot parametrů použitých v dotazu.

Terminologie a jeho fungování

OPPO vychází z architektury optimalizace adaptivního plánu (Multiplan), která se používá také optimalizací plánu citlivého na parametry. Pomocí Multiplanu může databázový stroj vygenerovat a ukládat do mezipaměti více výkonových plánů pro jeden dotaz.

Když databázový stroj zjistí způsobilý volitelný vzor parametrů, vytvoří:

Plán dispečera
Jedna nebo více variant dotazů optimalizovaných pro konkrétní stav hodnoty parametru

V okamžiku provedení

Databázový stroj vyhodnotí hodnotu parametru.
Multiplan dispatcher vybere příslušnou variantu dotazu.
Vybraná varianta dotazu se spustí.

Jakmile databázový stroj vybere variantu dotazu, zjednodušuje predikáty na základě skutečné hodnoty parametru. Představte si následující výraz:

@p1 IS NULL

V tomto příkladu je výraz zjednodušený na konstantní výsledek pro vybranou variantu. Toto skládání konstantních výsledků umožňuje optimalizátoru generovat plány provádění, které nejsou platné jako jediný opakovaně použitelný plán.

Výběrem plánů tímto způsobem umožňuje OPPO efektivní spouštění pro různé stavy parametrů, aniž by bylo nutné přepisovat dotazy nebo pokyny k ručnímu dotazu.

Optimalizace OPPO a PSP řeší různé varianty problémů s plánováním souvisejících s parametry.

Optimalizace PSP vybírá plány na základě odhadovaných rozdílů kardinality pro predikáty rovnosti nebo rozsahu.
OPPO vybere plány na základě toho, zda je NULLhodnota parametru .

Jeden dotaz může těžit z obou nebo jedné funkce v závislosti na zahrnutých predikátech.

Podporované vzory dotazů

Volitelná optimalizace plánu parametrů se vztahuje na dotazy, ve kterých NULL kontroly parametrů ovlivňují platnost plánu provádění. Představte si například webový formulář aplikace pro realitní společnost, která umožňuje volitelné filtrování počtu ložnic pro konkrétní nabídku. OPPO se vztahuje na disjunktivní volitelné predikáty parametrů, například:

SELECT *
FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

I když značky parametrů mohou číst @bedrooms = 10 parametr a víte, že kardinalita pro počet ložnic bude pravděpodobně velmi nízká, optimalizátor nevygeneruje plán, který vyhledává v indexu existujícím ve sloupci ložnice, protože to není platný plán pro případ, kdy je @bedroomsNULL. Vygenerovaný plán nezahrnuje kontrolu indexu.

Představte si, že byste tento dotaz mohli přepsat jako dva samostatné příkazy. V závislosti na hodnotě parametru za běhu byste mohli vyhodnotit následující příklad:

IF @bedrooms IS NULL
    SELECT *
    FROM Properties;
ELSE
    SELECT *
    FROM Properties
    WHERE bedrooms = @bedrooms;

Tato funkce toho může dosáhnout pomocí infrastruktury Multiplan, která umožňuje vytvořit plán dispečera , který odesílá variantu dotazu.

OPPO vloží do metadat plánu systémem vygenerovanou PLAN PER VALUE nápovědu dotazu (optional_predicate), která přidruží každou variantu dotazu k jeho stavu parametru. Tento tip je systémově generovaný a vložený do textu plánového dotazu. Tento tip není platný pro použití aplikací nebo ruční použití.

Pokračování v předchozím příkladu

SELECT *
FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

OPPO může vygenerovat dvě varianty dotazu, které můžou mít v rámci showplan XML přidané následující atributy:

@bedrooms je NULL. Varianta dotazu slučuje predikáty na základě hodnoty parametru, což umožňuje vygenerovat plán založený na skenování.

SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234; QueryVariantID = 1, optional_predicate(@bedrooms is NULL))
@bedrooms IS NOT NULL

SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234; QueryVariantID = 2, optional_predicate(@bedrooms is NULL))

Použít volitelnou optimalizaci plánu parametrů

K povolení OPPO pro databázi jsou vyžadovány následující požadavky:

Databáze musí používat úroveň kompatibility 170.
Konfigurace OPTIONAL_PARAMETER_OPTIMIZATION s vymezeným oborem databáze musí být povolena.

Ve OPTIONAL_PARAMETER_OPTIMIZATION výchozím nastavení je povolená konfigurace s oborem databáze, takže databáze s úrovní kompatibility 170 (výchozí hodnota v SQL Serveru 2025 (17.x)) používá ve výchozím nastavení OPPO.

Spuštěním následujících příkazů můžete zajistit, aby databáze používala OPPO v SQL Serveru 2025 (17.x):

ALTER DATABASE [<database-name-placeholder>]
SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Pokud chcete zakázat OPPO pro databázi, zakažte konfiguraci v rámci databáze OPTIONAL_PARAMETER_OPTIMIZATION:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Použití volitelné optimalizace plánu parametrů prostřednictvím nápovědy k dotazům

Pomocí indikátoru DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION zakažte funkci OPPO pro daný dotaz. Zadejte nápovědu prostřednictvím klauzule USE HINT. Další informace najdete v tématu nápovědy k dotazům.

Tato nápověda funguje pod libovolnou úrovní kompatibility a přepíše konfiguraci omezenou na databázi OPTIONAL_PARAMETER_OPTIMIZATION.

Zadejte nápovědu DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION dotazu přímo v dotazu nebo prostřednictvím tipů úložiště dotazů.

Rozšířené události

Pro řešení potíží a diagnostiku použijte následující rozšířené události. Tyto události nejsou nutné k používání této funkce.

optional_parameter_optimization_skipped_reason: Nastane, když OPPO rozhodne, že dotaz nemá nárok na optimalizaci. Tato rozšířená událost se řídí stejným vzorem parameter_sensitive_plan_optimization_skipped_reason jako událost, kterou používá optimalizace PSP. Vzhledem k tomu, že dotaz může generovat jak optimalizace PSP, tak varianty dotazů OPPO, zkontrolujte obě události, abyste pochopili, proč se jedna či druhá funkce nespustila nebo neaktivovala.

Následující dotaz ukazuje všechny možné důvody, proč byl psp vynechán:
```
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;
```
query_with_optional_parameter_predicate: Tato rozšířená událost se řídí stejným vzorem query_with_parameter_sensitivity jako událost, kterou používá optimalizace PSP. Obsahuje další pole, která jsou k dispozici ve vylepšeních optimalizace PSP.

Zobrazí se tato pole:
- počet predikátů, které funkce považovala za zajímavé,
- další podrobnosti ve formátu JSON týkající se zajímavých predikátů a
- zda je pro predikát nebo predikáty zajištěna podpora pro OPPO.

Remarks

ShowPlan XML pro variantu dotazu vypadá podobně jako v následujícím příkladu. Predikáty, které funkce vybere, mají odpovídající informace přidané do nápovědy PLAN PER VALUE (optional_predicate).

<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

Příklad výstupu z události query_with_optional_parameter_predicate rozšířené:

Field	Value
`optional_parameter_optimization_supported`	True
`optional_parameter_predicate_count`	3
`predicate_details`	`{"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}`
`query_type`	193

Zjistit způsobilost a omezení

OPPO se vztahuje pouze na dotazy, které splňují podmínky pro optimalizaci Multiplan. Tato funkce se nepoužije ve scénářích, které zahrnují:

Dotazy, které místo parametrů používají místní proměnné
Dotazy zkompilované pomocí OPTION (RECOMPILE)
Dotazy spouštěné pomocí SET ANSI_NULLS OFF
Automaticky parametrizované příkazy

Průvodce architekturou zpracování dotazů
Kompilace prováděcích plánů
Opětovné použití parametrů a plánu provádění
Jednoduchá parametrizace
Vynucená parametrizace
Rady dotazů (Transact-SQL)
inteligentní zpracování dotazů v databázích SQL
Citlivost parametru
ÚPRAVA KONFIGURACE S ROZSAHEM DATABÁZE (Transact-SQL)

Váš názor

Byla tato stránka užitečná?

Last updated on 2026-01-07