Optimalisatie van parametergevoelig plan

Van toepassing op: SQL Server 2022 (16.x) en latere versies Van Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric Preview

Parameter Sensitive Plan (PSP) optimalisatie maakt deel uit van de intelligent queryverwerkingsfamilie van functies. Hiermee wordt het scenario opgelost waarbij één plan in de cache voor een geparameteriseerde query niet optimaal is voor alle mogelijke binnenkomende parameterwaarden. Dit geldt voor niet-uniforme gegevensdistributies. Zie Parametergevoeligheid en Parameters en Hergebruik van uitvoeringsplan voor meer informatie.

Zie voor meer informatie over bestaande tijdelijke oplossingen voor dit probleemscenario:

• parametergevoelige problemen onderzoeken en oplossen
• parameters en het uitvoeringsplan opnieuw gebruiken
• Query's met Parameter Sensitive Plan (PSP)-problemen

PSP-optimalisatie schakelt automatisch meerdere actieve abonnementen in de cache in voor één geparameteriseerde instructie. Uitvoeringsplannen in de cache bieden plaats aan verschillende gegevensgrootten op basis van de door de klant geleverde runtimeparameterwaarde(s).

Meer informatie over parameterisatie

In de SQL Server Database Engine verhoogt het gebruik van parameters of parametermarkeringen in Transact-SQL -instructies (T-SQL) de mogelijkheid van de relationele engine om te voldoen aan nieuwe T-SQL-instructies met bestaande, eerder gecompileerde uitvoeringsplannen en het verhogen van het hergebruik van plannen. Zie Simple Parameterization voor meer informatie.

U kunt ook het standaardgedrag van eenvoudige parameters van SQL Server overschrijven door op te geven dat alle SELECT, INSERT, UPDATEen DELETE instructies in een database worden geparameteriseerd, afhankelijk van bepaalde beperkingen. Zie Geforceerde parameterisatie voor meer informatie.

PSP-optimalisatie-implementatie

Tijdens de eerste compilatie identificeren kolomstatistieken histogrammen niet-uniforme distributies en evalueren ze de meest risicovolle predicaten, tot drie van alle beschikbare predicaten. Met andere woorden, als meerdere predicaten binnen dezelfde query voldoen aan de criteria, kiest PSP-optimalisatie de top drie. De PSP-functie beperkt het aantal predicaten dat wordt geëvalueerd, om te voorkomen dat de plancache en de Query Store (als de Query Store is ingeschakeld) met te veel plannen worden opgeblazen.

Voor in aanmerking komende plannen produceert de eerste compilatie een dispatcherplan dat de PSP-optimalisatielogica bevat, een dispatcherexpressie genoemd. Een dispatcherplan komt overeen met queryvarianten op basis van de grenswaarden voor kardinaliteitsbereiken.

Terminology

Dispatcher-expressie

Evalueert de kardinaliteit van predicaten op basis van runtimeparameterwaarden en route-uitvoering naar verschillende queryvarianten.

Dispatcher-abonnement

Een plan met de dispatcher-expressie wordt in de cache opgeslagen voor de oorspronkelijke query. Het dispatcher-plan is in wezen een verzameling van de predicaten die door de functie zijn geselecteerd, met een paar extra details. Voor elk predicaat dat is geselecteerd, zijn enkele van de details die zijn opgenomen in het dispatcherplan de waarden voor hoge en lage grenzen. Deze waarden worden gebruikt om parameterwaarden te verdelen in verschillende emmers of reeksen. Het dispatcherplan bevat ook de statistieken die zijn gebruikt om de grenswaarden te berekenen.

Queryvariant

Als een dispatcher-plan de kardinaliteit van predicaten evalueert op basis van runtimeparameterwaarden, worden deze waarden gegroepeerd en worden afzonderlijke onderliggende query's gegenereerd om te compileren en uit te voeren. Deze subquery's worden queryvarianten genoemd. Query-varianten hebben hun eigen plannen in de Plancache en Query Store.

Predicaatkardinaliteitsbereik

Tijdens runtime wordt de kardinaliteit van elk predicaat geëvalueerd op basis van runtimeparameterwaarden. De dispatcher verdeelt de kardinaliteitswaarden naar drie predicaatkardinaliteitscategorieën tijdens de compilatiefase. De PSP-optimalisatiefunctie kan bijvoorbeeld drie bereiken creëren die lage, gemiddelde en hoge kardinaliteitsbereiken omvatten, zoals weergegeven in het onderstaande diagram.

