Parameterabhängige Planoptimierung

Gilt für: SQL Server 2022 (16.x) und höhere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

Die Optimierung des Parameterempfindlichkeitsplans (Parameter Sensitivity Plan, PSP) ist Teil der intelligenten Abfrageverarbeitungs-Features. Dieser wurde für Szenarios entwickelt, in denen ein einzelner zwischengespeicherter Plan für eine parametrisierte Abfrage nicht für alle möglichen eingehenden Parameterwerte optimal ist. Dies ist bei uneinheitlichen Datenverteilungen der Fall. Weitere Informationen finden Sie unter Parameterempfindlichkeit sowie unter Parameter und Wiederverwendung von Ausführungsplänen.

Weitere Informationen zu vorhandenen Problemumgehungen für dieses Problemszenario finden Sie unter:

• Untersuchen und beheben Sie parametersensitive Probleme
• Parameter und Wiederverwendung von Ausführungsplänen
• Abfragen mit PSP-Problemen (Parameter Sensitive Plan, parameterempfindlicher Plan)

Die PSP-Optimierung aktiviert automatisch mehrere aktive zwischengespeicherte Pläne für eine einzelne parametrisierte Anweisung. Zwischengespeicherte Ausführungspläne decken verschiedene Datengrößen basierend auf den kundenseitig angegebenen Laufzeitparameterwert(en) ab.

Grundlegendes zur Parametrisierung

In der SQL Server-Datenbank-Engine verbessert die Verwendung von Parametern oder Parametermarkierungen in Transact-SQL (T-SQL)-Anweisungen die Fähigkeit der relationalen Engine, neue T-SQL-Anweisungen mit vorhandenen, zuvor kompilierten Ausführungsplänen abzugleichen und die Wiederverwendung von Plänen zu fördern. Weitere Informationen finden Sie unter Einfache Parametrisierung.

Sie können das standardmäßige Parametrisierungsverhalten von SQL Server (die einfache Parametrisierung) auch überschreiben, indem Sie angeben, dass alle SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen in einer Datenbank mit bestimmten Einschränkungen parametrisiert werden sollen. Weitere Informationen finden Sie unter Erzwungene Parametrisierung.

Implementierung der PSP-Optimierung

Während der anfänglichen Kompilierung werden über Spaltenstatistikhistogramme uneinheitliche Verteilungen identifiziert und bis zu drei der am stärksten gefährdeten parametrisierten Prädikate bewertet. Anders ausgedrückt: Wenn mehrere Prädikate innerhalb derselben Abfrage die Kriterien erfüllen, wählt die PSP-Optimierung die besten drei aus. Das PSP-Feature begrenzt die Anzahl der ausgewerteten Prädikate, damit der Plancache und der Abfragespeicher (wenn Abfragespeicher aktiviert ist) nicht mit zu vielen Plänen überlastet werden.

Für berechtigte Pläne erzeugt die anfängliche Kompilierung einen Dispatcher-Plan, der die PSP-Optimierungslogik enthält. Dies wird als Dispatcher-Ausdruck bezeichnet. Ein Dispatcher-Plan wird Abfragevarianten auf den Prädikaten für die Grenzwerte des Kardinalitätsbereichs.

Terminology

Elternanfrage

Die ursprüngliche parametrisierte Abfrage, für die die PSP-Optimierung einen Dispatcherplan und Abfragevarianten erzeugt. Abfragevarianten sind Kindabfragen der übergeordneten Abfrage. Abfragespeicher kann über die Zeit mehr als einen Dispatcher-Plan für dieselbe übergeordnete Abfrage speichern.

Dispatcher-Ausdruck

Bewertet die Kardinalität von Prädikaten auf der Grundlage von Runtimeparameterwerten und leitet die Ausführung an verschiedene Abfragevarianten weiter.

Verteilerplan

