Optimalizace plánu citlivého na parametry

Platí pro: SQL Server 2022 (16.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Optimalizace plánu PSP (Parameter Sensitive Plan) je součástí souboru funkcí inteligentního zpracování dotazů. Řeší scénář, kdy jeden plán uložený v mezipaměti parametrizovaného dotazu není optimální pro všechny možné příchozí hodnoty parametrů. Jedná se o případ s nejednotnými distribucemi dat. Další informace najdete v tématu Citlivost parametrů a parametry a opětovné použití plánu spuštění.

Další informace o existujících alternativních řešeních pro tento scénář problému najdete tady:

• Zkoumání a řešení problémů citlivých na parametry
• Parametry a opětovné použití plánu vykonávání
• Dotazy s problémy s plánem citlivým na parametry (PSP)

Optimalizace PSP automaticky umožňuje více aktivních plánů uložených v mezipaměti pro jeden parametrizovaný příkaz. Plány spouštění uložené v mezipaměti odpovídají různým velikostem dat na základě hodnot parametrů modulu runtime poskytnutého zákazníkem.

Vysvětlení parametrizace

V databázovém stroji SQL Serveru používáním parametrů nebo značek parametrů v příkazech Transact-SQL (T-SQL) zvyšuje schopnost relačního stroje přiřadit nové příkazy T-SQL se stávajícími, dříve zkompilovanými plány provádění, a podpořit opětovné využití plánů. Další informace naleznete v tématu Jednoduchá parametrizace.

Můžete také přepsat výchozí chování jednoduché parametrizace SQL Serveru tím, že určíte, že všechny SELECT, INSERT, UPDATE, a DELETE příkazy v databázi jsou parametrizovány, s výhradou určitých omezení. Další informace naleznete v tématu Vynucené parametrizace.

Implementace optimalizace PSP

Během počáteční kompilace histogramy statistiky sloupců identifikují neuniformní rozdělení a vyhodnocují až tři nejrizikovější parametrizované predikáty ze všech dostupných predikátů. Jinými slovy, pokud více predikátů v rámci stejného dotazu splňuje kritéria, optimalizace PSP zvolí první tři. Funkce PSP omezuje počet vyhodnocovaných predikátů, aby se zabránilo zahlcení plánovací mezipaměti a Úložiště dotazů (pokud je Úložiště dotazů povoleno) příliš velkým množstvím plánů.

V případě způsobilých plánů vytvoří počáteční kompilace plán dispečera, který obsahuje optimalizační logiku PSP nazývanou výraz dispečera. Plán dispečeru se mapuje na varianty dotazů na základě predikátů hranic hodnot rozsahu kardinality.

Terminology

Výraz dispečeru

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ů runtime, rozdělí tyto hodnoty do skupin a vygeneruje samostatné podřízené dotazy pro kompilaci a spuštění. Tyto podřízené dotazy se nazývají varianty dotazů. Varianty dotazů mají vlastní plány v mezipaměti plánů a úložišti dotazů.

Rozsah kardinality predikátu

Za běhu se kardinalita jednotlivých predikátů vyhodnocuje na základě hodnot parametrů modulu runtime. Dispečer rozdělí hodnoty kardinality do tří kategorií rozsahů kardinality predikátu v době kompilace. Například funkce optimalizace PSP může vytvořit tři oblasti, které by představovaly oblasti nízké, střední a vysoké kardinality, jak je znázorněno v následujícím diagramu.

Jinými slovy, pokud je parametrizovaný dotaz původně zkompilován, funkce optimalizace PSP generuje plán prostředí označovaný jako plán dispečera. Výraz dispečera má logiku, která zařazuje dotazy do dotazových variant na základě běhových hodnot parametrů. Při zahájení skutečného spuštění provede dispečer dva kroky, které zahrnují:

• dispečer vyhodnotí dispečerský výraz pro danou sadu parametrů, aby vypočítal rozsah kardinality.

• dispečer tyto oblasti mapuje na konkrétní varianty dotazů a zkompiluje a spustí varianty. Vzhledem k tomu, že má několik variant dotazů, funkce optimalizace PSP dosahuje více plánů pro jeden dotaz.

Hranice rozsahu kardinality lze zobrazit v souboru ShowPlan XML plánu odeslání:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

K SQL příkazu v ShowPlan XML varianty dotazu je připojena nápověda generovaná optimalizací PSP. Nápovědu nelze použít přímo a při ručním přidání se neanalyduje. Nápověda obsahuje následující prvky:

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) )