Met andere woorden, wanneer een geparameteriseerde query in eerste instantie wordt gecompileerd, genereert de PSP-optimalisatiefunctie een shell-plan dat bekend staat als een dispatcherplan. De dispatcher-expressie heeft de logica waarmee query's worden ingedeeld in queryvarianten op basis van de runtime waarden van parameters. Wanneer de werkelijke uitvoering begint, voert de dispatcher twee stappen uit:

• De verwerker evalueert de verwerkerexpressie voor de opgegeven set parameters om het kardinaliteitsbereik te berekenen.

• de dispatcher alloceert deze reeksen aan specifieke queryvarianten, compileert en voert de varianten uit. De PSP-optimalisatie biedt vanwege de aanwezigheid van meerdere queryvarianten meerdere plannen voor één query.

De kardinaliteitsbereikgrenzen zijn te zien in de ShowPlan XML van een verzendplan:

<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>

Een door PSP-optimalisatie gegenereerde hint wordt toegevoegd aan de SQL-instructie in de ShowPlan XML van een queryvariant. De hint kan niet rechtstreeks worden gebruikt en wordt niet geparseerd als deze handmatig wordt toegevoegd. De hint bevat de volgende elementen:

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

• ObjectID is afkomstig van de module (dat wil zeggen, opgeslagen procedure, functie, trigger) waarvan de huidige instructie deel uitmaakt; met de aanname dat de instructie is gegenereerd op basis van een module. Als de instructie het resultaat is van dynamische of ad-hoc SQL (dat wil zeggen) sp_executesqlis het ObjectID-element gelijk aan 0.
• QueryVariantID komt ongeveer overeen met de combinatie van bereiken voor alle predicaten die door PSP-optimalisatie zijn geselecteerd. Als een query bijvoorbeeld twee predicaten heeft die in aanmerking komen voor PSP en elk predicaat drie bereiken heeft, zijn er negen query-variantbereiken, genummerd 1-9.
• predicaatbereik is de geprediceerde kardinaliteitsbereikinformatie die is gegenereerd op basis van de dispatcher-expressie.

En binnen de ShowPlan XML van een queryvariant (binnen het dispatcher-element):

<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

• Vanaf SQL Server 2025 (17.x) Preview en databasecompatibiliteit 170 bevat PSP-optimalisatie de volgende vier verbeteringen:

  • Ondersteuning voor instructies van de gegevensmanipulatietaal (Data Manipulatie Taal), zoals DELETE, INSERT, MERGE en UPDATE.
  • Uitgebreide ondersteuning voor tempdb.
  • Aanvullende overwegingen in scenario's waarin meerdere in aanmerking komende predicaten in dezelfde tabel aanwezig zijn.
  • Wijzigingen aan de query_with_parameter_sensitivity uitgebreide gebeurtenis, die de velden interesting_predicate_count, max_skewness, psp_optimization_supported en query_type omvatte, vóór de veranderingen in SQL Server 2025 (17.x) Preview en databasecompatibiliteit 170. Maar neem nu de velden interesting_predicate_count, interesting_predicate_details, psp_optimization_supported en query_type op. Zie de sectie Uitgebreide gebeurtenissen voor meer informatie.

• De functie PSP-optimalisatie werkt momenteel alleen met gelijkheidspredicaten.

• Dispatcher-plannen worden automatisch opnieuw opgebouwd als er aanzienlijke wijzigingen in de gegevensdistributie zijn. Queryvariantplannen worden onafhankelijk opnieuw gecompileerd, net als bij elk ander type queryplan, afhankelijk van standaard hercompilatiegebeurtenissen. Zie Uitvoeringsplannen opnieuw compileren voor meer informatie over hercompilatie.

• De sys.query_store_plan Query Store-systeemcatalogusweergave is gewijzigd om onderscheid te maken tussen een normaal gecompileerd plan, een dispatcherplan en een queryvariantplan. De nieuwe systeemcatalogusweergave van Query Store, sys.query_store_query_variant, bevat informatie over de ouder-kind relaties tussen de oorspronkelijke geparameteriseerde query's (ook wel ouderquery's genoemd), dispatcher plannen en hun onderliggende queryvarianten.

