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

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 lekérdezés végrehajtása során meglévő bizalmas paraméterérték határozza meg, hogy keresnünk kell-e egy táblát, vagy be kell-e vizsgálnunk. Egy egyszerű példa a következőhöz hasonló:

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

Ebben a példában az SQL Server mindig olyan tervet választ, amely megvizsgálja a táblát Table1, még akkor is, ha index van rajta Table1(col1). Lehetséges, hogy a keresési terv nem lehetséges az NULL-ekkel. Előfordulhat, hogy a OPTIMIZE FOR lekérdezési tippelési technikák nem hasznosak az ilyen típusú PSP-problémák esetében, mivel jelenleg nincs olyan operátor, amely a végrehajtás során dinamikusan módosítja az indexkeresést szkenneléssé. Ez a fajta keresési és vizsgálati> kombináció futásidőben szintén nem biztos, hogy hatékony, mert az operátor tetején lévő számosságbecslések valószínűleg pontatlanok lennének. Az eredmény nem hatékony tervválasztás és túlzott memóriakihasználtság a hasonló lekérdezési mintákkal rendelkező összetettebb lekérdezésekhez.

Az opcionális paraméterterv-optimalizálási (OPPO) funkció a paraméterérzékeny terv optimalizálási fejlesztésével bevezetett adaptív tervoptimalizálási (Multiplan) infrastruktúrát használja, amely egyetlen utasításból több csomagot hoz létre. Ez lehetővé teszi, hogy a szolgáltatás a lekérdezésben használt paraméterértékek alapján különböző feltételezéseket tegyen. A lekérdezés végrehajtásának ideje alatt az OPPO kiválasztja a megfelelő tervet:

• ahol a paraméter értéke IS NOT NULLegy keresési tervet vagy egy teljes vizsgálati tervnél optimálisabbat használ.
• ahol a paraméter értéke van NULL, egy vizsgálati tervet használ.

Az adaptív tervoptimalizálási funkciócsalád részeként, amely tartalmazza a paraméterérzékeny csomagok optimalizálását, az OPPO megoldást kínál a Multiplan funkciókészlet második összetevőjére, amely a dinamikus keresési képességeket fedi le.

• Egyenlőségi predikátumok

  WHERE column1 = @p
  

• Dinamikus keresés

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

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

Term	Description
Diszpécser kifejezés	Ez a kifejezés a futásidejű paraméterértékek alapján kiértékeli a predikátumok számosságát, és a végrehajtást különböző lekérdezésvariánsokra irányítja.
Diszpécser terv	Az eredeti lekérdezéshez az a terv van gyorsítótárazva, amely tartalmazza a diszpécserkifejezést. A diszpécsercsomag lényegében a funkció által kiválasztott predikátumok gyűjteménye, néhány további részlettel. Minden egyes kiválasztott predikátum esetében a diszpécsertervben szereplő részletek között szerepel néhány, mint például a magas és az alacsony határértékek. Ezek az értékek különböző gyűjtőkre vagy tartományokra osztják a paraméterértékeket. A diszpécserterv tartalmazza a határértékek kiszámításához használt statisztikákat is.
Lekérdezésvariáns	Mivel az ütemező terv a futásidei paraméterértékek alapján értékeli ki a predikátumok számosságát, csoportosítja őket, és külön gyermeklekérdezéseket hoz létre a futtatáshoz. Ezeket a gyermek lekérdezéseket lekérdezésvariánsoknak nevezzük. A lekérdezésvariánsok saját tervekkel rendelkeznek a terv gyorsítótárában és a Lekérdezéstárban. Más szavakkal, különböző lekérdezési változatok használatával érjük el azt a célt, hogy egyetlen lekérdezéshez több terv is legyen.

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. Gyakori antipattern lehet például az opcionális szűrő kifejezése így:

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

Még ha a paramétert @bedrooms = 10 a paraméterjelölők használata észleli is, és tudjuk, hogy a hálószobák számossága valószínűleg nagyon alacsony, az optimalizáló nem készít keresési tervet a hálószoba oszlop indexére, mert ez nem érvényes terv arra az esetre, ahol @bedroomsNULL. A létrehozott terv nem tartalmazza az index vizsgálatát.

Tegyük fel, hogy ezt két külön utasításként lehet újraírni. A paraméter futásidejű értékétől függően a következőhöz hasonlót értékelhetünk ki:

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

Ezt az adaptív tervoptimalizálási infrastruktúrával érhetjük el, amely lehetővé teszi egy olyan diszpécsercsomag létrehozását, amely két lekérdezésvariánst küld el.

A PSP-optimalizálás által használt predikátum-számosságtartományhoz hasonlóan az OPPO beágyaz egy rendszer használható lekérdezési tippet a terv lekérdezési szövegéhez. Ez a tipp nem érvényes egy alkalmazás általi használatra, vagy ha saját maga próbálja használni.

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és változata átalakította az eredeti lekérdezést egy szkennelési terv eléréséhez.

  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 azt jelenti, hogy egy 170-es kompatibilitási szintet használó adatbázis (az SQL Server 2025 alapértelmezett beállítása) 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:

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

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Ha le szeretné tiltani egy adatbázis opcionális paraméterterv-optimalizálását, tiltsa le az OPTIONAL_PARAMETER_OPTIMIZATION adatbázis-hatókörű konfigurációt:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Választható paraméterterv-optimalizálás használata lekérdezési tippek használatával

A lekérdezési tipp segítségével letilthatja az DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION adott lekérdezés opcionális paraméterterv-optimalizálását. A tippeket a USE HINT záradékon keresztül kell megadni. További információ: Lekérdezési tippek.

A tippek bármilyen kompatibilitási szinten működnek, és felülbírálják az OPTIONAL_PARAMETER_OPTIMIZATION adatbázis-hatókörű konfigurációt.

A DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION lekérdezési tipp közvetlenül a lekérdezésben vagy a Lekérdezéstárban használható tippeken keresztül adható meg.

Bővített események

• 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 PSP-optimalizálás által használt parameter_sensitive_plan_optimization_skipped_reason esemény. Mivel egy lekérdezés képes PSP-optimalizálást és OPPO-lekérdezésvariánsokat is generálni, érdemes ellenőriznie mindkét eseményt, hogy megértse, miért van aktiválva az egyik funkció vagy egyik sincs aktiválva.

  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: A kiterjesztett esemény ugyanazt a mintát követi, mint a PSP-optimalizálás által használt query_with_parameter_sensitivity esemény. Tartalmazza a PSP-optimalizálási fejlesztésekben elérhető további mezőket, amelyek a funkció által érdekesnek talált predikátumok számának megjelenítéséből állnak, az érdekes predikátumokkal kapcsolatos további részletek json formátumban, valamint ha az OPPO támogatott a predikátumhoz vagy a predikátumokhoz.

Remarks

• Egy lekérdezésváltozat ShowPlan XML-je az alábbi példához hasonlóan nézne ki, ahol a kiválasztott predikátumok hozzá vannak adva az értékenkénti tervhez, optional_predicate javaslattal.

<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

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)