• ObjectID pochází z modulu (tedy uložená procedura, funkce, trigger), jehož součástí je aktuální příkaz; s předpokladem, že příkaz byl generován z modulu. Pokud je příkaz výsledkem dynamického nebo ad hoc SQL (tj sp_executesql. ) ObjectID element rovná 0.
• QueryVariantID je zhruba ekvivalentní ke kombinaci rozsahů ve všech predikátech, které optimalizace PSP vybrala. Pokud má například dotaz dva predikáty, které mají nárok na PSP a každý predikát má tři rozsahy, bude existovat devět rozsahů variant dotazu číslovaný 1 až 9.
• Rozsah predikátu je informace o předpokládaném rozsahu kardinality vygenerovaná z výrazu dispečera.

A v rámci ShowPlan XML varianty dotazu (uvnitř elementu Dispatcher):

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Remarks

• Od verze SQL Server 2025 (17.x) a kompatibility databází 170 zahrnuje optimalizace PSP následující čtyři vylepšení:

  • Podpora příkazů DML (Data Manipulation Language) jazyka pro manipulaci s daty, jako jsou DELETE, INSERT, MERGE a UPDATE.
  • Rozšířená podpora pro tempdb.
  • Další aspekty uvedené ve scénářích, kdy ve stejné tabulce existuje více způsobilých predikátů.
  • query_with_parameter_sensitivity Změny rozšířené události, která zahrnovala pole interesting_predicate_count, max_skewness, psp_optimization_supported a query_type před změnami v SQL Serveru 2025 (17.x) a kompatibilitě databáze 170. Nyní však zahrnují pole interesting_predicate_count, interesting_predicate_details, psp_optimization_supported a query_type. Další informace naleznete v části Rozšířené události .

• Funkce optimalizace PSP v současné době funguje pouze s predikáty rovnosti.

• Plány dispečera se automaticky znovu sestaví, pokud dojde k významným změnám distribuce dat. Plány variant dotazů se podle potřeby rekompilují nezávisle, stejně jako u jakéhokoli jiného typu plánu dotazu, na základě výchozích událostí rekompilace. Další informace o rekompilace naleznete v tématu Rekompiling Execution Plans.

• Zobrazení systémového katalogu úložiště dotazů sys.query_store_plan bylo změněno tak, aby rozlišovalo mezi normálním zkompilovaným plánem, plánem dispečera a plánem varianty dotazu. Nové zobrazení systémového katalogu úložiště dotazů , sys.query_store_query_variant, obsahuje informace o relacích nadřazených a podřízených mezi původními parametrizovanými dotazy (označované také jako nadřazené dotazy), plány dispečerů a jejich podřízenými variantami dotazů.

• Pokud je součástí stejné tabulky více predikátů, optimalizace PSP vybere predikát, který má největší zkreslení dat na základě podkladového histogramu statistik. Například s SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, protože obě column1 = @predicate1 a column2 = @predicate2 jsou ze stejné tabulky, table1bude funkce vyhodnocovat pouze nejvíce zkosený predikát. Pokud však ukázkový dotaz zahrnuje operátor, jako je UNION, PSP vyhodnotí více než jeden predikát. Například pokud dotaz má charakteristiky podobné SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, PSP vybere v tomto případě maximálně dva predikáty, protože systém tento scénář považuje za dvě různé tabulky. Stejné chování je možné pozorovat u dotazů, které se připojují sami prostřednictvím aliasů tabulek.

• ShowPlan XML pro variantu dotazu by vypadal podobně jako v následujícím příkladu, kde oba predikáty, které byly vybrány, mají příslušné informace přidané k nápovědě související s PLAN PER VALUE PSP.

  <Batch>
    <Statements>
      <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
        <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
        <Dispatcher>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
        </Dispatcher>
        <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">
  