• Wanneer er meerdere predicaten zijn die deel uitmaken van dezelfde tabel, selecteert PSP-optimalisatie het predicaat met de meeste scheeftrekken van gegevens op basis van het onderliggende histogram voor statistieken. Bijvoorbeeld, omdat SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2beide column1 = @predicate1 en column2 = @predicate2 afkomstig zijn uit dezelfde tabel, table1wordt alleen het meest scheve predicaat geëvalueerd door de functie. Als de voorbeeldquery echter een operator zoals een UNION bevat, evalueert PSP meer dan één predicaat. Als een query bijvoorbeeld kenmerken heeft die vergelijkbaar zijn met SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, kiest PSP in dit geval maximaal twee predicaten, omdat het systeem dit scenario behandelt alsof het twee verschillende tabellen zijn. Hetzelfde gedrag kan worden waargenomen vanuit query's die zichzelf samenvoegen via tabelaliassen.

• De ShowPlan XML voor een queryvariant zou er ongeveer uitzien zoals in het volgende voorbeeld, waarbij beide geselecteerde predicaten hun respectieve informatie hebben toegevoegd aan de PLAN PER VALUE PSP-gerelateerde hint.

  <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">
  

• U kunt de huidige scheefheidsdrempels beïnvloeden die worden gebruikt door de functie PSP-optimalisatie, met een of meer van de volgende methoden:

  • Traceringsvlaggen van kardinaliteitsschatter (CE), zoals traceringsvlag 9481 (globaal, sessie- of queryniveau)

  • Configuratieopties voor databasebereik die proberen het CE-model in gebruik te verlagen of invloed hebben op de veronderstellingen die het CE-model maakt met betrekking tot de onafhankelijkheid van meerdere predicaten. Dit is vooral handig in gevallen waarin statistieken met meerdere kolommen niet bestaan, wat van invloed is op het vermogen van PSP-optimalisatie om de kandidatuur van deze predicaten te evalueren.

  • Zie de sectie Verhoogde correlatie-aanname voor meerdere predicaten van de sectie Uw queryplannen optimaliseren met het technisch document Kardinaliteitsschatter sql Server 2014 voor meer informatie. Het nieuwere CE-model probeert uit te gaan van een correlatie en minder onafhankelijkheid voor de combinatie en het ontkoppelen van predicaten. Het gebruik van het verouderde CE-model kan van invloed zijn op de wijze waarop selectiviteit van de predicaten in een joinscenario met meerdere kolommen kan worden berekend. Deze actie moet alleen worden overwogen voor specifieke scenario's en het wordt niet aanbevolen om het verouderde CE-model voor de meeste workloads te gebruiken.

• PSP-optimalisatie compileert momenteel elke queryvariant en voert deze uit als een nieuwe voorbereide instructie. Dit is een van de redenen waarom de queryvarianten hun koppeling met eventuele bovenliggende modules object_id verliezen als het dispatcherplan is gebaseerd op een module (dat wil zeggen, opgeslagen procedure, trigger, functie, weergave, enzovoort). Als voorbereide verklaring is de object_id niet iets dat rechtstreeks aan een object in sys.objects kan worden gekoppeld, maar is het in wezen een berekende waarde die is gebaseerd op een interne hash van de batchtekst. Zie de sectie Geretourneerde tabel van de sys.dm_exec_plan_attributes DMV-documentatie voor meer informatie.

  Query-variantplannen worden in de objectopslag van de plancache geplaatst (CACHESTORE_OBJCP) terwijl dispatcher-plannen worden geplaatst in de SQL-plannencache-opslag (CACHESTORE_SQLCP). De PSP-functie slaat echter de ouder van een queryvariant op in het ObjectID-kenmerk, dat deel uitmaakt van de PLAN PER VALUE-hint die PSP toevoegt aan de ShowPlan XML, als de ouderquery deel uitmaakt van een module en niet dynamisch of ad hoc T-SQL is. Statistische prestatiestatistieken voor procedures, functies en triggers in de cache kunnen voor hun respectieve doeleinden blijven worden gebruikt. Gedetailleerdere uitvoeringsstatistieken, zoals die in weergaven die vergelijkbaar zijn met de sys.dm_exec_query_stats DMV, bevatten nog steeds gegevens voor queryvarianten, maar de koppeling tussen de object_id voor queryvarianten en objecten in de sys.objects tabel komt momenteel niet overeen, zonder aanvullende verwerking van de ShowPlan XML voor elk van de queryvarianten waarvoor meer gedetailleerde runtimestatistieken vereist zijn. De informatie over runtime- en wachtstatistieken voor queryvarianten kan worden verkregen uit de Query Store zonder extra ShowPlan XML-parseringstechnieken als Query Store is ingeschakeld.

