Teilen über

Optionale Parameterplanoptimierung (OPPO)

Gilt für: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

Optionale Parameterplanoptimierung (OPPO) verbessert die Abfrageplanqualität für Abfragen, die optionale Parameter enthalten. In diesen Abfragen hängt der optimale Ausführungsplan davon ab, ob ein Parameterwert zur Ausführungszeit NULL beträgt. Der Begriff optionale Parameter bezieht sich auf eine bestimmte Variation des PSP-Problems (Parametersensitiv Plan ), bei dem der Parameterwert zur Ausführungszeit bestimmt, ob für die Abfrage eine Suche oder ein Scan erforderlich ist.

Überblick

Abfragen, die optionale Parameter verwenden, enthalten häufig Prädikate, die Filter bedingt anwenden, basierend darauf, ob ein Parameterwert bereitgestellt wird. Ein gängiges Muster lautet wie folgt:

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

Wenn @p IS NOT NULL, ist ein Index-Seek auf col1 häufig der effizienteste Ausführungsplan. Wenn @p IS NULL, wird das Prädikat zu TRUE ausgewertet, und ein Scan könnte möglicherweise besser geeignet sein. Ohne OPPO muss das SQL Server-Datenbankmodul einen einzelnen Ausführungsplan kompilieren und zwischenspeichern, der für beide Fälle gültig ist. Da ein suchbasierter Plan nicht gültig ist, @p IS NULLwählt der Optimierer häufig einen konservativen scanbasierten Plan für alle Ausführungen aus. Diese Auswahl kann zu ineffizienten Planoptionen und übermäßiger Ressourcennutzung für selektive Ausführungen führen.

Herkömmliche Hinweistechniken wie OPTIMIZE FOR sind in diesem Szenario nicht wirksam, da der Plan für beide Parameterzustände korrekt bleiben muss.

OPPO verwendet die adaptive Infrastruktur zur Planoptimierung (Multiplan), die mit der Optimierung des Parameter Sensitive Plan (PSP) eingeführt wurde. Diese Infrastruktur generiert und zwischenspeichert mehrere Ausführungspläne für eine einzelne Anweisung, wodurch OPPO unterschiedliche Annahmen basierend auf den parameterwerten vornehmen kann, die in der Abfrage verwendet werden.

Terminologie und Funktionsweise

OPPO baut auf dem Framework für die adaptive Planoptimierung (Multiplan) auf, das auch von parametersensitiven Planoptimierungen verwendet wird. Mithilfe von Multiplan kann das Datenbankmodul mehrere Ausführungspläne für eine einzelne Abfrage generieren und zwischenspeichern.

Wenn das Datenbankmodul ein berechtigtes optionales Parametermuster erkennt, wird Folgendes erstellt:

  • Ein Verteilerplan
  • Eine oder mehrere Abfragevarianten, die jeweils für einen bestimmten Parameterwertzustand optimiert sind

Zur Ausführungszeit:

  • Das Datenbankmodul wertet den Parameterwert aus.
  • Der Multiplan-Dispatcher wählt die entsprechende Abfragevariante aus.
  • Die ausgewählte Abfragevariante wird ausgeführt.

Nachdem das Datenbankmodul eine Abfragevariante ausgewählt hat, vereinfacht es Prädikate basierend auf dem tatsächlichen Parameterwert. Betrachten Sie den folgenden Ausdruck:

@p1 IS NULL

In diesem Beispiel wird der Ausdruck auf ein konstantes Ergebnis für die ausgewählte Variante vereinfacht. Diese konstante Ergebnisfaltung ermöglicht es dem Optimierer, Ausführungspläne zu generieren, die in einem einzigen wiederverwendbaren Plan nicht gültig sind.

Durch die Auswahl von Plänen auf diese Weise ermöglicht OPPO die effiziente Ausführung für unterschiedliche Parameterzustände, ohne dass Abfrageumschreibungen oder manuelle Abfragehinweise erforderlich sind.

Oppo und PSP-Optimierung behandeln verschiedene Variationen von parameterbezogenen Planproblemen:

  • Die PSP-Optimierung wählt Pläne basierend auf geschätzten Kardinalitätsunterschieden für Gleichheits- oder Bereichsprädikate aus.

  • OPPO wählt Pläne basierend darauf aus, ob ein Parameterwert ist NULL.

Je nach den beteiligten Prädikaten kann eine einzelne Abfrage entweder von beiden oder nur von einer Funktion profitieren.

Unterstützte Abfragemuster

Optionale Parameterplanoptimierung gilt für Abfragen, bei denen NULL sich Überprüfungen auf Parameter auf die Gültigkeit des Ausführungsplans auswirken. 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. OPPO gilt für disjunktive optionale Parameter-Prädikate, wie zum Beispiel:

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