• Aktuální prahové hodnoty nerovnoměrné distribuce používané funkcí optimalizace PSP můžete ovlivnit pomocí jedné nebo více následujících metod:

  • Příznaky trasování odhadovače kardinality (CE), například příznak trasování 9481 (globální, relační nebo na úrovni dotazu)

  • Možnosti konfigurace s vymezeným oborem databáze, které se pokoušejí snížit složitost využívaného modelu CE, nebo ovlivňují předpoklady, které model CE má ve vztahu k nezávislosti více predikátů. To je užitečné zejména v případech, kdy neexistují statistiky s více sloupci, což ovlivňuje schopnost optimalizace PSP vyhodnotit kandidaturu těchto predikátů.

  • Další informace najdete v části Zvýšení předpokladu korelace pro více predikátů v dokumentu White paper optimalizace plánů dotazů pomocí nástroje pro posouzení kardinality SQL Serveru 2014 . Novější model CE se snaží předpokládat určitou korelaci a méně nezávislosti pro spojení a disjunkci predikátů. Použití starší verze modelu CE může ovlivnit způsob výpočtu selektivity predikátů ve scénáři spojení s více sloupci. Tuto akci byste měli zvážit pouze pro konkrétní scénáře a nedoporučujeme používat starší model CE pro většinu úloh.

• Optimalizace PSP v současné době kompiluje a spouští každou variantu dotazu jako nový připravený příkaz, což je jeden z důvodů, proč varianty dotazu ztratí spojení s libovolnými nadřazenými moduly, object_id pokud byl plán dispečera založený na modulu (to znamená uložená procedura, trigger, funkce, zobrazení atd.). Jako připravený příkaz není object_id něco, co by bylo možné přímo namapovat na objekt sys.objects, ale v podstatě se jedná o hodnotu vypočítanou na základě interního hashe dávkového textu. Další informace najdete v části Vrácená tabulka v sys.dm_exec_plan_attributes dokumentaci k zobrazení dynamické správy.

  Plány variant dotazů jsou umístěny v úložišti objektů mezipaměti plánu (CACHESTORE_OBJCP), zatímco plány dispečera jsou umístěny v úložišti mezipaměti plánů SQL (CACHESTORE_SQLCP). Funkce PSP však uloží object_id nadřazenou hodnotu varianty dotazu v rámci atributu ObjectID, který je součástí nápovědy PLAN PER VALUE, který PSP přidá do ShowPlan XML, pokud nadřazený dotaz je součástí modulu, a ne dynamické nebo ad hoc T-SQL. Agregované statistiky výkonu pro procedury, funkce a triggery uložené v mezipaměti se můžou dál používat pro příslušné účely. Podrobnější statistiky související s prováděním, jako jsou ty, které se nacházejí v zobrazeních podobných sys.dm_exec_query_stats DMV, stále obsahují data pro varianty dotazů, avšak přidružení variant dotazů object_id a objektů v tabulce sys.objects aktuálně není sladěno bez dalšího zpracování ShowPlan XML pro každou variantu dotazu, u které jsou požadovány podrobnější statistiky provádění. Informace o době běhu a statistikách čekání pro varianty dotazů je možné získat z Úložiště dotazů, aniž by bylo nutné používat další techniky analýzy XML ShowPlan, pokud je Úložiště dotazů povolené.

• Vzhledem k tomu, že varianty dotazu PSP se spouštějí jako nový připravený příkaz, object_id se automaticky nezpřístupňuje ve zobrazeních dynamické správy souvisejících s mezipamětí plánu sys.dm_exec_* bez rozebrání XML ShowPlan a použití technik porovnávání textových vzorů (tj. dodatečné zpracování XQuery). Pouze plány dispečera optimalizace PSP aktuálně generují odpovídající ID nadřazeného objektu. Tato object_id data se zveřejňují v úložišti dotazů, protože úložiště dotazů umožňuje relační model, než poskytuje hierarchie mezipaměti plánu. Další informace najdete v zobrazení systémového katalogu úložiště dotazů sys.query_store_query_variant.

Considerations

• Pokud chcete povolit optimalizaci PSP, povolte úroveň kompatibility databáze 160 pro databázi, ke které jste připojení při provádění dotazu.

• Pokud chcete získat další přehled o funkci optimalizace PSP, doporučujeme povolit integraci úložiště dotazů zapnutím úložiště dotazů. Následující příklad zapne úložiště dotazů pro existující databázi s názvem MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

Note

Od verze SQL Server 2022 (16.x) je úložiště dotazů ve výchozím nastavení povolené pro všechny nově vytvořené databáze.

• Pokud chcete zakázat optimalizaci PSP na úrovni databáze, použijte konfiguraci s vymezeným oborem ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF databáze.

• Pokud chcete zakázat optimalizaci PSP na úrovni dotazu, použijte nápovědu DISABLE_PARAMETER_SENSITIVE_PLAN k dotazu.