• Omdat PSP-queryvarianten worden uitgevoerd als een nieuwe voorbereide instructie, wordt deze object_id niet automatisch weergegeven in de verschillende plancache-gerelateerde sys.dm_exec_* DMV's zonder de ShowPlan XML te versnipperen en tekstpatroonkoppelingstechnieken toe te passen (dat wil zeggen aanvullende XQuery-verwerking). Alleen PSP Optimization Dispatcher-plannen genereren momenteel de juiste bovenliggende object-ID. De object_id optie wordt weergegeven in de Query Store, omdat Query Store een meer relationeel model toestaat dan de plancachehiërarchie biedt. Zie de systeemcatalogusweergave van Query Store sys.query_store_query_variant voor meer informatie.

Considerations

• Als u PSP-optimalisatie wilt inschakelen, schakelt u databasecompatibiliteitsniveau 160 in voor de database waarmee u bent verbonden bij het uitvoeren van de query.

• Voor meer inzicht in de functie PSP-optimalisatie raden we u aan om Query Store-integratie in te schakelen door Query Store in te schakelen. In het volgende voorbeeld wordt Query Store ingeschakeld voor een bestaande database met de naam MyNewDatabase:

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

• Als u PSP-optimalisatie op databaseniveau wilt uitschakelen, gebruikt u de configuratie van het ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF databasebereik.

• Als u PSP-optimalisatie op queryniveau wilt uitschakelen, gebruikt u de DISABLE_PARAMETER_SENSITIVE_PLAN queryhint.

• Als parameter-sniffing is uitgeschakeld door traceringsvlag 4136, PARAMETER_SNIFFING configuratie met databasebereik of de USE HINT('DISABLE_PARAMETER_SNIFFING') queryhint, wordt PSP-optimalisatie uitgeschakeld voor de bijbehorende workloads en uitvoeringscontexten. Zie Queryhints en ALTER DATABASE SCOPED CONFIGURATION voor meer informatie.

• Het aantal unieke abonnementsvarianten per dispatcher die zijn opgeslagen in de plancache, is beperkt om cache-bloating te voorkomen. De interne drempelwaarde wordt niet gedocumenteerd. Omdat elke SQL-batch de mogelijkheid heeft om meerdere plannen te creëren en elk queryvariantplan een onafhankelijke vermelding in de plancache heeft, is het mogelijk om het standaard maximum aantal toegestane planvermeldingen te bereiken. Als de verwijderingssnelheid van de plancache waarneembaar hoog is of als de grootte van de CACHESTORE_OBJCP en CACHESTORE_SQLCPcacheopslag te hoog is, kunt u overwegen trace flag 174 toe te passen.

• Het aantal unieke planvarianten dat is opgeslagen voor een query in het Query Store-archief, wordt beperkt door de max_plans_per_query configuratieoptie. Omdat queryvarianten meer dan één abonnement kunnen hebben, kunnen er in totaal 200 abonnementen per query in de Query Store worden weergegeven. Dit nummer bevat alle query-variantplannen voor alle dispatchers die deel uitmaken van een ouderquery. Overweeg om de max_plans_per_query configuratieoptie Query Store te verhogen.

  • Een voorbeeld van hoe het aantal unieke abonnementen de standaardlimiet voor Query Store max_plans_per_query kan overschrijden, is een scenario waarin u het volgende gedrag hebt. Stel dat u een query hebt met een query-id van 10, die twee dispatcherplannen heeft en elk dispatcherplan 20 queryvarianten heeft (in totaal 40 queryvarianten). Het totale aantal abonnementen voor query-id 10 is 40 abonnementen voor de queryvarianten en de twee dispatcherplannen. Het is ook mogelijk dat de bovenliggende query zelf (query-id 10) 5 reguliere (niet dispatcher) plannen kan hebben. Dit maakt 47 abonnementen (40 van queryvarianten, 2 dispatcher en 5 niet-PSP-gerelateerde abonnementen). Verder, als elke queryvariant ook een gemiddelde van vijf plannen heeft, is het in dit scenario mogelijk om meer dan 200 plannen te hebben in de Query Store voor een hoofdquery. Dit is ook afhankelijk van grote gegevensscheeftrekking in de gegevensset(s) waarnaar deze voorbeeldquery mogelijk verwijst.