Ein Plan, der den Dispatcher-Ausdruck enthält, wird für die ursprünglich parametrisierte Abfrage, auch als Elternabfrage bekannt, zwischengespeichert. Der Dispatcher-Plan ist im Wesentlichen eine Sammlung der Prädikate, die das Feature ausgewählt hat, mit einigen zusätzlichen Details. Für jedes ausgewählte Prädikat enthält der Disponentplan die oberen und unteren Grenzwerte. Diese Werte teilen die Parameterwerte in verschiedene Buckets oder Bereiche auf. Der Dispatcherplan enthält auch die Statistiken, die zur Berechnung der Grenzwerte verwendet wurden.

Abfragevariante

Wenn ein Dispatcherplan die Kardinalität von Prädikaten auf der Grundlage von Runtimeparameterwerten auswertet, legt er diese Werte in Buckets ab und generiert separate untergeordnete Abfragen für die Kompilierung und Ausführung. Diese untergeordneten Abfragen werden als Abfragevarianten bezeichnet. Abfragevarianten verfügen über eigene Pläne im Plancache und Abfragespeicher.

Kardinalitätsbereich des Prädikats

Zur Laufzeit wird die Kardinalität jedes Prädikats anhand der Laufzeitparameterwerte ausgewertet. Der Dispatcher ordnet die Kardinalitätswerte bei der Kompilierung in drei Kardinalitätsbereiche für Prädikate ein. Das Feature zur parameterabhängigen Planoptimierung kann z. B. drei Bereiche erstellen, die niedrige, mittlere und hohe Kardinalitätsbereiche darstellen, wie im folgenden Diagramm dargestellt.

Mit anderen Worten: Wenn eine parametrisierte Abfrage anfänglich kompiliert wird, generiert das Feature zur parameterabhängigen Planoptimierung einen Shellplan, der als Dispatcherplan bekannt ist. Der Dispatcher-Ausdruck enthält die Logik, mit der Abfragen auf der Grundlage der Laufzeitwerte von Parametern in Abfragevarianten eingeteilt werden. Wenn die eigentliche Ausführung beginnt, führt der Dispatcher zwei Schritte aus:

• Der Dispatcher wertet seinen Dispatcherausdruck für die festgelegte Reihe von Parametern aus, um den Kardinalitätsbereich zu berechnen.

• Der Dispatcher ordnet diese Bereiche bestimmten Abfragevarianten zu, kompiliert die Varianten und führt sie dann aus. Durch mehrere Abfragevarianten ermöglicht das Feature zur parameterabhängigen Planoptimierung mehrere Pläne für eine einzeln Abfrage.

Die Grenzen des Kardinalitätsbereichs können in der ShowPlan-XML eines Verteilerplans angezeigt werden:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Ein generierter Hinweis zur PSP-Optimierung wird an die SQL-Anweisung in der ShowPlan-XML einer Abfragevariante angefügt. Der Hinweis kann nicht direkt verwendet werden und wird nicht analysiert, wenn er manuell hinzugefügt wird. Dieser Hinweis enthält die folgenden Elemente:

Option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

• ObjectID stammt aus dem Modul (d. h. gespeicherte Prozedur, Funktion, Trigger), zu dem die aktuelle Anweisung gehört; mit der Annahme, dass die Anweisung aus einem Modul generiert wurde. Wenn die Anweisung das Ergebnis von dynamischem oder Ad-hoc-SQL ist (das heißt sp_executesql), ist das Element ObjectID gleich 0.
• QueryVariantID entspricht ungefähr der Kombination von Bereichen für alle Prädikate, die die PSP-Optimierung ausgewählt hat. Wenn eine Abfrage beispielsweise zwei Prädikate enthält, die für PSP geeignet sind und jedes Prädikat drei Bereiche aufweist, gibt es neun Abfragevariantenbereiche mit der Nummer 1–9.
• Prädikatbereich bezeichnet die prädizierte Kardinalitätsbereichsinformationen, die aus dem Dispatcherausdruck generiert werden.

Und innerhalb der ShowPlan-XML einer Abfragevariante (innerhalb des Dispatcher-Elements):

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Remarks