• Pokud je snifování parametrů zakázáno příznakem trasování 4136, PARAMETER_SNIFFING konfigurací s vymezeným oborem databáze nebo příkazovým náznakem USE HINT('DISABLE_PARAMETER_SNIFFING') dotazu, optimalizace PSP je zakázána pro související úlohy a kontexty spuštění. Další informace naleznete v tématu Nápovědy k dotazům a ALTER DATABASE SCOPED CONFIGURATION.

• Počet jedinečných variant plánu na dispečera uložených v mezipaměti plánu je omezený, aby nedocházelo k nafukování mezipaměti. Vnitřní prahová hodnota není zdokumentovaná. Vzhledem k tomu, že každá dávka SQL má potenciál vytvořit více plánů a každý plán variant dotazů má nezávislou položku v mezipaměti plánu, je možné dosáhnout výchozího maximálního počtu povolených položek plánu. Pokud je míra vyřazení mezipaměti plánu pozorovatelná nebo velikosti CACHESTORE_OBJCPCACHESTORE_SQLCPúložišť mezipaměti jsou nadměrné, měli byste zvážit použití příznaku trasování 174.

• Počet jedinečných variant plánů uložených pro dotaz ve Query Store je omezen možností konfigurace max_plans_per_query. Vzhledem k tomu, že varianty dotazů můžou mít více než jeden plán, může být v úložišti dotazů k dispozici celkem 200 plánů. Toto číslo zahrnuje všechny plány variant dotazů pro všechny dispečery, kteří patří do nadřazeného dotazu. Zvažte zvýšení max_plans_per_query možnosti konfigurace úložiště dotazů.

  • Příkladem toho, jak může počet jedinečných plánů překročit výchozí limit dotazového úložiště max_plans_per_query, je situace, kdy se vyskytne následující scénář. Předpokládejme, že máte dotaz s ID dotazu 10, který má dva plány dispečera a každý plán dispečera má 20 variant dotazů (celkem 40 variant dotazů). Celkový počet plánů pro ID dotazu 10 je 40 plánů pro varianty dotazu a dva plány dispečera. Je také možné, že samotný nadřazený dotaz (ID dotazu 10) může mít 5 běžných (ne-dispečerských) plánů. Tímto je celkem 47 plánů (40 z variant dotazů, 2 dispečerské a 5 plánů nesouvisejících s PSP). Pokud má každá varianta dotazu také průměrně pět plánů, je v tomto scénáři možné mít v úložišti dotazů pro nadřazený dotaz více než 200 plánů. To by také záviselo na velké nerovnoměrné distribuci dat v datových sadách, na které může tento příklad nadřazeného dotazu odkazovat.

• Pro každou variantu dotazu přiřazenou danému dispečerovi:

  • query_plan_hash je jedinečný. Tento sloupec je k dispozici v sys.dm_exec_query_stats, zobrazeních dynamické správy a tabulkách katalogu.
  • plan_handle je jedinečný. Tento sloupec je k dispozici v sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans a v dalších zobrazeních a funkcích dynamické správy a v tabulkách katalogu.
  • query_hash je společné pro různé varianty mapování na stejného dispečera, takže je možné určit celkové využití prostředků pro dotazy, které se liší pouze hodnotami vstupních parametrů. Tento sloupec je k dispozici v zobrazeních dynamické správy sys.dm_exec_query_stats, v katalogových tabulkách sys.query_store_query a dalších.
  • Je sql_handle jedinečný z důvodu přidání speciálních identifikátorů optimalizace PSP do textu dotazu během kompilace. Tento sloupec je k dispozici v sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans a v dalších zobrazeních a funkcích dynamické správy a v tabulkách katalogu. Stejné informace o popisovači jsou k dispozici v úložišti dotazů jako last_compile_batch_sql_handle sloupec v sys.query_store_query tabulce katalogu.
  • Úložiště dotazů query_id je jedinečné. Tento sloupec je k dispozici v tabulkách katalogu sys.query_store_query a v dalších tabulkách katalogu úložiště dotazů.

Vynucení plánu v úložišti dotazů

Používá stejné sp_query_store_force_plan a sp_query_store_unforce_plan uložené procedury k operacím na dispečerských nebo alternativních plánech.

Pokud je varianta vynucena, nadřazený dispečer není vynucen. Pokud je dispečer vynucený, považují se za způsobilé k použití pouze varianty od daného dispečera:

• Dříve vynucené varianty jiných dispečerů stávají se neaktivními, ale zachovávají si vynucený stav, dokud jejich dispečer znovu není vynucen.
• Dříve vynucené varianty ve stejném dispečerovi, který se stal neaktivním, jsou nuceny znovu

Chování navrhování dotazů v Query Store

• Pokud se do varianty dotazu (podřízeného dotazu) přidá hint Query Store, použije se stejným způsobem jako dotaz bez PSP. Varianty nápověd dotazů mají přednost, pokud byla nápověda také použita na nadřazený dotaz v Query Store.

• Když do nadřazeného dotazu přidáte nápovědu úložiště dotazů a podřízený dotaz (varianta dotazu) nemá existující nápovědu úložiště dotazů, podřízený dotaz (varianta dotazu) dědí nápovědu z nadřazeného dotazu.

• Pokud je z nadřazeného dotazu odebrána nápověda dotazu úložiště, mají podřízené dotazy (varianty dotazů) také odebranou tuto nápovědu.

• RECOMPILE Pokud se do nadřazeného dotazu přidá nápověda, systém po odebrání všech existujících plánů variant dotazů z mezipaměti plánu vygeneruje ne-PSP plány, protože funkce PSP nefunguje s dotazy, které mají nápovědu RECOMPILE.

• Výsledky poznámek úložiště dotazů lze pozorovat pomocí rozšířených událostí query_store_hints_application_success a query_store_hints_application_failed. Pro tabulku sys.query_store_query_hints obsahuje informace o radách dotazu, které byly použity. Pokud byla nápověda použita pouze u nadřazeného dotazu, systémový katalog obsahuje informace nápovědy pro nadřazený dotaz, ale ne pro podřízené dotazy, i když podřízené dotazy dědí nápovědu nadřazeného dotazu.

PSP s tipy pro dotazy a chování vynucení plánování lze shrnout v následující tabulce:

Nápověda k dotazu nebo plán varianty	Nadřazený má uživatelskou nápovědu	Nadřazený má nápovědu se zapracovanou zpětnou vazbou	Rodičovský plán byl ručně vynucen	Nadřazený má vynucený APC 1 plán.
Nápověda prostřednictvím uživatele	Nápověda k variantě dotazu	Nápověda k variantě dotazu	Nápověda k variantě dotazu	N/A
Nápověda prostřednictvím zpětné vazby	Nápověda k variantě dotazu	Nápověda k variantě dotazu	Nápověda k variantě dotazu	N/A
Plánování vynucené uživatelem	Varianta dotazu vynucený plán	Varianta dotazu vynucený plán	Varianta dotazu vynucený plán	Varianta dotazu vynucený plán
Plán vynucený APC	Varianta dotazu vynucený plán	Varianta dotazu vynucený plán	Varianta dotazu vynucený plán	Varianta dotazu vynucený plán
Žádný tip nebo vynucený plán	Nápověda pro nadřazeného uživatele	Bez nápovědy	Žádná akce	Žádná akce

¹ Komponenta automatické opravy plánu funkce automatického ladění

Rozšířené události

• parameter_sensitive_plan_optimization_skipped_reason: K tomu dojde, když je funkce plánu citlivého na parametr přeskočena. Tato událost slouží ke sledování důvodu vynechání optimalizace PSP.

  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] = 'psp_skipped_reason_enum'
  ORDER BY map_key;
  

• parameter_sensitive_plan_optimization: Nastane, když dotaz používá funkci optimalizace PSP. Pouze kanál ladění. Některá pole, která vás zajímají, můžou být:

  • is_query_variant: popisuje, zda se jedná o plán dispečera (rodič) nebo plán varianty dotazu (potomek).
  • predicate_count: počet predikátů vybraných psp
  • query_variant_id: zobrazí ID varianty dotazu. Hodnota 0 znamená, že objekt je plán dispečera (nadřazený).

• query_with_parameter_sensitivity: Tato událost, když je spuštěna, zobrazí počet predikátů, které funkce našla zajímavé, další podrobnosti ve formátu JSON týkající se zajímavých predikátů a zda je PSPO podporován pro predikát nebo predikáty.

• Příklad výstupu z rozšířené události query_with_parameter_sensitivity

Field	Value
interesting_predicate_count	3
interesting_predicate_details	{"Predicates":[{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75},{"ColumnId":7,"TableId":1221579390,"Skewness":475038.75}]}
psp_optimization_supported	True
query_type	195