• Voor elke queryvarianttoewijzing aan een bepaalde dispatcher:

  • Het query_plan_hash is uniek. Deze kolom is beschikbaar in sys.dm_exec_query_statsen andere dynamische beheerweergaven en catalogustabellen.
  • Het plan_handle is uniek. Deze kolom is beschikbaar in sys.dm_exec_query_stats, sys.dm_exec_sql_texten sys.dm_exec_cached_plansin andere dynamische beheerweergaven en functies en catalogustabellen.
  • Het query_hash is gebruikelijk voor andere varianten die aan dezelfde dispatcher worden toegewezen, dus het is mogelijk om het geaggregeerde resourcegebruik te bepalen voor query's die alleen verschillen per invoerparameterwaarden. Deze kolom is beschikbaar in sys.dm_exec_query_stats, sys.query_store_queryen andere dynamische beheerweergaven en catalogustabellen.
  • Dit sql_handle is uniek omdat speciale PSP-optimalisatie-id's tijdens de compilatie aan de querytekst worden toegevoegd. Deze kolom is beschikbaar in sys.dm_exec_query_stats, sys.dm_exec_sql_texten sys.dm_exec_cached_plansin andere dynamische beheerweergaven en functies en catalogustabellen. Dezelfde handle-informatie is beschikbaar in de Query Store als de last_compile_batch_sql_handle kolom in de sys.query_store_query catalogustabel.
  • De query_id naam is uniek in de Query Store. Deze kolom is beschikbaar in sys.query_store_queryen andere Query Store-catalogustabellen.

Plan afdwingen in Query Store

Gebruikt dezelfde sp_query_store_force_plan en sp_query_store_unforce_plan opgeslagen procedures om te werken op dispatcher- of variantplannen.

Als een variant wordt gedwongen, wordt de hoofd-dispatcher niet gedwongen. Als een dispatcher gedwongen wordt, komen alleen varianten van die dispatcher in aanmerking voor gebruik.

• Eerder geforceerde varianten van andere dispatchers worden inactief, maar behouden de geforceerde status totdat hun dispatcher opnieuw geforceerd wordt.
• Eerder geforceerde varianten in dezelfde dispatcher, die inactief waren geworden, worden opnieuw geforceerd.

Het gedrag van query-hints in Query Store

• Wanneer een Query Store-hint wordt toegevoegd aan een queryvariant (onderliggende query), wordt de hint op dezelfde manier toegepast als een niet-PSP-query. Hints voor queryvarianten hebben een hogere prioriteit als er ook een hint is toegepast op de bovenliggende query in Query Store.

• Wanneer een Query Store-hint wordt toegevoegd aan de bovenliggende query en de onderliggende query (queryvariant) geen bestaande Query Store-hint heeft, neemt de onderliggende query (queryvariant) de hint over van de bovenliggende query.

• Als een Query Store-queryhint wordt verwijderd uit de bovenliggende query, wordt de hint ook verwijderd bij de onderliggende query's (queryvarianten).

• Als er een RECOMPILE hint wordt toegevoegd aan de bovenliggende query, genereert het systeem niet-PSP-abonnementen nadat bestaande queryvariantplannen zijn verwijderd uit de plancache, omdat de PSP-functie niet werkt op query's met een RECOMPILE hint.

• Resultaten van Query Store-hints kunnen worden waargenomen met behulp van de Extended Events query_store_hints_application_success en query_store_hints_application_failed gebeurtenissen. Voor de sys.query_store_query_hints tabel bevat deze informatie over de queryhint die is toegepast. Als de hint alleen is toegepast op een bovenliggende query, bevat de systeemcatalogus de hintgegevens voor de bovenliggende query, maar niet voor de onderliggende query's, hoewel de onderliggende query's de hint van de bovenliggende query overnemen.

PSP met queryhints en plan voor het afdwingen van gedrag kan worden samengevat in de volgende tabel:

¹ Automatische correctiecomponent van het plan van de functie voor automatisch afstemmen

Uitgebreide gebeurtenissen

• parameter_sensitive_plan_optimization_skipped_reason: treedt op wanneer de parametergevoelige planfunctie wordt overgeslagen. Gebruik deze gebeurtenis om de reden te controleren waarom PSP-optimalisatie wordt overgeslagen.

  De volgende query toont alle mogelijke redenen waarom PSP is overgeslagen:

  SELECT map_value
  FROM sys.dm_xe_map_values
  WHERE [name] = 'psp_skipped_reason_enum'
  ORDER BY map_key;
  

