Választható paraméterterv-optimalizálás (OPPO)

Vonatkozik a következőkre: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

Az opcionális paraméterterv-optimalizálás (OPPO) javítja a lekérdezésterv minőségét az opcionális paramétereket tartalmazó lekérdezések esetében. Ezekben a lekérdezésekben az optimális végrehajtási terv attól függ, hogy egy paraméter értéke végrehajtási időben van-e NULL . A választható paraméterek kifejezés a paraméterérzékeny terv (PSP) problémájának egy adott változatára utal, amelyben a paraméter értéke a végrehajtáskor határozza meg, hogy a lekérdezés keresésre vagy vizsgálatra van-e szükség.

Áttekintés

Az opcionális paramétereket használó lekérdezések gyakran tartalmaznak olyan predikátumokat, amelyek feltételesen alkalmaznak szűrőket attól függően, hogy meg van-e adva paraméterérték. A gyakori minta a következő:

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

Amikor @p IS NOT NULL, az col1 indexkeresés gyakran a leghatékonyabb kivitelezési terv. Amikor @p IS NULL, a predikátum TRUE-re értékelődik ki, és lehet, hogy egy vizsgálat megfelelőbb lenne. Az OPPO nélkül az SQL Server adatbázismotorjának egyetlen végrehajtási tervet kell lefordítania és gyorsítótáraoznia, amely mindkét esetben érvényes. Mivel a keresési alapú terv nem érvényes, amikor @p IS NULLaz optimalizáló gyakran egy konzervatív vizsgálatalapú tervet választ az összes végrehajtáshoz. Ez a választás nem hatékony tervválasztást és túlzott erőforrás-használatot eredményezhet a szelektív végrehajtásokhoz.

A hagyományos tippelési technikák, például OPTIMIZE FOR ebben a forgatókönyvben nem hatékonyak, mivel a tervnek mindkét paraméterállapot esetében helyesnek kell maradnia.

Az OPPO a Parameter Sensitive Plan (PSP) optimalizálással bevezetett adaptív tervoptimalizáló (Multiplan) infrastruktúrát használja. Ez az infrastruktúra több végrehajtási tervet hoz létre és gyorsítótáraz egyetlen utasításhoz, ami lehetővé teszi, hogy az OPPO különböző feltételezéseket tegyen a lekérdezésben használt paraméterértékek alapján.

Terminológia és működése

Az OPPO az adaptív tervoptimalizálási (Multiplan) keretrendszerre épül, amelyet a paraméterérzékeny terv optimalizálása is használ. A Multiplan használatával az adatbázismotor több végrehajtási tervet is létrehozhat és gyorsítótárazhat egyetlen lekérdezéshez.

Amikor az adatbázismotor egy választható paramétermintát észlel, a következőt hozza létre:

• Egy diszpécserterv
• Egy vagy több lekérdezésvariáns, amelyek mindegyike adott paraméterérték-állapotra van optimalizálva

Végrehajtáskor:

• Az adatbázismotor kiértékeli a paraméter értékét.
• A Multiplan lekérdező szolgáltatás kiválasztja a megfelelő lekérdezési változatot.
• A kijelölt lekérdezésvariáns végrehajtja a parancsot.

Miután az adatbázismotor kiválasztott egy lekérdezésvariánst, leegyszerűsíti a predikátumokat a tényleges paraméterérték alapján. Vegye figyelembe a következő kifejezést:

@p1 IS NULL

Ebben a példában a kifejezés a kiválasztott változat állandó eredményére lesz egyszerűsítve. Ez az állandó eredményátadás lehetővé teszi, hogy az optimalizáló olyan végrehajtási terveket hozzon létre, amelyek nem érvényesek egyetlen újrafelhasználható tervben.

A tervek ily módon történő kiválasztásával az OPPO lehetővé teszi a különböző paraméterállapotok hatékony végrehajtását anélkül, hogy lekérdezési átírásokat vagy manuális lekérdezési tippeket kellene megadnia.

Az OPPO és a PSP optimalizálása a paraméterekkel kapcsolatos tervekkel kapcsolatos problémák különböző változatait kezeli:

• A PSP-optimalizálás az egyenlőségi vagy tartomány-predikátumok esetén a becsült számossági különbségek alapján választja ki a terveket.

• Az OPPO a terveket annak alapján választja ki, hogy egy paraméter értéke milyen.

Az egyes lekérdezések az érintett predikátumoktól függően mindkét vagy bármelyik funkció előnyeit élvezhetik.

Támogatott lekérdezési minták

Az opcionális paraméterterv-optimalizálás olyan lekérdezésekre vonatkozik, amelyekben NULL a paraméterek ellenőrzése hatással van a végrehajtási terv érvényességére. Vegyük például egy alkalmazás webes űrlapját egy olyan ingatlanvállalathoz, amely lehetővé teszi az adott lista hálószobáinak számának opcionális szűrését. Az OPPO a nem kötelező paraméter predikátumokra vonatkozik, például:

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

Még akkor sem, ha a paraméterjelölők ki tudják szippantani a @bedrooms = 10 paramétert, és tudja, hogy a hálószobák számának számossága valószínűleg nagyon alacsony, az optimalizáló nem hoz létre olyan tervet, amely a hálószoba oszlopban található indexre keres, mert ez nem érvényes terv arra az esetre, ahol @bedrooms van NULL. A létrehozott terv nem tartalmazza az index vizsgálatát.

Tegyük fel, hogy ezt a lekérdezést két külön utasításként is átírhatja. A paraméter futásidejű értékétől függően a következő példát értékelheti ki:

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

