Delen via

Optionele optimalisatie van parameterplannen (OPPO)

Van toepassing op:Van toepassing op: SQL Server 2025 (17.x) Preview Azure SQL Database SQL-databasein Microsoft Fabric Preview

De term optionele parameters verwijst naar een specifieke variatie van het parametergevoelige plan (PSP) probleem waarin de gevoelige parameterwaarde die aanwezig is tijdens het uitvoeren van query's, bepaalt of we een zoekopdracht moeten uitvoeren naar of scannen van een tabel. Een eenvoudig voorbeeld zou er ongeveer als volgt uitzien:

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

In dit voorbeeld kiest SQL Server altijd een plan waarmee een tabel Table1wordt gescand, zelfs als er een index is ingeschakeld Table1(col1). Een zoekplan is mogelijk niet mogelijk met NULL's. Hintingtechnieken voor query's, zoals OPTIMIZE FOR, zijn mogelijk niet nuttig voor dit type PSP-probleem, omdat er momenteel geen operator is die tijdens de uitvoering een index seek dynamisch omzet in een scan. Dit soort zoek- en scancombinatie tijdens uitvoering kan mogelijk ook niet effectief zijn, omdat de kardinaliteitsramingen die bij die operator horen waarschijnlijk onjuist zijn. Het resultaat is inefficiënte planningskeuzen en overmatige geheugentoekenningen voor complexere query's met vergelijkbare querypatronen.

De functie Optioneel parameterplanoptimalisatie (OPPO) maakt gebruik van de infrastructuur voor adaptieve planoptimalisatie (Multiplan) die is geïntroduceerd met de optimalisatieverbetering parametergevoelig plan, waarmee meerdere plannen worden gegenereerd op basis van één instructie. Hierdoor kan de functie verschillende veronderstellingen maken, afhankelijk van de parameterwaarden die in de query worden gebruikt. Tijdens de uitvoering van query's selecteert OPPO het juiste plan:

  • waarbij de parameterwaarde IS NOT NULLgebruikmaakt van een zoekplan of iets beters dan een volledig scanplan.
  • waarbij de parameterwaarde is NULL, wordt een scanplan gebruikt.

Als onderdeel van de functiefamilie adaptieve planoptimalisatie die parametergevoelige planoptimalisatie omvat, biedt OPPO een oplossing voor het tweede onderdeel van de multiplan-functieset, waarin dynamische zoekmogelijkheden worden behandeld.

  • Equality predicates

    WHERE column1 = @p

  • Dynamic search

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

Terminologie en hoe het werkt

Term Description
Dispatcher expression Deze expressie evalueert de kardinaliteit van predicaten op basis van runtimeparameterwaarden en routeert de uitvoering naar verschillende queryvarianten.
Dispatcher plan 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 categorieën of domeinen. Het dispatcherplan bevat ook de statistieken die zijn gebruikt om de grenswaarden te berekenen.
Query variant Wanneer het dispatcherplan de kardinaliteit van predicaten evalueert op basis van runtimeparameterwaarden, worden deze bucketizes gemaakt en worden afzonderlijke onderliggende query's gegenereerd die moeten worden uitgevoerd. Deze subquery's worden queryvarianten genoemd. Queryvarianten hebben hun eigen plannen in de plancache en de Query Store. Met andere woorden, door verschillende queryvarianten te gebruiken, bereiken we het doel van meerdere plannen voor één query.

Denk bijvoorbeeld aan een webformulier voor een vastgoedbedrijf dat optioneel filteren op het aantal slaapkamers voor een specifieke woning mogelijk maakt. Een veelvoorkomend antipatroon kan zijn om het optionele filter uit te drukken als:

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

Zelfs als de parameter @bedrooms = 10 wordt gedetecteerd door het gebruik van parametermarkeringen en we weten dat de kardinaliteit voor het aantal slaapkamers waarschijnlijk zeer laag is, produceert de optimizer geen plan dat zoekt naar een index die op de slaapkamerrij bestaat, omdat dat geen geldig plan is voor het geval waarin @bedrooms gelijk is aan NULL. Het gegenereerde plan bevat geen scan van de index.