• parameter_sensitive_plan_optimization: vindt plaats wanneer een query gebruikmaakt van de functie PSP-optimalisatie. Uitsluitend foutopsporingskanaal. Sommige interessevelden kunnen zijn:

  • is_query_variant: beschrijft of dit een dispatcherplan (ouder) of een queryvariantplan (kind) is
  • predicate_count: aantal predicaten geselecteerd door PSP
  • query_variant_id: geeft de queryvariant-id weer. Een waarde van 0 betekent dat het object een dispatcherplan (moederobject) is.

• query_with_parameter_sensitivity: Deze gebeurtenis, wanneer geactiveerd, toont het aantal predicaten dat de functie als interessant heeft beoordeeld. Het geeft meer details in JSON-indeling over deze interessante predicaten, evenals of PSPO wordt ondersteund voor het predicaat of de predicaten.

• Voorbeelduitvoer van de query_with_parameter_sensitivity uitgebreide gebeurtenis

Gedrag van SQL Server-audit

PSP-optimalisatie biedt controlegegevens voor het onderdeel van het dispatcherplan en alle queryvarianten die aan de dispatcher zijn verbonden. De additional_information kolom in SQL Server Audit biedt ook de juiste T-SQL-stackgegevens voor queryvarianten. Als u de MyNewDatabase-database als voorbeeld gebruikt, en deze database heeft een tabel genaamd T2 en een opgeslagen procedure met de naam usp_test, dan kan het auditlogboek de volgende vermeldingen bevatten na de uitvoering van de usp_test-opgeslagen procedure:

Bekende problemen

Heeft een tijdelijke oplossing

Een toegangsschendingsuitzondering kan optreden op secundaire, leesbare replica's onder bepaalde voorwaarden.

Query's die aan de volgende voorwaarden voldoen, kunnen een toegangsfout ondervinden wanneer een PSP-queryvariant de persistente status van de bovenliggende dispatcher-instructie niet kan bepalen:

• Uitgevoerd op een secundaire replica
• Gevoelig voor parametersniffing
• In aanmerking komen voor parametergevoelige planoptimalisatie (PSP)

Tijdelijke oplossing: schakel PSP uit op secundaire bestanden voor elke database die is voorbereid voor het gebruik van de Query Store voor de functie leesbare secundaire databases. Geef vanuit de context van een specifieke database de volgende Transact-SQL instructie uit:

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Resolved

Uitzondering voor toegangsschending vindt plaats in Query Store in SQL Server 2022 onder bepaalde voorwaarden

Dit probleem is opgetreden vanwege een racevoorwaarde die kan worden veroorzaakt wanneer de runtimestatistieken voor een uitgevoerde query worden behouden vanuit de geheugenweergave van de Query Store (gevonden in de MEMORYCLERK_QUERYDISKSTORE_HASHMAP geheugenbediende) naar de schijfversie van de Query Store. De runtimestatistieken, weergegeven als Runtime Stats, worden gedurende een bepaalde periode in het geheugen bewaard, gedefinieerd door de DATA_FLUSH_INTERVAL_SECONDS optie van de SET QUERY_STORE instructie (de standaardwaarde is 15 minuten). U kunt het dialoogvenster Query Store van Management Studio gebruiken om een waarde in te voeren voor het interval voor het leegmaken van gegevens (minuten), die intern wordt geconverteerd naar seconden. Als het systeem onder geheugendruk staat, kunnen runtimestatistieken eerder dan gedefinieerd met de optie worden leeggemaakt naar de DATA_FLUSH_INTERVAL_SECONDS schijf. Wanneer er extra achtergrondthreads van de Query Store zijn met betrekking tot het opschonen van queryplanopties van de Query Store (dat wil zeggen STALE_QUERY_THRESHOLD_DAYS en/of MAX_STORAGE_SIZE_MB), kan er een situatie ontstaan waarin een queryvariant en/of de bijbehorende dispatcher-statement voortijdig wordt ontkoppeld. Dit kan leiden tot een schending van toegang tijdens het invoegen of verwijderen van queryvarianten in de Query Store.

Raadpleeg de sectie Opmerkingen van het artikel Hoe Query Store gegevens verzamelt voor meer informatie over Query Store-bewerkingen.

Verwante inhoud

• Uitvoeringsplannen opnieuw compileren
• parameters en het uitvoeringsplan opnieuw gebruiken
• Eenvoudige parameterisatie
• Geforceerde parameterisatie
• queryhints (Transact-SQL)
• Intelligent queryverwerking in SQL-databases
• Parametergevoeligheid
• ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)