Parameterabhängige Planoptimierung

Gilt für: SQL Server 2022 (16.x) und höhere Versionen

Parameter Sensitive Plan (PSP)-Optimierung ist Teil der intelligenten Abfrageverarbeitungsfamilie von Features. Es behebt das Szenario, in dem ein einzelner zwischengespeicherter Plan für eine parametrisierte Abfrage für alle möglichen eingehenden Parameterwerte nicht 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 von Problemen mit Parametern
• 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 berücksichtigen unterschiedliche Datengrößen basierend auf den vom Kunden bereitgestellten Laufzeitparameterwerten.

Grundlegendes zur Parametrisierung

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

Sie können auch das standardmäßige einfache Parametrisierungsverhalten von SQL Server außer Kraft setzen, indem Sie angeben, dass alle SELECT, INSERT, , UPDATEund DELETE Anweisungen in einer Datenbank parametrisiert werden, vorbehaltlich bestimmter Einschränkungen. Weitere Informationen finden Sie unter Erzwungene Parametrisierung.

Implementierung der PSP-Optimierung

Während der ersten Kompilierung identifizieren Spaltenstatistiken Histogramme nicht einheitliche Verteilungen und bewerten die risikoreichsten parametrisierten Prädikate, bis zu drei von allen verfügbaren Prädikaten. 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 Dispatcherplan , der die PSP-Optimierungslogik enthält, die als Dispatcherausdruck bezeichnet wird. Ein Dispatcher-Plan wird Abfragevarianten auf den Prädikaten für die Grenzwerte des Kardinalitätsbereichs.

Terminologie

Dispatcher-Ausdruck

Wertet Karte Inalitä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 vom Feature ausgewählt wurden, mit einigen zusätzlichen Details. Für jedes Prädikat, das einige der Details ausgewählt wird, die im Dispatcherplan enthalten sind, sind die hohen und niedrigen Grenzwerte. Diese Werte werden verwendet, um Parameterwerte in verschiedene Buckets oder Bereiche aufzuteilen. Der Dispatcherplan enthält auch die Statistiken, die zur Berechnung der Grenzwerte verwendet wurden.

Abfragevariante

Als Verteilerplan wertet die Karte inalität von Prädikaten basierend auf Laufzeitparameterwerten aus und generiert separate untergeordnete Abfragen zum Kompilieren und Ausführen. Diese untergeordneten Abfragen werden als Abfragevarianten bezeichnet. Abfragevarianten verfügen über eigene Pläne im Plancache und Abfragespeicher.

Prädikat Karte inalitätsbereich

Zur Laufzeit wird die Karte inalität jedes Prädikats basierend auf Laufzeitparameterwerten ausgewertet. Der Dispatcher buckett die Karte inality-Werte in drei Prädikat Karte inality-Bereiche zur Kompilierungszeit. 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 Dispatcherausdruck weist die Logik auf, die Abfragen basierend auf den Laufzeitwerten von Parametern in Abfragevarianten bucketisiert. Wenn die eigentliche Ausführung beginnt, führt der Dispatcher zwei Schritte aus:

• der Dispatcher wertet seinen Dispatcherausdruck für den angegebenen Satz von Parametern aus, um den bereich der Karte inalität zu berechnen.

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

