Volitelná optimalizace plánu parametrů (OPPO)

Platí pro:Platí pro: SQL Server 2025 (17.x) Azure SQL Database SQL databáze v Microsoft Fabric

Termín volitelné parametry odkazuje na konkrétní variantu problému s plánem citlivým na parametry (PSP), ve kterém citlivá hodnota parametru, která existuje během provádění dotazu, určuje, jestli potřebujeme provést hledání nebo prohledat tabulku. Jednoduchý příklad by vypadal přibližně takto:

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

V tomto příkladu SQL Server vždy zvolí plán, který prohledává tabulku Table1, i když existuje index na Table1(col1). Plán hledání nemusí být možný s hodnotami NULL. Techniky nápovědy k dotazům, například OPTIMIZE FOR, nemusí být užitečné pro tento typ problému s PSP, protože aktuálně neexistuje operátor, který dynamicky mění prohledávání indexu na sekvenci během vykonávání. Tento druh kombinace seek>-scan za běhu nemusí být efektivní, protože odhady kardinality z výše uvedeného operátoru by pravděpodobně nebyly přesné. Výsledkem jsou neefektivní volby plánu a nadměrné přidělení paměti pro složitější dotazy s podobnými vzory dotazů.

Funkce Optimalizace volitelného plánu parametrů (OPPO) používá infrastrukturu adaptivní optimalizace plánu (Multiplan), která byla zavedena s vylepšením optimalizace plánu citlivého na parametry, která generuje více plánů z jednoho příkazu. Tato funkce umožňuje provádět různé předpoklady v závislosti na hodnotách parametrů použitých v dotazu. Během doby provádění dotazů vybere OPPO příslušný plán:

kde je hodnota parametru IS NOT NULL, používá plán vyhledávání nebo něco optimálnějšího než plán úplného procházení.
Pokud je hodnota parametru NULL, používá se skenovací plán.

V rámci řady funkcí optimalizace adaptivního plánu, která zahrnuje optimalizaci plánu citlivého na parametry, poskytuje OPPO řešení pro druhou komponentu sady funkcí Multiplan, která pokrývá možnosti dynamického vyhledávání.

Predikáty rovnosti
```
WHERE column1 = @p
```

Dynamické vyhledávání

WHERE (column1 = @p1 OR @p1 IS NULL)
  AND (column2 = @p2 OR @p2 IS NOT NULL)

Terminologie a jeho fungování

Term	Description
Výraz dispečeru	Tento výraz vyhodnotí kardinalitu predikátů na základě hodnot parametrů modulu runtime a směruje provádění do různých variant dotazů.
Plán dispečera	Plán obsahující výraz dispečera se ukládá do mezipaměti původního dotazu. Plán dispečera je v podstatě kolekce predikátů, které byly vybrány funkcí, s několika dalšími podrobnostmi. V rámci dispečerského plánu jsou pro každý vybraný predikát zahrnuty podrobnosti, jako jsou hodnoty horní a dolní hranice. Tyto hodnoty se používají k rozdělení hodnot parametrů do různých kbelíků nebo oblastí. Plán dispečera obsahuje také statistiky, které byly použity k výpočtu hodnot hranic.
Varianta dotazu	Plán dispečera vyhodnocuje kardinalitu predikátů na základě hodnot parametrů modulu runtime, rozdělí je do kategorií a vygeneruje samostatné podřízené dotazy, které se mají spustit. Tyto podřízené dotazy se nazývají varianty dotazů. Varianty dotazů mají v mezipaměti plánů a úložišti dotazů vlastní plány. Jinými slovy, pomocí různých variant dotazů dosáhneme cíle více plánů pro jeden dotaz.

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. Běžným antipatternem může být vyjádření volitelného filtru jako:

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

I když je parametr @bedrooms = 10 zjištěn pomocí značek parametrů a víme, že kardinalita pro počet ložnic je pravděpodobně velmi nízká, optimalizátor nevytvoří plán pro hledání indexu existujícího ve sloupci ložnice, protože to není platný plán pro případ, kde @bedrooms je NULL. Vygenerovaný plán nezahrnuje kontrolu indexu.

Představte si, jestli by se to dalo přepsat jako dva samostatné příkazy. V závislosti na běhové hodnotě parametru bychom mohli posoudit něco takového:

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

Toho můžeme dosáhnout pomocí infrastruktury optimalizace adaptivního plánu, která umožňuje vytvořit plán dispečera, který odesílá dvě varianty dotazu.

Podobně jako rozsah kardinality predikátu, který optimalizace PSP používá, OPPO vkládá systémově použitelný náznak dotazu do textu dotazu plánu. Tento tip není platný pro použití aplikací nebo pokud se ho pokusíte použít sami.

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 upravila původní dotaz tak, aby dosáhla skenovacího plánu.

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.

Konfigurace s rozsahem na úrovni databáze OPTIONAL_PARAMETER_OPTIMIZATION je ve výchozím nastavení povolená. To znamená, že databáze používající úroveň kompatibility 170 (výchozí hodnota v SQL Serveru 2025) 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:

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Pokud chcete zakázat volitelnou optimalizaci plánu parametrů pro databázi, zakažte konfiguraci s oborem OPTIONAL_PARAMETER_OPTIMIZATION databáze:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Použití volitelné optimalizace plánu parametrů prostřednictvím tipů pro dotazy

Pomocí nápovědy DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION dotazu můžete zakázat optimalizaci volitelného plánu parametrů pro daný dotaz. Nápovědy musí být zadány prostřednictvím USE HINT klauzule. Další informace najdete v tématu nápovědy k dotazům.

Nápovědy fungují na jakékoli úrovni kompatibility a přepíší konfiguraci v rozsahu databáze OPTIONAL_PARAMETER_OPTIMIZATION.

Náznak DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION k dotazu lze zadat přímo v dotazu nebo prostřednictvím hintů Query Store.

Rozšířené události

optional_parameter_optimization_skipped_reason: Nastane, když OPPO rozhodne, že dotaz nemá nárok na optimalizaci. Tato rozšířená událost odpovídá stejnému vzoru jako událost parameter_sensitive_plan_optimization_skipped_reason, kterou využívá optimalizace PSP. Vzhledem k tomu, že dotaz může generovat jak optimalizaci PSP, tak varianty dotazů OPPO, měli byste zkontrolovat obě události, abyste pochopili, proč se jedna nebo ani jedna z těchto funkcí 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: Rozšířená událost se řídí stejným vzorem jako událost query_with_parameter_sensitivity, kterou používá optimalizace PSP. Zahrnuje další pole, která jsou k dispozici ve vylepšeních optimalizace PSP. Tyto vylepšení zahrnují zobrazení počtu predikátů, které funkce shledala zajímavými, podrobnější informace ve formátu JSON ohledně těchto zajímavých predikátů, a také informaci, zda je OPPO podporován pro daný predikát nebo predikáty.

Remarks

ShowPlan XML pro variantu dotazu by vypadal podobně jako v následujícím příkladu, kde predikáty, které byly vybrány, mají příslušné informace přidané do plánu PER VALUE, optional_predicate tip.

<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 rozšířené události query_with_optional_parameter_predicate

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

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 2025-11-18