Parameterabhängige Planoptimierung

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

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 parameterabhängige Planoptimierung die ersten drei aus. Das Feature zur parameterabhängigen Planoptimierung begrenzt die Anzahl der ausgewerteten Prädikate, um zu vermeiden, dass der Plancache und der Abfragespeicher (wenn Abfragespeicher aktiviert ist) mit zu vielen Plänen überfüllt 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.

Terminologie

Dispatcher-Ausdruck

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

Dispatcher-Plan

Ein Plan, der den Dispatcherausdruck enthält, wird für die ursprüngliche Abfrage zwischengespeichert. Der Dispatcher-Plan ist im Wesentlichen eine Sammlung der Prädikate, die vom Feature ausgewählt wurden, mit ein paar zusätzlichen Details. Für jedes ausgewählte Prädikat sind einige der Details, die in den Dispatcherplan aufgenommen werden, die hohen und niedrigen Grenzwerte. Diese Werte werden verwendet, um die Parameterwerte in verschiedene Buckets oder Bereiche zu unterteilen. 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 Runtime wird die Kardinalität jedes Prädikats anhand der Runtimeparameterwerte 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 haben. 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>

Hinweise

• 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 (Transact-SQL) 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 (Transact-SQL), enthält Informationen zu den Beziehungen zwischen den ursprünglich parametrisierten Abfragen (auch als übergeordnete Abfragen bezeichnet), Verteilerplänen und deren untergeordneten Abfragevarianten.

• Wenn mehrere Prädikate vorhanden sind, die Teil derselben Tabelle sind, wählt die PSP-Optimierung das Prädikat aus, das auf der Grundlage des zugrundeliegenden Statistikhistogramms die größte Datenschiefe aufweist. Zum Beispiel wird bei SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, da beide (column1 = @predicate1 und column2 = @predicate2) aus der gleichen Tabelle table1 stammen, nur das schiefste Prädikat vom Merkmal ausgewertet. Wenn die Beispielabfrage jedoch einen Operator wie ein UNION enthält, wertet PSP mehr als ein Prädikat aus. Wenn eine Abfrage zum Beispiel SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate-ähnliche Merkmale aufweist, wählt PSP in diesem Fall höchstens zwei Prädikate aus, da das System dieses Szenario so behandelt, als handele es sich um zwei verschiedene Tabellen. Das gleiche Verhalten ist bei Abfragen zu beobachten, die sich über Tabellenaliase selbst verbinden.

• 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 ein Whitepaper 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 derzeit jede Abfragevariante als neue vorbereitete Anweisung aus. Dies ist einer der Gründe dafür, dass die Abfragevarianten ihre Zuordnung zu übergeordneten object_id-Modulen verlieren, wenn der Dispatcher-Plan auf einem Modul basiert (d. h. gespeicherte Prozedur, Triggerfunktion, Ansicht usw.). Als vorbereitete Anweisung ist object_id nichts, das einem Objekt sys.objects direkt zugeordnet werden kann, sondern im Wesentlichen ein berechneter Wert, basierend auf einem internen Hash des Batchtexts. Weitere Informationen finden Sie im Abschnitt Tabelle zurückgegeben in der sys.dm_exec_plan_attributes DMV-Dokumentation.

  Abfragevariantenpläne werden im Plancache-Objektspeicher (CACHESTORE_OBJCP) platziert, während Verteilerpläne im SQL-Plans-Cachespeicher (CACHESTORE_SQLCP) platziert werden. Die PSP-Funktion speichert jedoch den object_id der übergeordneten Variante einer Abfrage im Attribut ObjectID, das Teil des PLAN PER VALUE-Hinweises ist, den PSP der ShowPlan-XML hinzufügt, wenn die übergeordnete Abfrage Teil eines Moduls und nicht dynamisch oder ad hoc T-SQL ist. 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 in Ansichten ähnlich dem sys.dm_exec_query_stats-DMV zu finden sind, enthalten weiterhin Daten für Abfragevarianten. Allerdings stimmt die Zuordnung zwischen den object_id Abfragevarianten und den Objekten innerhalb der sys.objects Tabelle derzeit nicht überein, ohne dass die ShowPlan-XML für jede der Abfragevarianten, in denen detailliertere Laufzeitstatistiken erforderlich sind, zusätzlich verarbeitet wird. Die Laufzeit- und Wartestatistikinformationen für Abfragevarianten können ohne zusätzliche ShowPlan XML-Parsing-Techniken aus dem Abfragespeicher bezogen werden, wenn der Abfragespeicher aktiviert ist.