Ez a funkció a többsíkú infrastruktúra használatával érhető el, amely lehetővé teszi egy olyan diszpécsercsomag létrehozását, amely egy lekérdezésvariánst küld el.

Az OPPO beágyaz egy rendszer által generált PLAN PER VALUE lekérdezési tippet (optional_predicate) a terv metaadataiba, hogy az egyes lekérdezésvariánsokat a paraméterállapotához társítsa. Ez a tipp a rendszer által generált és a terv lekérdezési szövegébe ágyazott. Ez a tipp nem érvényes egy alkalmazás általi használatra vagy manuálisan történő alkalmazásra.

Az előző példával folytatva:

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

Az OPPO két olyan lekérdezésvariánst hozhat létre, amelyekhez a Showplan XML-fájlban a következő attribútumok tartozhatnak:

• @bedrooms az NULL. A lekérdezési változat a paraméterérték alapján összevonja a predikátumokat, lehetővé téve egy szkennelésalapú terv generálását.

  SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms null))

• @bedrooms IS NOT NULL

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

Választható paraméterterv-optimalizálás használata

Az OPPO adatbázishoz való engedélyezéséhez a következő előfeltételek szükségesek:

• Az adatbázisnak a 170-es kompatibilitási szintet kell használnia.
• Az OPTIONAL_PARAMETER_OPTIMIZATION adatbázis-hatókörű konfigurációt engedélyezni kell.

Az OPTIONAL_PARAMETER_OPTIMIZATION adatbázis-hatókörű konfiguráció alapértelmezés szerint engedélyezve van, ezért egy 170-es kompatibilitási szintet használó adatbázis (az SQL Server 2025 (17.x) alapértelmezett értéke) alapértelmezés szerint OPPO-t használ.

Az alábbi utasítások végrehajtásával biztosíthatja, hogy egy adatbázis OPPO-t használjon az SQL Server 2025-ben (17.x):

ALTER DATABASE [<database-name-placeholder>]
SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Egy adatbázis OPPO-jának letiltásához tiltsa le az OPTIONAL_PARAMETER_OPTIMIZATION adatbázis-hatókörű konfigurációt:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Opcionális paraméterterv-optimalizálás használata lekérdezési tippeléssel

A lekérdezési DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION tipp használatával letilthatja az OPPO-t egy adott lekérdezéshez. Adja meg a tippet a USE HINT záradékon keresztül. További információ: Lekérdezési tippek.

Ez a tipp bármilyen kompatibilitási szinten működik, és felülbírálja az OPTIONAL_PARAMETER_OPTIMIZATION adatbázis-hatókörű konfigurációt.

Adja meg a lekérdezési DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION tippet közvetlenül a lekérdezésben vagy a Lekérdezéstár tippeken keresztül.

Bővített események

A hibaelhárításhoz és a diagnosztikához használja az alábbi bővített eseményeket. Ezek az események nem szükségesek a funkció használatához.

• optional_parameter_optimization_skipped_reason: Akkor fordul elő, ha az OPPO úgy dönt, hogy egy lekérdezés nem jogosult az optimalizálásra. Ez a kiterjesztett esemény ugyanazt a mintát követi, mint a parameter_sensitive_plan_optimization_skipped_reason PSP-optimalizálás által használt esemény. Mivel a lekérdezés PSP-optimalizálási és OPPO-lekérdezési változatokat is képes generálni, ellenőrizze mindkét eseményt, hogy megértse, miért lett bekapcsolva valamelyik vagy egyik funkció sem.

  Az alábbi lekérdezés a PSP kihagyásának lehetséges okait mutatja be:

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

• query_with_optional_parameter_predicate: Ez a kiterjesztett esemény ugyanazt a mintát követi, mint a query_with_parameter_sensitivity PSP-optimalizálás által használt esemény. Tartalmazza a PSP-optimalizálási fejlesztésekben elérhető további mezőket.

  Ezek a mezők a következők:

  • a jellemző által érdekesnek talált predikátumok száma,
  • további részletek JSON formátumban az érdekes predikátumokról, és azok használatáról
  • hogy az OPPO támogatott-e a predikátum vagy predikátumok esetében.

Remarks

• Egy lekérdezésvariáns ShowPlan XML-fájlja az alábbi példához hasonlóan néz ki. Azok a predikátumok, amelyeket a funkció kiválaszt, hozzá vannak adva az PLAN PER VALUE (optional_predicate) útmutatóhoz.

<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éldakimenet a query_with_optional_parameter_predicate kiterjesztett eseményből:

  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

Lekérdezési jogosultság és korlátozások

Az OPPO csak a Multiplan optimalizálásra jogosult lekérdezésekre vonatkozik. A funkció nem alkalmazható az alábbi forgatókönyvekben:

• Paraméterek helyett helyi változókat használó lekérdezések
• Lekérdezések összeállítása a OPTION (RECOMPILE)
• A következővel végrehajtott lekérdezések: SET ANSI_NULLS OFF
• Automatikusan paraméterezett utasítások

Kapcsolódó tartalom

• lekérdezésfeldolgozási architektúra útmutatója
• Végrehajtási tervek újrakészítése
• Paraméterek és végrehajtási terv újbóli felhasználása
• Egyszerű paraméterezés
• Kényszerített paraméterezés
• Lekérdezési tippek (Transact-SQL)
• intelligens lekérdezésfeldolgozás SQL-adatbázisokban
• Paraméter érzékenysége
• ALTER DATABASE HATÓKÖRŰ KONFIGURÁCIÓ (Transact-SQL)