Dela via

Valfri optimering av parameterplan (OPPO)

Gäller för:Gäller för: SQL Server 2025 (17.x) Förhandsversion Azure SQL DatabaseSQL-databas i Microsoft Fabric Förhandsversion

Termen valfria parametrar refererar till en specifik variant av problemet med parameterkänslig plan (PSP) där det känsliga parametervärdet som finns under frågekörningen styr om vi behöver söka i eller skanna en tabell. Ett enkelt exempel skulle vara ungefär så här:

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

I det här exemplet väljer SQL Server alltid en plan som söker igenom tabellen Table1, även om det finns ett index på Table1(col1). En sökplan kanske inte är möjlig med NULLL:er. Frågeindikatorer, till exempel OPTIMIZE FOR, kanske inte är användbara för den här typen av PSP-problem eftersom det för närvarande inte finns någon operator som dynamiskt ändrar en indexsökning till en genomsökning under själva körningen. Den här typen av sök-skanningskombination> vid körning kan möjligen inte heller vara effektiv, eftersom kardinalitetsuppskattningarna för operatorn troligtvis är felaktiga. Resultatet är ineffektiva planval och överdrivna minnesbidrag för mer komplexa frågor med liknande frågemönster.

Funktionen Valfri optimering av parameterplan (OPPO) använder infrastrukturen för anpassningsbar planoptimering (Multiplan) som introducerades med optimeringsförbättringen för parameterkänslig plan, som genererar flera planer från en enda instruktion. På så sätt kan funktionen göra olika antaganden beroende på de parametervärden som används i frågan. Under körning av frågan väljer OPPO den mest lämpliga planen.

  • där parametervärdet är IS NOT NULL, används en sökplan eller något mer optimalt än en fullständig skanningsplan.
  • där parametervärdet är NULLanvänder den en genomsökningsplan.

Som en del av funktionsfamiljen för adaptiv planoptimering, som omfattar optimering av parameterkänsliga planer, tillhandahåller OPPO en lösning på den andra komponenten i Multiplan-funktionsuppsättningen, som omfattar dynamiska sökfunktioner.

  • Equality predicates

    WHERE column1 = @p

  • Dynamic search

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

Terminologi och hur det fungerar

Term Description
Dispatcher expression Det här uttrycket utvärderar kardinaliteten för predikat baserat på körningsparametervärden och dirigerar körningen till olika frågevarianter.
Dispatcher plan En plan som innehåller dispatcher-uttrycket cachelagras för den ursprungliga frågan. Dispatcher-planen är i princip en samling predikat som har valts av funktionen, med några extra detaljer. För varje predikat som väljs ingår viss information i dispatcherplanen, nämligen de höga och låga gränsvärdena. Dessa värden används för att dela in parametervärden i olika bucketar eller intervall. Dispatcher-planen innehåller också den statistik som användes för att beräkna gränsvärdena.
Query variant När dispatcher-planen utvärderar kardinaliteten för predikat baserat på körningsparametervärden, ordnar den dem i grupper och genererar separata underordnade frågningar som ska köras. Dessa barnfrågor kallas frågevarianter. Frågevarianter har egna planer i plancachen och Query Store. Med andra ord uppnår vi målet med flera planer för en enskild fråga genom att använda olika frågevarianter.

Tänk dig ett exempel på ett webbformulär för en applikation hos ett fastighetsföretag som möjliggör valfri filtrering av antalet sovrum för en specifik annons. Ett vanligt antimönster kan vara att uttrycka det valfria filtret som:

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

Även om parametern @bedrooms = 10 sniffas av användningen av parametermarkörer och vi vet att kardinaliteten för antalet sovrum sannolikt är mycket låg, skapar optimeraren inte en plan som söker efter ett index som finns i sovrumskolumnen eftersom det inte är en giltig plan för fallet där @bedrooms är NULL. Den genererade planen innehåller ingen genomsökning av indexet.

Tänk dig om detta kan skrivas om som två separata instruktioner. Beroende på värdet vid körning för parametern kan vi utvärdera något liknande:

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

Vi kan uppnå detta med hjälp av optimeringsinfrastrukturen för anpassningsbar planoptimering, vilket möjliggör skapandet av en distributionsplan som genomför två frågevarianter.

På samma sätt som det kardinalitetsintervall för predikatet som PSP-optimering använder, bäddar OPPO in ett användbart, systemmässigt frågetips tillsammans med frågetexten i planen. Det här tipset är inte giltigt för användning av ett program eller om du försöker använda det själv.

Fortsätter med föregående exempel,

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

OPPO kan generera två frågevarianter som kan ha följande attribut tillagda i Showplan XML:

  • @bedrooms är NULL. Frågevarianten omformat den ursprungliga frågan för att uppnå en genomsökningsplan.

    SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, valfritt_predikat(@sovrum är NULL))

  • @bedrooms IS NOT NULL

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

Använd valfri parameter för planoptimering

För att aktivera OPPO för en databas krävs följande krav:

  • Databasen måste använda kompatibilitetsnivå 170.
  • Konfigurationen OPTIONAL_PARAMETER_OPTIMIZATION med databasomfattning måste vara aktiverad.

Konfigurationen OPTIONAL_PARAMETER_OPTIMIZATION med databasomfattning är aktiverad som standard. Det innebär att en databas som använder kompatibilitetsnivå 170 (standardvärdet i SQL Server 2025) använder OPPO som standard.

Du kan se till att en databas använder OPPO i SQL Server 2025 genom att köra följande instruktioner:

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

Om du vill inaktivera valfri optimering av parameterplan för en databas inaktiverar du konfigurationen OPTIONAL_PARAMETER_OPTIMIZATION med databasomfattning:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Använda valfri optimering av parameterplan via frågetips

Du kan använda frågetipset DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION för att inaktivera valfri parameterplansoptimering för en viss fråga. Tipsen måste anges via USE HINT -satsen. Mer information finns i Frågetips.

Indikatorerna fungerar på valfri kompatibilitetsnivå och åsidosätter den databasavgränsade konfigurationen OPTIONAL_PARAMETER_OPTIMIZATION.

Frågetipset DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION kan anges direkt i frågan eller via Query Store-tips.

Extended Events

  • optional_parameter_optimization_skipped_reason: Inträffar när OPPO beslutar att en fråga inte är berättigad till optimering. Den här utökade händelsen följer samma mönster som händelsen parameter_sensitive_plan_optimization_skipped_reason som används av PSP-optimering. Eftersom en fråga kan generera både PSP-optimering och OPPO-frågevarianter bör du kontrollera båda händelserna för att förstå varför en eller inget av funktionerna används. Följande fråga visar alla möjliga orsaker till varför PSP hoppades över:
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;
  • query_with_optional_parameter_predicate: Den utökade händelsen följer samma mönster som "query_with_parameter_sensitivity"-händelsen som används av PSP-optimering. Den innehåller de ytterligare fält som är tillgängliga i förbättringarna för PSP-optimering som består av att visa antalet predikater som funktionen fann intressanta, mer information i json-format om de intressanta predikaten, samt om OPPO stöds för predikatet eller predikaten.

Remarks

  • ShowPlan XML för en frågevariant skulle se ut ungefär som i följande exempel, där predikaten som valdes har sin respektive information tillagd i PLAN PER VALUE, optional_predicate tips.
<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">
  • Exempel på utdata från den query_with_optional_parameter_predicate utökade händelsen
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