Selbst wenn Parametermarker den @bedrooms = 10 Parameter sniffen können, und Sie wissen, dass die Kardinalität für die Anzahl der Schlafzimmer wahrscheinlich sehr niedrig ist, erzeugt der Optimierer keinen Plan, der für einen Index sucht, der in der Schlafzimmerspalte vorhanden ist, da dies kein gültiger Plan für den Fall ist, in dem @bedrooms dies der Fall ist NULL. Der generierte Plan enthält keine Überprüfung des Indexes.

Stellen Sie sich vor, wenn Sie diese Abfrage als zwei separate Anweisungen umschreiben könnten. Je nach Laufzeitwert des Parameters können Sie das folgende Beispiel auswerten:

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

Das Feature kann dies mithilfe der Multiplan-Infrastruktur erreichen, die die Erstellung eines Dispatcherplans ermöglicht, der eine Abfragevariante verteilt.

OPPO bettet einen vom System generierten PLAN PER VALUE Abfragehinweis (optional_predicate) in die Planmetadaten ein, um jede Abfragevariante dem Parameterstatus zuzuordnen. Dieser Hinweis wird vom System generiert und in den Abfragetext des Plans eingebettet. Diese Anweisung ist nicht dazu geeignet, von einer Anwendung genutzt oder manuell angewendet zu werden.

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 zusammenfaltet Prädikate basierend auf dem Parameterwert, sodass ein Scan-basierter Plan generiert werden kann.

    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 Konfiguration mit Datenbankbereich ist standardmäßig aktiviert, sodass eine Datenbank mit Kompatibilitätsebene 170 (standard in SQL Server 2025 (17.x)) standardmäßig OPPO verwendet.

Sie können sicherstellen, dass eine Datenbank OPPO in SQL Server 2025 (17.x) 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 OPPO für eine Datenbank zu deaktivieren, deaktivieren Sie die OPTIONAL_PARAMETER_OPTIMIZATION Datenbankbereichskonfiguration:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Verwenden Sie die optionale Parameterplanoptimierung über Abfragehinweise.

Verwenden Sie den DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Abfragehinweis, um OPPO für eine bestimmte Abfrage zu deaktivieren. Geben Sie den Hinweis über die USE HINT Klausel an. Weitere Informationen finden Sie unter Abfragehinweise.

Dieser Hinweis funktioniert unter allen Kompatibilitätsstufen und überschreibt die datenbankspezifische OPTIONAL_PARAMETER_OPTIMIZATION-Konfiguration.

Geben Sie den DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION Abfragehinweis direkt in der Abfrage oder über Abfragespeicherhinweise an.

Erweiterte Ereignisse

Verwenden Sie die folgenden erweiterten Ereignisse für die Problembehandlung und Diagnose. Diese Ereignisse sind nicht erforderlich, um das Feature zu verwenden.

  • 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 Ereignis, das von der parameter_sensitive_plan_optimization_skipped_reason PSP-Optimierung verwendet wird. Da eine Abfrage sowohl PSP-Optimierungs- als auch OPPO-Abfragevarianten generieren kann, überprüfen Sie beide Ereignisse, um zu verstehen, warum eine Funktion oder keine der Funktionen genutzt wurde.

    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: Dieses erweiterte Ereignis folgt demselben Muster wie das Ereignis, das von der query_with_parameter_sensitivity PSP-Optimierung verwendet wird. Es enthält die zusätzlichen Felder, die in den Verbesserungen für die PSP-Optimierung verfügbar sind.

    Diese Felder werden angezeigt:

    • die Anzahl der Prädikate, die das Feature interessant fand,
    • weitere Details im JSON-Format zu den interessanten Prädikaten und
    • ob OPPO für das Prädikat oder die Prädikate unterstützt wird.

Remarks

  • Die ShowPlan-XML für eine Abfragevariante sieht ähnlich wie im folgenden Beispiel aus. Die Prädikate, die das Feature auswählt, werden mit ihren entsprechenden Informationen dem PLAN PER VALUE (optional_predicate) Hinweis hinzugefügt.
<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 :

    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

Abfrageberechtigung und Einschränkungen

OPPO gilt nur für Abfragen, die für die Multiplan-Optimierung berechtigt sind. Das Feature wird nicht in Szenarien angewendet, die Folgendes umfassen:

  • Abfragen, die lokale Variablen anstelle von Parametern verwenden
  • Kompilierte Abfragen mit OPTION (RECOMPILE)
  • Ausgeführte Abfragen mit SET ANSI_NULLS OFF
  • Automatisch parametrisierte Anweisungen

Verwandte Inhalte

  • Last updated on