• Da PSP-Abfragevarianten als neue vorbereitete Anweisung ausgeführt werden, werden die object_id nicht automatisch in den verschiedenen sys.dm_exec_* DMVs für den Plan-Cache angezeigt, ohne dass die ShowPlan-XML zerschlagen wird und Textmustervergleichstechniken angewendet werden (d. h. zusätzliche XQuery-Verarbeitung). Nur PSP-Optimierungs-Dispatcherpläne geben derzeit die entsprechende übergeordnete Objekt-ID aus. Der object_id wird im Abfragespeicher angezeigt, da der Abfragespeicher ein relationaleres Modell ermöglicht als die Plan-Cache-Hierarchie. Weitere Informationen finden Sie in der Abfragespeicher-Systemkatalogansicht sys.query_store_query_variant (Transact-SQL).

Überlegungen

• Aktivieren Sie zum Aktivieren der parameterabhängigen Planoptimierung den Datenbankkompatibilitätsgrad 160 für die Datenbank, mit der Sie beim Ausführen der Abfrage verbunden sind.

• 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 schaltet den Abfragespeicher für eine bereits vorhandene Datenbank namens MyNewDatabase frei:

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

Hinweis

Ab SQL Server 2022 (16.x) ist der Abfragespeicher jetzt standardmäßig für alle neu erstellten Datenbanken aktiviert.

• 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_OPTIMIZATION verwenden.

• Wenn die Parameterermittlung durch das Ablaufverfolgungsflag 4136, durch die datenbankweit gültige Konfiguration PARAMETER_SNIFFING oder durch den Abfragehinweis USE HINT('DISABLE_PARAMETER_SNIFFING') deaktiviert ist, wird die PSP-Optimierung für die zugeordneten Workloads und Ausführungskontexte deaktiviert. Weitere Informationen finden Sie unter Hinweise (Transact-SQL) – Abfrage sowie unter ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

• 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 einen unabhängigen Eintrag im Plancache hat, ist es möglich, die standardmäßige Höchstzahl der zulässigen Planeinträge zu erreichen. Wenn die Plancache-Entfernungsrate festzuhalten ist oder die Größen der CACHESTORE_OBJCP-Speicher und CACHESTORE_SQLCP-Cachespeicher übermäßig groß sind, sollten Sie die Anwendung des Ablaufverfolgungskennzeichens 174 in Betracht ziehen.

• Die im Speicher des Abfragespeichers gespeicherte Anzahl individueller 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 hat. Dies macht 47 Pläne (40 aus Abfragevarianten, 2 Dispatcher und 5 nicht-PSP-bezogene 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 würde auch von starken Datenverzerrungen in den Datensätzen abhängen, auf die sich diese Beispielabfrage bezieht.

• Bei jeder Abfragevariantenzuordnung zu einem bestimmten Dispatcher gilt:

  • Der Abfrageplanhash (query_plan_hash) ist eindeutig. Diese Spalte ist in sys.dm_exec_query_stats und in anderen dynamischen Verwaltungssichten sowie in Katalogtabellen verfügbar.
  • Der Abfrageplanhash (plan_handle) ist eindeutig. 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.
  • Der Abfragehash (query_hash) umfasst auch andere Varianten, die dem Dispatcher zugeordnet werden. Dies ermöglicht die Bestimmung der aggregierten Ressourcennutzung für Abfragen, bei denen sich nur die Eingabeparameterwerte 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 (sql_handle) ist aufgrund spezieller PSP-Optimierungsbezeichner, die dem Abfragetext während der Kompilierung hinzugefügt werden, eindeutig. 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 sys.query_store_query als Spalte last_compile_batch_sql_handle 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) für Dispatcher- oder Variantenpläne.

Wenn eine Variante erzwungen wird, wird der übergeordnete Dispatcher nicht erzwungen. 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.

Abfragehinweisverhalten Abfragespeicher

• 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 dieser Informationen zu den angewendeten Abfragehinweisen. 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:

Abfragevariantenhinweise oder -plan	Übergeordnetes Element weist einen benutzerbezogenen Hinweis auf	Übergeordnetes Element weist feedbackbezogene Hinweise auf	Übergeordnetes Element hat manuell erzwungenen Plan	Übergeordnetes Element hat APC 1 erzwungenen Plan
Hinweis über den Benutzer	Hinweis auf Abfragevariante	Hinweis auf Abfragevariante	Hinweis auf Abfragevariante	N/V
Hinweis über Feedback	Hinweis auf Abfragevariante	Hinweis auf Abfragevariante	Hinweis auf Abfragevariante	N/V
Plan, der vom Benutzer erzwungen wird	Abfragevariante Erzwungener Plan	Abfragevariante Erzwungener Plan	Abfragevariante Erzwungener Plan	Abfragevariante Erzwungener Plan
Plan, der von APC erzwungen wird	Abfragevariante Erzwungener Plan	Abfragevariante Erzwungener Plan	Abfragevariante Erzwungener Plan	Abfragevariante Erzwungener Plan
Kein Hinweis oder erzwungener Plan	Hinweis des übergeordneten Benutzers	Kein Hinweis	Keine Aktion	Keine Aktion