• Ab SQL Server 2025 (17.x) und Datenbankkompatibilität 170 umfasst die PSP-Optimierung die folgenden vier Verbesserungen:

  • Unterstützung für Data Manipulation Language (DML)-Anweisungen wie DELETE, INSERT, , MERGEund UPDATE.
  • Erweiterte Unterstützung für tempdb.
  • Unterstützung für die Auswahl mehrerer zulässiger Prädikate aus derselben Tabelle, wobei pro Spalte höchstens ein Prädikat ausgewählt werden kann.
  • Änderungen an dem query_with_parameter_sensitivity erweiterten Ereignis, das die Felder interesting_predicate_count, max_skewness, psp_optimization_supported und query_type vor den Änderungen in SQL Server 2025 (17.x) und der Datenbankkompatibilität 170 enthielt. Fügen Sie jetzt jedoch die Felder interesting_predicate_count, interesting_predicate_details, psp_optimization_supported und query_type ein. Weitere Informationen finden Sie im Abschnitt "Erweiterte Ereignisse ".

• Das PSP-Optimierungsfeature funktioniert derzeit nur mit Gleichheitsprädikaten.

• Dispatcher-Pläne werden automatisch neu erstellt, wenn sich die Datenverteilung erheblich ändert. Abfragevariantenpläne werden bei Bedarf unabhängig voneinander neu kompiliert, wie jeder andere Abfrageplantyp auch, und unterliegen standardmäßigen Neukompilierungsereignissen. Weitere Informationen zum erneuten Kompilieren finden Sie unter Erneutes Kompilieren von Ausführungsplänen.

• Die sys.query_store_plan Abfragespeicher-Systemkatalogansicht wurde geändert, um zwischen einem normalen kompilierten Plan, einem Verteilerplan und einem Abfragevariantenplan zu unterscheiden. Die neue Abfragespeicher Systemkatalogansicht, sys.query_store_query_variant, enthält Informationen zu den Beziehungen zwischen den ursprünglich parametrisierten Abfragen (auch als übergeordnete Abfragen bezeichnet), Verteilerplänen und deren untergeordneten Abfragevarianten.

• Auf Datenbankkompatibilitätsstufe 160, wenn mehrere zulässige Prädikate Teil derselben Tabellenreferenz sind, wählt die PSP-Optimierung das Prädikat aus, das die größte Datenverzerrung aufweist, basierend auf dem zugrunde liegenden statistischen Histogramm. Zum Beispiel wird mit SELECT * FROM table1 WHERE column1 = @predicate1 AND column2 = @predicate2nur das am stärksten verzerrte Prädikat ausgewählt, weil beide Prädikate auf dieselbe Tabelle verweisen. Wenn die Abfrage jedoch mehrmals auf die Tabelle verweist, zum Beispiel in separaten Verzweigungen eines UNION oder durch Aliase in einem Self-Join, kann die PSP-Optimierung Prädikate aus mehr als einer Tabellenreferenz auswählen.

• Beginnend mit SQL Server 2025 (17.x) und Datenbankkompatibilitätsstufe 170 kann die PSP-Optimierung mehrere zulässige Prädikate aus derselben Tabelle auswählen. Es wird nur ein Prädikat pro Spalte ausgewählt, und die Gesamtzahl der für die Abfrage ausgewählten Prädikate bleibt auf drei begrenzt. Zum Beispiel können mit SELECT * FROM table1 WHERE column1 = @predicate1 AND column2 = @predicate2, beide Prädikate ausgewählt werden, wenn sie die Kriterien für die PSP-Optimierung erfüllen.

• Der ShowPlan-XML-Code für eine Abfragevariante würde dem folgenden Beispiel ähneln, bei dem beide ausgewählten Prädikate ihre jeweiligen Informationen dem PLAN PER VALUE PSP-bezogenen Hinweis hinzugefügt haben.

  <Batch>
    <Statements>
      <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
        <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
        <Dispatcher>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
        </Dispatcher>
        <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">
  