Die Grenzen des Karte inalitä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. Der 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 dynamischer oder ad-hoc SQL (d. h., sp_executesql) ist das ObjectID-Element 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.
• Der Prädikatbereich ist die prädizierte Karte inalitä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 unabhängig nach Bedarf neu kompiliert, wie bei jedem anderen Abfrageplantyp, vorbehaltlich standardmäßiger Neukompilierungsereignisse. 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 die meisten Datenverknöpfungen basierend auf dem zugrunde liegenden Statistik-Histogramm aufweist. Wenn sie beispielsweise aus SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2column1 = @predicate1 der gleichen Tabelle table1stammen, column2 = @predicate2 wird nur das verzerrte Prädikat vom Feature ausgewertet. Wenn die Beispielabfrage jedoch einen Operator wie ein UNIONPSP umfasst, wertet PSP mehrere Prädikate aus. Wenn eine Abfrage z. B. Merkmale aufweist SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, wählt PSP in diesem Fall höchstens zwei Prädikate aus, da das System dieses Szenario so behandelt, als wäre es zwei verschiedene Tabellen. Dasselbe Verhalten kann von Abfragen beobachtet werden, die sich über Tabellenaliasen 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 Skewnessschwellenwerte beeinflussen, die vom PSP-Optimierungsfeature verwendet werden, mit einer oder mehreren der folgenden Methoden:

  • Ablaufverfolgungskennzeichnungen (Cardinality Estimator, CE) wie Ablaufverfolgungskennzeichnung 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 zur Optimierung Ihrer Abfragepläne mit dem SQL Server 2014 Cardinality Estimator-Whitepaper. Das neuere CE-Modell versucht, einige Korrelation und weniger Unabhängigkeit für die Konjunktion und Disjunktion von Prädikaten zu übernehmen. Die Verwendung des älteren CE-Modells kann sich darauf auswirken, wie die Selektorivität der Prädikate in einem mehrspaltigen Verknüpfungsszenario berechnet werden kann. Diese Aktion sollte nur für bestimmte Szenarien berücksichtigt werden, und es wird nicht empfohlen, das legacy CE-Modell für die meisten Workloads zu verwenden.

• PSP-Optimierung kompiliert derzeit jede Abfragevariante als neue vorbereitete Anweisung, was einer der Gründe dafür ist, dass die Abfragevarianten ihre Zuordnung zu allen übergeordneten Modulen verlieren , object_id wenn der Dispatcherplan auf einem Modul basiert (d. h. gespeicherte Prozedur, Trigger, Funktion, Ansicht usw.). Als vorbereitete Anweisung ist dies 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 ist. Weitere Informationen finden Sie im Abschnitt "Tabelle zurückgegeben" der sys.dm_exec_plan_attributes DMV-Dokumentation.

  Abfragevariantenpläne werden im Plancacheobjektspeicher (CACHESTORE_OBJCP) platziert, während Verteilerpläne im SQL-Plans-Cachespeicher (CACHESTORE_SQLCP) platziert werden. Das PSP-Feature speichert jedoch das object_id übergeordnete Element einer Abfragevariante im ObjectID-Attribut, das Teil des PLAN PER VALUE-Hinweises ist, das PSP dem ShowPlan-XML hinzufügt, wenn die übergeordnete Abfrage Teil eines Moduls ist 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. Genauere Ausführungsstatistiken wie die in Ansichten ähnlichen sys.dm_exec_query_stats Ansichten enthalten weiterhin Daten für Abfragevarianten, aber die Zuordnung zwischen den object_id Abfragevarianten und Objekten innerhalb der sys.objects Tabelle wird derzeit nicht ausgerichtet, ohne zusätzliche Verarbeitung der ShowPlan-XML für jede der Abfragevarianten, in denen detailliertere Laufzeitstatistiken erforderlich sind. Die Laufzeit- und Wartezeitstatistikinformationen für Abfragevarianten können aus der Abfragespeicher ohne zusätzliche ShowPlan-XML-Analysetechniken abgerufen werden, wenn die Abfragespeicher aktiviert ist.

• Da PSP-Abfragevarianten als neue vorbereitete Anweisung ausgeführt werden, wird dies object_id nicht automatisch im verschiedenen plancachebezogenen sys.dm_exec_* DMVs verfügbar gemacht, ohne die ShowPlan-XML zu löschen und Textmustervergleichstechniken anzuwenden (d. h. zusätzliche XQuery-Verarbeitung). Derzeit geben nur PSP-Optimierungs-Dispatcherpläne die entsprechende übergeordnete Objekt-ID aus. Dies object_id wird innerhalb der Abfragespeicher verfügbar gemacht, da Abfragespeicher ein relationaleres Modell ermöglicht, als die Plancachehierarchie bereitstellt. 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 weitere Einblicke in das PSP-Optimierungsfeature zu erhalten, empfehlen wir, dass Abfragespeicher Integration aktiviert ist, indem sie die Abfragespeicher aktivieren. Im folgenden Beispiel wird die Abfragespeicher für eine bereits vorhandene Datenbank aktiviertMyNewDatabase:

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

