Freigeben über

Optionale Parameterplanoptimierung (OPPO)

Gilt für:Gilt für: SQL Server 2025 (17.x) Vorschau Azure SQL-DatenbankSQL-Datenbank in Microsoft Fabric

Der Begriff optionale Parameter bezieht sich auf eine bestimmte Variante des PSP-Problems (Parametersensitiv Plan ), bei dem der vertrauliche Parameterwert, der während der Abfrageausführung vorhanden ist, steuert, ob eine Suche in eine Tabelle durchgeführt oder gescannt werden muss. Ein einfaches Beispiel wäre etwa wie folgt:

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

In diesem Beispiel wählt SQL Server immer einen Plan aus, der die Tabelle Table1 scannt, auch wenn ein Index auf Table1(col1) vorhanden ist. Möglicherweise ist ein Suchplan mit NULLs nicht möglich. Abfragehinweistechniken, wie OPTIMIZE FOR, sind für diese Art von PSP-Problem möglicherweise nicht hilfreich, da derzeit kein Operator vorhanden ist, der während der Ausführung eine Index-Suche dynamisch in einen Scan umwandelt. Diese Art von Such- und Scan-Kombination> zur Laufzeit ist möglicherweise auch nicht wirksam, da die Kardinalitätsschätzungen auf diesem Operator wahrscheinlich ungenau sind. Das Ergebnis ist ineffiziente Planoptionen und übermäßige Speichererteilungen für komplexere Abfragen mit ähnlichen Abfragemustern.

Die Optionale Parameterplanoptimierung (OPPO) verwendet die Adaptive Planoptimierungsinfrastruktur (Multiplan), die mit der Optimierung des Parametersensitiven Plans eingeführt wurde und mehrere Pläne aus einer einzigen Anweisung generiert. Dadurch kann das Feature unterschiedliche Annahmen abhängig von den parameterwerten vornehmen, die in der Abfrage verwendet werden. Während der Abfrageausführung wählt OPPO den entsprechenden Plan aus:

  • Wenn der Parameterwert IS NOT NULL erfüllt ist, wird ein Suchplan verwendet oder ein Plan, der optimaler ist als ein vollständiger Scanplan.
  • wobei der Parameterwert lautet NULL, wird ein Scanplan verwendet.

Im Rahmen der Featurefamilie für die adaptive Planoptimierung, die die Parameter-sensible Planoptimierung umfasst, bietet OPPO eine Lösung für die zweite Komponente des Multiplan-Featuresatzes, die speziell dynamische Suchfunktionen abdeckt.

  • Gleichheits-Prädikate

    WHERE column1 = @p

  • Dynamische Suche

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

Terminologie und Funktionsweise

Begriff BESCHREIBUNG
Dispatcher-Ausdruck Dieser Ausdruck wertet die Kardinalität von Prädikaten basierend auf Laufzeitparameterwerten aus und leitet die Ausführung an verschiedene Abfragevarianten weiter.
Verteilerplan Ein Plan, der den Dispatcherausdruck enthält, wird für die ursprüngliche Abfrage zwischengespeichert. Der Dispatcherplan ist im Wesentlichen eine Sammlung der Prädikate, die von der Funktion ausgewählt wurden, mit einigen weiteren Details. Für jedes ausgewählte Prädikat sind einige der im Dispatcherplan enthaltenen Details die hohen und niedrigen Grenzwerte. Diese Werte werden verwendet, um Parameterwerte in verschiedene Buckets oder Bereiche aufzuteilen. Der Verteilerplan enthält auch die Statistiken, die zum Berechnen der Grenzwerte verwendet wurden.
Abfragevariante Wenn der Dispatcherplan die Kardinalität von Prädikaten basierend auf Laufzeitparameterwerten auswertet, werden sie bucketisiert und separate untergeordnete Abfragen generiert, die ausgeführt werden sollen. Diese untergeordneten Abfragen werden als Abfragevarianten bezeichnet. Abfragevarianten verfügen über eigene Pläne im Abfrageplan-Cache und im Abfragestore. Anders ausgedrückt: Mit unterschiedlichen Abfragevarianten erreichen wir das Ziel mehrerer Pläne für eine einzelne Abfrage.

Betrachten Sie beispielsweise ein Webformular einer Anwendung für ein Immobilienunternehmen, das eine optionale Filterung nach der Anzahl der Schlafzimmer für ein bestimmtes Angebot ermöglicht. Ein gängiges Antipattern könnte sein, den optionalen Filter wie folgt auszudrücken:

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

Auch wenn der Parameter @bedrooms = 10 durch die Verwendung von Parametermarkierungen erschnüffelt wird und wir wissen, dass die Kardinalität für die Anzahl der Schlafzimmer wahrscheinlich sehr niedrig ist, erzeugt der Optimierer keinen Plan, der einen Index verwendet, der in der Schlafzimmerspalte vorhanden ist, da dies kein gültiger Plan für den Fall ist, wenn @bedroomsNULL ist. Der generierte Plan enthält keine Überprüfung des Indexes.