• Sie können die aktuellen Schwellenwerte für die Schiefe, die von der PSP-Optimierungsfunktion verwendet werden, mit einer oder mehreren der folgenden Methoden beeinflussen:

  • Ablaufverfolgungsflags Kardinalitätsschätzer (Cardinality Estimator, CE) wie Ablaufverfolgungsflag 9481 (global, Sitzung oder Abfrageebene)

  • Datenbankbereichskonfigurationsoptionen, die versuchen, das verwendete CE-Modell zu senken oder die Annahmen zu beeinflussen, die das CE-Modell in Bezug auf die Unabhängigkeit mehrerer Prädikate macht. Dies ist besonders nützlich in Fällen, in denen nicht mehrspaltige Statistiken vorhanden sind, was sich auf die Fähigkeit der PSP-Optimierung auswirkt, die Kandidatur dieser Prädikate auszuwerten.

  • Weitere Informationen finden Sie im Abschnitt Erweiterte Korrelationsannahme für mehrere Prädikate des Whitepapers Optimieren Ihrer Abfragepläne mit dem SQL Server 2014 Kardinalitätsschätzer. Das neuere CE-Modell versucht, eine gewisse Korrelation und weniger Unabhängigkeit für die Konjunktion und Disjunktion von Prädikaten anzunehmen. Die Verwendung des Vorversions-CE-Modells kann sich darauf auswirken, wie die Selektivität der Prädikate in einem mehrspaltigen Verknüpfungsszenario berechnet werden kann. Diese Aktion sollte nur für bestimmte Szenarien in Betracht gezogen werden, und es wird nicht empfohlen, das Vorversions-CE-Modell für die meisten Workloads zu verwenden.

• Die PSP-Optimierung kompiliert und führt jede Abfragevariante als separate, vorbereitete Anweisung aus. Abfragevariantenpläne können zwischengespeichert und wiederverwendet werden, sodass nicht für jede Ausführung eine neue Kompilierung erforderlich ist. Da Abfragevarianten separate vorbereitete Anweisungen sind, identifizieren ihre Plan-Cache-Werte object_id das übergeordnete Modul nicht, wenn die übergeordnete Abfrage Teil eines Moduls ist (also gespeicherte Prozedur, Trigger, Funktion usw.). Für eine vorbereitete Anweisung ist der Plan-Cache object_id im Wesentlichen ein berechneter Wert, der auf einem internen Hash des Batch-Textes basiert, und nicht auf der ID eines Objekts in sys.objects. Weitere Informationen finden Sie im Abschnitt Zurückgegebene Tabelle der sys.dm_exec_plan_attributes DMV-Dokumentation.

  Abfragevariantenpläne werden im SQL Plans Cache-Store (CACHESTORE_SQLCP) abgelegt. Der Dispatcherplan bleibt Teil des zwischengespeicherten Plans der ursprünglichen Charge oder des ursprünglichen Moduls. Wenn die Elternabfrage Teil einer gespeicherten Prozedur oder eines anderen Moduls ist, dessen kompilierter Plan im Object Plans Cache-Store (CACHESTORE_OBJCP) gespeichert ist, wird der Dispatcher-Plan in CACHESTORE_OBJCP gespeichert. Wenn die übergeordnete Abfrage Teil eines parametrisierten dynamischen SQL- oder eines geeigneten Ad-hoc-Batches ist, wird der Dispatcher-Plan in CACHESTORE_SQLCP gespeichert.

  Die PSP-Funktion speichert das object_id des Elternmoduls einer Abfragevariante innerhalb des ObjectID Attributs, das Teil des PLAN PER VALUE Hinweises im ShowPlan XML ist. Wenn die übergeordnete Abfrage dynamisch oder ad hoc T-SQL ist, ist dieses Attribut 0. Aggregierte Leistungsstatistiken für zwischengespeicherte Prozeduren, Funktionen und Trigger können weiterhin für ihre jeweiligen Zwecke verwendet werden. Detailliertere ausführungsbezogene Statistiken, wie sie im sys.dm_exec_query_stats DMV zu finden sind, enthalten weiterhin Daten für Abfragevarianten. Die Zuordnung dieser Statistiken zu einem übergeordneten Modul erfordert jedoch eine zusätzliche Verarbeitung des ShowPlan-XML, da der Plan-Cache object_id der Abfragevariante das übergeordnete Modul nicht direkt identifiziert. Laufzeit- und Wartestatistiken für Abfragevarianten können aus dem Abfragespeicher ohne zusätzliche ShowPlan-XML-Parsing abgerufen werden, wenn der Abfragespeicher aktiviert ist.