Hinweis

Ab SQL Server 2022 (16.x) ist Abfragespeicher jetzt für alle neu erstellten Datenbanken standardmäßig 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 Parametersuche durch Ablaufverfolgungskennzeichnung 4136, PARAMETER_SNIFFING Datenbankbereichskonfiguration oder der Abfragehinweis deaktiviert ist, wird die USE HINT('DISABLE_PARAMETER_SNIFFING') PSP-Optimierung für die zugehörigen 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 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 Plancache-Entfernungsrate festzuhalten ist oder die Größen der CACHESTORE_OBJCP Speicher und CACHESTORE_SQLCPCachespeicher ü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 mehrere Pläne haben können, können pro Abfrage insgesamt 200 Pläne innerhalb der Abfragespeicher vorhanden sein. Diese Zahl enthält alle Abfragevariantenpläne für alle Verteiler, 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 eindeutiger Pläne den Standardgrenzwert Abfragespeicher max_plans_per_query überschreiten kann, wäre ein Szenario, in dem Sie das folgende Verhalten haben. Angenommen, Sie haben eine Abfrage mit einer Abfrage-ID von 10, die zwei Dispatcherpläne enthält, und jeder Dispatcherplan verfügt über 20 Abfragevarianten (insgesamt 40 Abfragevarianten). Die Gesamtanzahl der Pläne für die Abfrage-ID 10 beträgt 40 Pläne für die Abfragevarianten und die beiden Verteilerpläne. Es ist auch möglich, dass die übergeordnete Abfrage selbst (Abfrage-ID 10) über 5 reguläre Pläne (nicht verteilerisch) verfügen kann. Dies macht 47 Pläne (40 aus Abfragevarianten, 2 Dispatcher und 5 nicht-PSP-bezogenen Plänen). Wenn jede Abfragevariante auch einen Mittelwert von fünf Plänen aufweist, ist es in diesem Szenario möglich, mehr als 200 Pläne in der Abfragespeicher für eine übergeordnete Abfrage zu haben. Dies hängt auch von einer starken Datenverknrüppung in den Datasets ab, auf die in diesem Beispiel eine übergeordnete Abfrage verweisen könnte.

• Bei jeder Abfragevariantenzuordnung zu einem bestimmten Dispatcher gilt:

  • Der Abfrageplanhash (query_plan_hash) ist eindeutig. Diese Spalte ist in sys.dm_exec_query_statsden dynamischen Verwaltungsansichten und 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 Verteiler nicht erzwungen. Wenn ein Dispatcher erzwungen wird, gelten nur Varianten aus diesem Dispatcher als geeignet für die Verwendung:

• Zuvor erzwungene Varianten anderer Verteiler werden inaktiv, behalten jedoch den erzwungenen Status bei, bis ihr Verteiler erneut erzwungen wird.
• Zuvor erzwungene Varianten im gleichen Dispatcher, die inaktiv geworden sind, werden erneut erzwungen.

Abfragespeicher Abfragehinweisverhalten

• Wenn ein Abfragespeicher Hinweis zu einer Abfragevariante (untergeordnete Abfrage) hinzugefügt wird, wird der Hinweis auf die gleiche Weise wie eine Nicht-PSP-Abfrage angewendet. Abfragevariantenhinweise haben eine höhere Priorität, wenn auch ein Hinweis auf die übergeordnete Abfrage in 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 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 Planzwingverhalten kann in der folgenden Tabelle zusammengefasst werden:

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 erzwungener Plan
Hinweis über den Benutzer	Abfragevariantenhinweis	Abfragevariantenhinweis	Abfragevariantenhinweis	N/V
Hinweis über Feedback	Abfragevariantenhinweis	Abfragevariantenhinweis	Abfragevariantenhinweis	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 den Grund zu überwachen, warum die PSP-Optimierung übersprungen wird.

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

  SELECT name, 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 das PSP-Optimierungsfeature verwendet. Nur Debugkanal. Einige interessante Bereiche sind u. U.:

  • is_query_variant: beschreibt, ob es sich um einen Dispatcherplan (übergeordnetes Element) oder einen Abfragevariantenplan (untergeordnetes Element) handelt.
  • predicate_count: Anzahl der von PSP ausgewählten Prädikate
  • query_variant_id: Zeigt die Abfragevarianten-ID 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. Wenn diese MyNewDatabase Datenbank z. B. eine Tabelle aufgerufen T2 und eine gespeicherte Prozedur mit dem Namen " usp_test, nach der Ausführung der gespeicherten usp_test Prozedur, enthält das Überwachungsprotokoll möglicherweise die folgenden Einträge:

action_id	object_name	statement	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	select * from dbo.t2 where 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	select * from dbo.t2 where 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
Ausnahme von Zugriffsverletzungen tritt in Abfragespeicher in SQL Server 2022 (16.x) unter bestimmten Bedingungen auf. Wenn die PSP-Optimierung Abfragespeicher Integration aktiviert ist, treten möglicherweise Zugriffsverletzungsverletzungen auf. Weitere Informationen finden Sie im Update in parametersensitiver Planoptimierung, Warum?.	März 2023	Gelöst	August 2023 (CU 7)

Gelöst

Ausnahme von Zugriffsverletzungen tritt unter bestimmten Bedingungen in Abfragespeicher in SQL Server 2022 auf

Hinweis

Ab SQL Server 2022 (16.x) kumulativem Update 7 wurden mehrere Korrekturen für eine Racebedingung behoben, die zu einer Zugriffsverletzung führen kann. Wenn Zugriffsverletzungen im Zusammenhang mit der PSP-Optimierung mit Abfragespeicher Integration auftreten, nachdem das kumulative Update 7 für 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 Speicherkaufmann gefunden) auf der Datenträgerversion der Abfragespeicher beibehalten werden. Die Laufzeitstatistiken, die als Laufzeitstatistiken angezeigt werden, werden für einen bestimmten Zeitraum im Arbeitsspeicher aufbewahrt, der durch die DATA_FLUSH_INTERVAL_SECONDS Option der SET QUERY_STORE Anweisung definiert wird (der Standardwert beträgt 15 Minuten). Sie können das Dialogfeld "Management Studio Abfragespeicher" verwenden, um einen Wert für das Datenspülintervall (Minuten) einzugeben, der intern in Sekunden konvertiert wird. Wenn das System unter Arbeitsspeicherdruck liegt, können Laufzeitstatistiken früher als mit der Option definiert auf den DATA_FLUSH_INTERVAL_SECONDS Datenträger geleert werden. Wenn zusätzliche Abfragespeicher Hintergrundthreads im Zusammenhang mit Abfragespeicher Abfrageplan sauber up (d. sSTALE_QUERY_THRESHOLD_DAYS. und/oder MAX_STORAGE_SIZE_MB Abfragespeicher Optionen) stehen, abfragen sie aus dem Abfragespeicher, gibt es ein Szenario, in dem eine Abfragevariante und/oder die zugehörige Dispatcher-Anweisung vorzeitig abgeleitet werden kann. Dies kann zu einer Zugriffsverletzung während des Einfügens oder Löschens von Abfragevarianten in die Abfragespeicher führen.

Weitere Informationen zu Abfragespeicher Vorgängen finden Sie im Abschnitt "Hinweise" im Artikel "How Abfragespeicher Collects Data".

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

• 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 sys.query_store_query_variant -Katalogansicht (Transact-SQL) angezeigt werden, kann eine Abfrage verwendet werden, die der folgenden ähnelt. 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 Ihre Abfragespeicher groß ist oder ihr System über eine erhebliche Arbeitsauslastung und/oder eine hohe Anzahl nicht parametrisierter Ad-hoc-Abfragen verfügt, die für die Erfassung durch Abfragespeicher geeignet sind, kann das Deaktivieren der Abfragespeicher einige Zeit in Anspruch nehmen. Um die Abfragespeicher in diesen Szenarien forcibly zu deaktivieren, verwenden Sie stattdessen den ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) Befehl im vorherigen Beispiel T-SQL. 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)