Stel dat dit kan worden herschreven als twee afzonderlijke instructies. Afhankelijk van de runtimewaarde van de parameter kunnen we ongeveer als volgt evalueren:

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

We kunnen dit bereiken met behulp van de infrastructuur voor optimalisatie van adaptieve plannen, waarmee een dispatcher-plan kan worden gemaakt dat twee queryvarianten verzendt.

Net als het predicaat kardinaliteitsbereik dat door PSP-optimisatie wordt gebruikt, voegt OPPO een voor het systeem bruikbare query-hint toe aan de query-tekst van het plan. Deze hint is niet geldig voor gebruik door een toepassing of als u deze zelf probeert te gebruiken.

Doorgaan met het vorige voorbeeld,

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

OPPO kan twee queryvarianten genereren waarvoor mogelijk de volgende kenmerken zijn toegevoegd in de Showplan XML:

  • @bedrooms is NULL. De queryvariant heeft de oorspronkelijke query gevouwen om een scanplan te verkrijgen.

    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)

Optionele optimalisatie van parameterplannen gebruiken

Als u OPPO wilt inschakelen voor een database, zijn de volgende vereisten vereist:

  • De database moet compatibiliteitsniveau 170 gebruiken.
  • De OPTIONAL_PARAMETER_OPTIMIZATION configuratie van het databasebereik moet zijn ingeschakeld.

De OPTIONAL_PARAMETER_OPTIMIZATION configuratie met databasebereik is standaard ingeschakeld. Dit betekent dat voor een database met compatibiliteitsniveau 170 (de standaardinstelling in SQL Server 2025) standaard OPPO wordt gebruikt.

U kunt ervoor zorgen dat een database GEBRUIKMAAKT van OPPO in SQL Server 2025 door de volgende instructies uit te voeren:

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

Als u optionele optimalisatie van parameterplannen voor een database wilt uitschakelen, schakelt u de configuratie van het OPTIONAL_PARAMETER_OPTIMIZATION databasebereik uit:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Optionele optimalisatie van parameterplannen gebruiken via queryhints

U kunt de DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION queryhint gebruiken om optionele optimalisatie van parameterplannen voor een bepaalde query uit te schakelen. De hints moeten worden opgegeven via de USE HINT component. Zie queryhintsvoor meer informatie.

De hints werken onder elk compatibiliteitsniveau en overschrijven de OPTIONAL_PARAMETER_OPTIMIZATION configuratie met databasebereik.

De DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION queryhint kan rechtstreeks in de query worden opgegeven of via Query Store-hints.

Extended Events

  • optional_parameter_optimization_skipped_reason: treedt op wanneer OPPO besluit dat een query niet in aanmerking komt voor optimalisatie. Deze uitgebreide gebeurtenis volgt hetzelfde patroon als de parameter_sensitive_plan_optimization_skipped_reason-gebeurtenis, die door PSP-optimalisatie wordt gebruikt. Omdat een query zowel PSP-optimalisatie als OPPO-queryvarianten kan genereren, moet u beide gebeurtenissen controleren om te begrijpen waarom één functie is ingeschakeld of geen van beide functies is geactiveerd. De volgende query toont alle mogelijke redenen waarom PSP is overgeslagen:
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;
  • query_with_optional_parameter_predicate: De uitgebreide gebeurtenis volgt hetzelfde patroon als de query_with_parameter_sensitivity gebeurtenis die wordt gebruikt door PSP-optimalisatie. Het omvat de extra velden die beschikbaar zijn in de verbeteringen voor PSP-optimalisatie. Dit bestaat uit het weergeven van het aantal predicaten dat de functie interessant heeft gevonden, het verstrekken van meer details in json-formaat over deze interessante predicaten, evenals of OPPO wordt ondersteund voor het predicaat of de predicaten.

Remarks

  • De ShowPlan XML voor een queryvariant zou er ongeveer uitzien zoals in het volgende voorbeeld, waarbij de geselecteerde predikaten hun respectieve informatie hebben toegevoegd aan het PLAN PER VALUE, optional_predicate hint.
<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">
  • Voorbeelduitvoer van de query_with_optional_parameter_predicate uitgebreide gebeurtenis
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

Related content