• Die Objekt-ID des Elternmoduls wird in plan-cachebezogenen sys.dm_exec_* DMVs nicht direkt als object_id der Abfragevariante angezeigt. Der zwischengespeicherte Modulplan, der den Dispatcher enthält, behält die Objekt-ID des Moduls. Bei Abfragevarianten kann die Objekt-ID des Elternmoduls aus dem ObjectID Attribut im PLAN PER VALUE Hinweis durch Verarbeitung des ShowPlan-XML gewonnen werden. Abfragespeicher bietet ein relationales Modell zur Identifikation dieser Beziehungen. Die sys.query_store_query_variant-Katalogansicht identifiziert die ursprüngliche parametrisierte Abfrage mit parent_query_id, den Dispatcherplan mit dispatcher_plan_id und die untergeordnete Abfrage mit query_variant_query_id.

Considerations

• Um die PSP-Optimierung zu aktivieren, verwenden Sie Datenbankkompatibilitätsstufe 160 oder höher für die Datenbank, mit der Sie verbunden sind, wenn Sie die Abfrage ausführen. Die in SQL Server 2025 (17.x) eingeführten PSP-Optimierungsverbesserungen erfordern ein Datenbankkompatibilitätsniveau von 170 oder höher.

• Um zusätzliche Erkenntnisse über das Feature zur parameterabhängigen Planoptimierung zu erhalten, empfehlen wir Ihnen, die Integration des Abfragespeichers zu aktivieren, indem Sie den Abfragespeicher aktivieren. Das folgende Beispiel aktiviert den Abfragespeicher für eine bereits vorhandene Datenbank namens MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

• Wenn Sie die PSP-Optimierung auf Datenbankebene deaktivieren möchten, können Sie die datenbankweit gültige Konfiguration ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF verwenden.

• Wenn Sie die PSP-Optimierung auf Abfrageebene deaktivieren möchten, können Sie den Abfragehinweis DISABLE_PARAMETER_SENSITIVE_PLAN verwenden.

• Wenn die Parametersuche durch Trace-Flag 4136, PARAMETER_SNIFFING datenbankbereichsspezifische Konfiguration oder den USE HINT('DISABLE_PARAMETER_SNIFFING') Abfragehinweis deaktiviert wird, wird die PSP-Optimierung für die zugehörigen Workloads und Ausführungskontexte deaktiviert. Weitere Informationen finden Sie unter Abfragehinweise und ALTER DATABASE SCOPED CONFIGURATION.

• Die im Plancache gespeicherte Anzahl individueller Planvarianten pro Dispatcher ist beschränkt, um eine Überfrachtung des Caches zu vermeiden. Der interne Schwellenwert ist nicht dokumentiert. Da jeder SQL-Batch das Potenzial hat, mehrere Pläne zu erstellen, und jeder Abfragevariantenplan verfügt über einen unabhängigen Eintrag im Plancache, ist es möglich, die standardmäßige maximale Anzahl zulässiger Planeinträge zu erreichen. Wenn die Plan-Cache-Verdrängungsrate merklich hoch ist oder die Größen der CACHESTORE_OBJCP u CACHESTORE_SQLCPCache-Speicher übermäßig sind, sollten Sie die Anwendung des Ablaufverfolgungskennzeichens 174 in Betracht ziehen.