Auditování SQL Serveru

Optimalizace PSP poskytuje data auditu pro prohlášení plánu dispečera a všechny varianty dotazů spojené s dispečerem. Sloupec additional_information v rámci sql Serveru Audit také poskytuje příslušné informace o zásobníku T-SQL pro varianty dotazů. Příklad použití databáze MyNewDatabase: pokud má tato databáze tabulku s názvem T2 a uloženou proceduru s názvem usp_test, po spuštění uložené procedury usp_test může protokol auditu obsahovat následující položky:

action_id	object_name	příkaz	additional_information
AUSC			<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
EX	usp_test	exec usp_test 300
SL	T2	select * from dbo.t2 where ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100,0, 100000,0)))	tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
EX	usp_test	exec usp_test 60000
SL	T2	select * from dbo.t2 where ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364; QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[ dbo]. [T2]. [ID] = @id, 100,0, 100000,0))	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

Známé problémy

Issue	Datum zjištění	Status	Datum vyřešení
V úložišti dotazů v SQL Serveru 2022 (16.x) za určitých podmínek dochází k výjimce porušení přístupu. Pokud je povolená integrace úložiště dotazů pro optimalizaci PSP, může dojít k výjimkám porušení přístupu. Další informace najdete v aktualizaci optimalizace plánu citlivého na parametr, Proč?	Březen 2023	Resolved	Srpen 2023 (CU 7)
Výjimka porušení přístupu může nastat na čitelných sekundárních replikách za určitých podmínek v SQL Serveru 2025 (17.x). Při povolení optimalizace PSP v sekundární databázi s možností čtení, která je nakonfigurovaná tak, aby používala úložiště dotazů pro funkci čtení sekundárních souborů , může dojít k narušení přístupu.	Září 2025	Má řešení

Má řešení

V sekundárních replikách s možností čtení může za určitých podmínek dojít k výjimce porušení přístupu.

Dotazy, které splňují následující podmínky, můžou zaznamenat narušení přístupu, když varianta dotazu PSP nedokáže určit trvalý stav nadřazeného příkazu dispečera:

• Spuštěno na sekundární replice
• Citlivé na zašifrování parametrů
• Nárok na optimalizaci plánu citlivého na parametry (PSP)

Alternativní řešení: Zakažte PSP na sekundárech pro každou databázi, která byla nasazena, aby umožnila použití úložiště dotazů pro funkci čitelných sekundárů. V kontextu konkrétní databáze zadejte následující příkaz Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Resolved

V úložišti dotazů v SQL Serveru 2022 za určitých podmínek dochází k výjimce porušení přístupu

Note

SQL Server 2022 (16.x) Kumulativní aktualizace 7 obsahuje několik oprav závodní podmínky, která může vést k porušení přístupu.

K tomuto problému došlo kvůli podmínce závodu, která může být způsobena, když jsou statistiky modulu runtime pro spuštěný dotaz uchovány z paměti reprezentace úložiště dotazů (nachází se v paměti clerk MEMORYCLERK_QUERYDISKSTORE_HASHMAP) na diskovou verzi úložiště dotazů. Statistiky modulu runtime zobrazené jako statistiky modulu runtime se uchovávají v paměti po dobu definovanou DATA_FLUSH_INTERVAL_SECONDS možností SET QUERY_STORE příkazu (výchozí hodnota je 15 minut). Dialogové okno Úložiště dotazů v sadě Management Studio můžete použít k zadání hodnoty intervalu vyprázdnění dat (minuty), který se interně převede na sekundy. Pokud je systém pod tlakem paměti, statistiky modulu runtime je možné vyprázdnit na disk dříve, než je definováno pomocí možnosti DATA_FLUSH_INTERVAL_SECONDS. Pokud existují další vlákna úložiště dotazů související s vyčištěním plánu dotazů úložiště dotazů (to znamená volby úložiště dotazů STALE_QUERY_THRESHOLD_DAYS a/nebo MAX_STORAGE_SIZE_MB), existuje scénář, ve kterém se může předčasně dereferencovat varianta dotazu a/nebo jeho přidružený dispatcher. To může způsobit narušení přístupu během operací vložení nebo odstranění variant dotazů do úložiště dotazů.

Další informace o operacích úložiště dotazů najdete v části Poznámky v článku o shromažďování dat v úložišti dotazů.

Související obsah

• Rekompilování plánů provádění
• Parametry a opětovné použití plánu vykonává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)