¹ 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 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.

SQL Server-Überwachungsverhalten

Die PSP-Optimierung stellt Überwachungsdaten für die Dispatcherplan-Anweisung 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:

action_id	object_name	Anweisung	additional_information
AUSC			<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
EX	usp_test	exec usp_test 300
SL	T2	select * from dbo.t2 where ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	Wählen Sie * von dbo.t2 wo ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
EX	usp_test	exec usp_test 60000
SL	T2	select * from dbo.t2 where ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	Wählen Sie * von dbo.t2 wo ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

Bekannte Probleme

Problem	Entdeckt am	Status	Gelöst am
Zugriffsverletzungs-Ausnahme tritt in Abfragespeicher in SQL Server 2022 (16.x) unter bestimmten Bedingungen auf. Es kann zu Ausnahmen bei Zugriffsverletzungen kommen, wenn die PSP-Optimierung Abfragespeicher-Integration aktiviert ist. Weitere Informationen finden Sie in der Aktualisierung von Parameterabhängige Planoptimierung, Warum?	März 2023	Gelöst	August 2023 (CU 7)

Gelöst

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

Hinweis

Ab dem kumulativem Update 7 von SQL Server 2022 (16.x) wurden mehrere Korrekturen für eine Racebedingung behoben, die zu einer Zugriffsverletzung führen kann. Wenn Zugriffsverletzungen im Zusammenhang mit der PSP-Optimierung mit der Abfragespeicher-Integration auftreten, nachdem das kumulative Update 7 auf SQL Server 2022 (16.x) angewendet wurde, ziehen Sie den folgenden Abschnitt zur Problemumgehung in Betracht.

Dieses Problem tritt aufgrund einer Racebedingung auf, die verursacht werden kann, wenn die Laufzeitstatistiken für eine ausgeführte Abfrage aus der Speicherdarstellung der Abfragespeicher (im MEMORYCLERK_QUERYDISKSTORE_HASHMAP-Arbeitsspeicherclerk gefunden) auf der Datenträgerversion von Abfragespeicher beibehalten 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 Hintergrund-Threads im Zusammenhang mit der Abfragespeicher- Abfrageplanbereinigung (d. hSTALE_QUERY_THRESHOLD_DAYS. und/oder MAX_STORAGE_SIZE_MB-Abfragespeicher-Optionen) stehen Abfragen aus dem Abfragespeicher durchführen, 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.

Problemumgehung: Die Abfragevarianten, die sich im Abfragespeicher befinden, können entfernt werden, oder das PSP-Feature kann vorübergehend auf Abfrage- oder Datenbankebene deaktiviert werden, bis weitere Korrekturen verfügbar sind, wenn ihr System weiterhin Zugriffsverletzungen in Abfragespeicher mit aktivierter PSP-Integration nach dem Anwenden des kumulativen Updates 7 für SQL Server 2022 (16.x) zeigt.

• 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_OPTIMIZATION verwenden.

Um alle Abfragevarianten aus dem Abfragespeicher zu entfernen, nicht nur die Abfragevarianten, die in der Katalogansicht sys.query_store_query_variant (Transact-SQL) erscheinen, kann eine Abfrage ähnlich der folgenden verwendet werden. Ersetzen Sie [<database>] diese durch die entsprechende Datenbank, bei der Probleme aufgetreten sind:

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the Query Store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

Wenn Ihr Abfragespeicher groß ist oder wenn Ihr System eine hohe Arbeitslast und/oder eine große Anzahl von nicht parametrisierten Ad-hoc-Abfragen aufweist, die für die Erfassung durch den Abfragespeicher in Frage kommen, kann das Abschalten des Abfragespeichers einige Zeit in Anspruch nehmen. Um den Abfragespeicher in diesen Szenarien zwangsweise zu deaktivieren, verwenden Sie stattdessen den Befehl ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) im vorherigen T-SQL-Beispiel. Informationen zum Suchen nicht parametrisierter Abfragen finden Sie unter Suchen von nicht parametrisierten Abfragen in Abfragespeicher.

Zugehöriger Inhalt

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