• Die Anzahl der im Abfragespeicher gespeicherten individuellen Planvarianten für eine Abfrage wird durch die Konfigurationsoption max_plans_per_query beschränkt. Da Abfragevarianten mehr als einen Plan haben können, können insgesamt 200 Pläne pro Abfrage im Abfragespeicher vorhanden sein. Diese Zahl umfasst alle Abfragevariantenpläne für alle Dispatcher, die zu einer übergeordneten Abfrage gehören. Erwägen Sie, die max_plans_per_query Abfragespeicher-Konfigurationsoption zu erhöhen.

  • Ein Beispiel dafür, wie die Anzahl der eindeutigen Pläne das max_plans_per_query Standardlimit des Abfragespeichers überschreiten kann, wäre ein Szenario, in dem Sie folgendes Verhalten zeigen. Nehmen wir an, Sie haben eine Abfrage mit einer Abfrage-ID von 10, die zwei Dispatcher-Pläne hat und jeder Dispatcher-Plan hat jeweils 20 Abfragevarianten (insgesamt 40 Abfragevarianten). Die Gesamtzahl der Pläne für Abfrage-ID 10 beträgt 40 Pläne für die Abfragevarianten und die beiden Dispatcher-Pläne. Es ist auch möglich, dass die übergeordnete Abfrage selbst (Abfrage-ID 10) 5 reguläre, nicht-dispatcher Pläne haben kann. Dies ergibt 47 Pläne (40 aus Abfragevarianten, 2 Dispatcher und 5 nicht mit PSP zusammenhängende Pläne). Wenn außerdem jede Abfragevariante durchschnittlich fünf Pläne hat, ist es in diesem Szenario möglich, mehr als 200 Pläne im Abfragespeicher für eine übergeordnete Abfrage zu haben. Dies hängt auch von einer starken Datenverzerrung in den Datensätzen ab, auf die sich diese Beispielabfrage möglicherweise bezieht.

• Bei jeder Abfragevariantenzuordnung zu einem bestimmten Dispatcher gilt:

  • query_plan_hash ist einzigartig. Diese Spalte ist in sys.dm_exec_query_stats und in anderen dynamischen Verwaltungssichten sowie in Katalogtabellen verfügbar.
  • plan_handle ist einzigartig. Diese Spalte ist in sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans und in anderen dynamischen Verwaltungssichten und -funktionen sowie in Katalogtabellen verfügbar.
  • Das query_hash ist auch bei anderen Varianten gebräuchlich, die demselben Dispatcher zugeordnet sind, was es ermöglicht, die aggregierte Ressourcennutzung für Abfragen zu bestimmen, die sich nur durch die Werte der Eingabeparameter unterscheiden. Diese Spalte ist in sys.dm_exec_query_stats, sys.query_store_query und in anderen dynamischen Verwaltungssichten sowie in Katalogtabellen verfügbar.
  • Das sql_handle ist aufgrund der speziellen PSP-Optimierungsbezeichner, die während der Kompilierung dem Abfragetext hinzugefügt werden, einzigartig. Diese Spalte ist in sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans und in anderen dynamischen Verwaltungssichten und -funktionen sowie in Katalogtabellen verfügbar. Die gleichen Handleinformationen stehen im Abfragespeicher in der Katalogtabelle last_compile_batch_sql_handle als Spalte sys.query_store_query zur Verfügung.
  • Die Abfrage-ID (query_id) ist im Abfragespeicher eindeutig. Diese Spalte ist in sys.query_store_query und in anderen Katalogtabellen des Abfragespeichers verfügbar.

Planerzwingung im Abfragespeicher

Verwendet die gleichen gespeicherten Prozeduren (sp_query_store_force_plan und sp_query_store_unforce_plan), um mit Dispatcher- oder Variantenplänen zu arbeiten.

Wenn du einen Query-Variantenplan erzwingst, erzwingst du nicht den zugehörigen Dispatcherplan. Wenn ein Dispatcher erzwungen wird, gelten nur Varianten aus diesem Dispatcher als geeignet für die Verwendung:

• Zuvor erzwungene Varianten von anderen Dispatchern werden inaktiv, behalten aber den Status Erzwungen, bis der zugehörige Dispatcher erneut erzwungen wird.
• Zuvor erzwungene Varianten im gleichen Dispatcher, die inaktiv geworden sind, werden erneut erzwungen.

Verhalten von Abfragespeicher Abfragehinweisen

• Wenn ein Abfragespeicher-Hinweis zu einer Abfragevariante (Unterabfrage) hinzugefügt wird, wird der Hinweis auf dieselbe Weise wie bei einer Nicht-PSP-Abfrage angewendet. Abfragevarianten-Hinweise haben einen höheren Vorrang, wenn ein Hinweis auch auf die übergeordnete Abfrage im Abfragespeicher angewendet wurde.

• Wenn der übergeordneten Abfrage ein Abfragespeicher-Hinweis hinzugefügt wird und die untergeordnete Abfrage (Abfragevariante) keinen Abfragespeicher-Hinweis enthält, erbt die untergeordnete Abfrage (Abfragevariante) den Hinweis von der übergeordneten Abfrage.

• Wenn ein Abfragespeicher-Abfragehinweis aus der übergeordneten Abfrage entfernt wird, werden auch die untergeordneten Abfragen (Abfragevarianten) entfernt.

• Wenn der übergeordneten Abfrage ein RECOMPILE-Hinweis hinzugefügt wird, generiert das System nicht-PSP-Pläne, nachdem vorhandene Abfragevariantenpläne aus dem Plancache entfernt wurden, da das PSP-Feature nicht für Abfragen mit einem RECOMPILE-Hinweis ausgeführt wird.

• Abfragespeicher-Ergebnishinweise können mithilfe der erweiterten Ereignisse query_store_hints_application_success und query_store_hints_application_failed-Ereignisse beobachtet werden. Für die sys.query_store_query_hints-Tabelle enthält sie Informationen zu dem angewendeten Abfragehinweis. Wenn der Hinweis nur auf eine übergeordnete Abfrage angewendet wurde, enthält der Systemkatalog die Hinweisinformationen für die übergeordnete Abfrage, aber nicht für die untergeordneten Abfragen, obwohl die untergeordneten Abfragen den Hinweis der übergeordneten Abfrage erben.

PSP mit Abfragehinweisen und Planzwangsverhalten lässt sich in der folgenden Tabelle zusammenfassen:

¹ Automatische Plankorrekturkomponente der automatischen Optimierungsfunktion

Erweiterte Ereignisse

• parameter_sensitive_plan_optimization_skipped_reason: Wird ausgelöst, wenn das Feature für den parameterempfindlichen Plan übersprungen wird. Verwenden Sie dieses Ereignis, um zu überwachen, warum die PSP-Optimierung übersprungen wird.

  Die folgende Abfrage zeigt alle möglichen Gründe, warum PSP übersprungen wurde:

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

• parameter_sensitive_plan_optimization: tritt auf, wenn eine Abfrage die PSP-Optimierungsfunktion verwendet. Nur für den Debugkanal. Was Sie noch interessieren könnte:

  • is_query_variant: beschreibt, ob es sich um einen Dispatcher-Plan (übergeordnetes Element) oder einen Query-Variant-Plan (untergeordnetes Element) handelt
  • predicate_count: Anzahl der von PSP ausgewählten Prädikate
  • query_variant_id: zeigt die ID der Abfragevariante an. Ein Wert von 0 bedeutet, dass das Objekt ein Dispatcherplan (übergeordnetes Element) ist.

• query_with_parameter_sensitivity: Wenn dieses Ereignis ausgelöst wird, zeigt es die Anzahl der Prädikate an, die das Feature interessant fand. Es bietet weitere Details im JSON-Format zu den interessanten Prädikaten sowie Informationen darüber, ob PSPO für das Prädikat oder die Prädikate unterstützt wird.