Stellen Sie sich vor, ob dies als zwei separate Anweisungen umgeschrieben werden könnte. Je nach Laufzeitwert des Parameters könnten wir etwas wie folgt auswerten:

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

Wir können dies erreichen, indem wir die adaptive Planoptimierungsinfrastruktur verwenden, die die Erstellung eines Dispatcherplans ermöglicht, der zwei Abfragevarianten verteilt.

Ähnlich wie der Prädikatenkardinalitätsbereich, den die PSP-Optimierung verwendet, bettet OPPO einen systemnutzbaren Abfragehinweis zusammen mit dem Abfragetext des Plans ein. Dieser Hinweis ist nicht gültig für die Verwendung durch eine Anwendung oder wenn Sie versuchen, ihn selbst zu verwenden.

Weiter mit dem vorherigen Beispiel,

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

OPPO kann zwei Abfragevarianten generieren, die ihnen in der Showplan-XML möglicherweise die folgenden Attribute hinzugefügt haben:

  • @bedrooms ist NULL. Die Abfragevariante hat die ursprüngliche Abfrage gefaltet , um einen Scanplan zu erzielen.

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

Verwenden der optionalen Parameterplanoptimierung

Um OPPO für eine Datenbank zu aktivieren, sind die folgenden Voraussetzungen erforderlich:

  • Die Datenbank muss die Kompatibilitätsebene 170 verwenden.
  • Die OPTIONAL_PARAMETER_OPTIMIZATION Konfiguration mit Datenbankbereich muss aktiviert sein.

Die OPTIONAL_PARAMETER_OPTIMIZATION Datenbankbereichskonfiguration ist standardmäßig aktiviert. Dies bedeutet, dass eine Datenbank, die Kompatibilitätsebene 170 verwendet (standard in SQL Server 2025), standardmäßig OPPO verwendet.

Sie können sicherstellen, dass eine Datenbank OPPO in SQL Server 2025 verwendet, indem Sie die folgenden Anweisungen ausführen:

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

Um die optionale Parameterplanoptimierung für eine Datenbank zu deaktivieren, deaktivieren Sie die OPTIONAL_PARAMETER_OPTIMIZATION datenbankübergreifende Konfiguration.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Verwenden der optionalen Parameterplanoptimierung über Abfragehinweise

Sie können den Abfragehinweis verwenden, um die DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Optimierung des optionalen Parameterplans für eine bestimmte Abfrage zu deaktivieren. Die Hinweise müssen über die USE HINT Klausel angegeben werden. Weitere Informationen finden Sie unter Abfragehinweise.

Die Hinweise funktionieren unter allen Kompatibilitätsstufen und überschreiben die OPTIONAL_PARAMETER_OPTIMIZATION datenbankübergreifende Konfiguration.

Der DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Abfragehinweis kann direkt in der Abfrage oder über Abfragespeicherhinweise angegeben werden.

Erweiterte Ereignisse

  • optional_parameter_optimization_skipped_reason: Tritt auf, wenn OPPO entscheidet, dass eine Abfrage nicht zur Optimierung berechtigt ist. Dieses erweiterte Ereignis folgt demselben Muster wie das parameter_sensitive_plan_optimization_skipped_reason Ereignis, das von der PSP-Optimierung verwendet wird. Da eine Abfrage sowohl PSP-Optimierungs- als auch OPPO-Abfragevarianten generieren kann, sollten Sie beide Ereignisse überprüfen, um zu verstehen, warum ein oder keines der Features beteiligt ist. Die folgende Abfrage zeigt alle möglichen Gründe, warum PSP übersprungen wurde:
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;
  • query_with_optional_parameter_predicate: Das erweiterte Ereignis folgt demselben Muster wie das query_with_parameter_sensitivity Ereignis, das von der PSP-Optimierung verwendet wird. Es enthält die zusätzlichen Felder, die in den Verbesserungen für die PSP-Optimierung verfügbar sind. Diese bestehen darin, die Anzahl der Prädikate anzuzeigen, die das Feature als interessant befunden hat. Außerdem werden weitere Details im JSON-Format zu den interessanten Prädikaten angezeigt, sowie ob OPPO für das Prädikat oder Prädikate unterstützt wird oder nicht.

Bemerkungen

  • Der ShowPlan-XML-Code für eine Abfragevariante würde dem folgenden Beispiel ähneln, in dem die ausgewählten Prädikate ihre jeweiligen Informationen zum PLAN PER VALUE hinzugefügt haben, optional_predicate-Hinweis.
<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">
  • Beispielausgabe des erweiterten Ereignisses query_with_optional_parameter_predicate
Feld Wert
optionale_Parameteroptimierung_unterstützt Richtig
optionale_Parameter_Prädikat_Anzahl 3
Prädikatdetails {"Prädikate":[{"Schiefe":1005.53},{"Schiefe":1989.00},{"Schiefe":1989.00}]}
Abfrage_typ 193

Verwandte Inhalte