• Beispielausgabe des erweiterten Ereignisses query_with_parameter_sensitivity

SQL Server-Überwachungsverhalten

Die PSP-Optimierung stellt Prüfdaten für die Dispatcher-Plananweisung und alle Abfragevarianten bereit, die dem Dispatcher zugeordnet sind. Die additional_information-Spalte in der SQL Server-Überwachung stellt auch die entsprechenden T-SQL-Stapelinformationen für Abfragevarianten bereit. Nehmen wir die MyNewDatabase-Datenbank als Beispiel: Wenn diese Datenbank eine Tabelle mit dem Namen T2 und eine gespeicherte Prozedur mit dem Namen usp_test, nach der Ausführung der gespeicherten Prozedur usp_test hat, könnte das Audit-Protokoll die folgenden Einträge enthalten:

Bekannte Probleme

Resolved

Zugriffsverletzungs-Exception kann unter bestimmten Bedingungen bei lesbaren sekundären Replikaten auftreten.

(Im Januar 2026 behoben)

Bei Abfragen, die die folgenden Bedingungen erfüllen, kann es zu einer Zugriffsverletzung kommen, wenn eine parametersensitive Plan (PSP) Abfragevariante den persistenten Zustand ihrer übergeordneten Dispatcher-Anweisung nicht bestimmen kann:

• Ausgeführt auf einem sekundären Replikat
• Sensitiv für Parameterermittlung
• Anspruch auf eine parameter-sensible Planoptimierung (PSP)

Eine Zugriffsverletzungs-Ausnahme tritt in Abfragespeicher in SQL Server 2022 unter bestimmten Bedingungen auf.

(Im März 2023 behoben)

Dieses Problem entstand aufgrund einer Race Condition, die auftreten kann, wenn die Laufzeitstatistiken für eine ausgeführte Abfrage von der In-Memory-Repräsentation des Abfragespeicher (gefunden im MEMORYCLERK_QUERYDISKSTORE_HASHMAP Speicherverwalter) auf die Version des Abfragespeicher auf der Festplatte übertragen werden. Die Laufzeitstatistiken (angezeigt als Runtime Stats) werden für einen Zeitraum im Speicher gehalten, der durch die DATA_FLUSH_INTERVAL_SECONDS-Option der SET QUERY_STORE-Anweisung festgelegt wird (der Standardwert ist 15 Minuten). Sie können das Dialogfeld des -Abfragespeichers verwenden, um einen Wert für Datenleerungsintervall (Minuten) einzugeben, der intern in Sekunden konvertiert wird. Wenn der Speicher des Systems ausgelastet ist, können die Laufzeitstatistiken früher als mit der DATA_FLUSH_INTERVAL_SECONDS-Option festgelegt auf die Festplatte geschrieben werden. Wenn zusätzliche Abfragespeicher-Hintergrundthreads im Zusammenhang mit der Bereinigung des Abfragespeicher-Abfrageplans (d. h. STALE_QUERY_THRESHOLD_DAYS und/oder MAX_STORAGE_SIZE_MB Abfragespeicheroptionen) und Abfragen aus dem Abfragespeicher vorhanden sind, gibt es ein Szenario, in dem eine Abfragevariante und/oder die zugehörige Dispatcher-Anweisung vorzeitig dereferenziert werden kann. Dies kann zu einer Zugriffsverletzung während des Einfügens oder Löschens von Abfragevarianten in den Abfragespeicher führen.

Weitere Informationen zu Abfragespeicher Vorgängen finden Sie im AbschnittHinweise im Artikel Wie Abfragespeicher Daten sammelt.

Verwandte Inhalte

• Erneutes Kompilieren von Ausführungsplänen
• Parameter und Wiederverwendung von Ausführungsplänen
• Einfache Parametrisierung
• Erzwungene Parametrisierung
• Abfragehinweise (Transact-SQL)
• Intelligente Abfrageverarbeitung in SQL-Datenbanken
• Parameterempfindlichkeit
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)