Feedback zur Kardinalitätsschätzung (CE)

Artikel
01/21/2024

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

Ab SQL Server 2022 (16.x) ist das Feedback zur Kardinalitätsschätzung (CE) Teil der intelligenten Abfrageverarbeitungsfamilie von Features und adressiert suboptimale Abfrageausführungspläne für wiederholte Abfragen, wenn diese Probleme aus falschen CE-Modellannahmen resultieren. Dieses Szenario hilft bei der Reduzierung von Regressionsrisiken im Zusammenhang mit der standardmäßigen Kardinalitätsschätzung beim Upgrade von älteren Versionen der Datenbank-Engine.

Da die große Vielzahl an Arbeitsauslastungen und Datenverteilungen von Kunden nicht durch einen einzelnen Satz von Modellen und Annahmen der Kardinalitätsschätzung abgedeckt werden kann, bietet das Feedback zur Kardinalitätsschätzung eine anpassbare Lösung basierend auf Merkmalen zur Abfragelaufzeit. Das Feedback zur Kardinalitätsschätzung identifiziert und verwendet eine Modellannahme, die besser zu einer bestimmten Abfrage- und Datenverteilung passt, um die Qualität des Abfrageausführungsplans zu verbessern. Derzeit kann CE Feedback Planoperatoren identifizieren, bei denen die geschätzte Anzahl von Zeilen und die tatsächliche Anzahl von Zeilen sehr unterschiedlich sind. Feedback wird angewendet, wenn erhebliche Modellschätzungsfehler auftreten, und es gibt ein geeignetes alternatives Modell, das sie ausprobieren können.

Weitere Abfragefeedbackfeatures finden Sie unter "Feedback zur Speichererteilung " und "Grad des Parallelismus(DOP)"-Feedbacks.

Grundlegendes zur Karte inalitätsschätzung (CE)-Feedback

Die Kardinalitätsschätzung (Cardinality Estimate, CE) ist, wie der Abfrageoptimierer die Gesamtanzahl der Zeilen schätzen kann, die auf jeder Ebene eines Abfrageplans verarbeitet werden. Die Kardinalitätsschätzung in SQL Server erfolgt in erster Linie mithilfe von Histogrammen, die gleichzeitig mit Indizes oder Statistiken erstellt werden. Der Vorgang kann entweder manuell oder automatisch ausgeführt werden. Manchmal verwendet SQL Server auch Einschränkungsinformationen und logische Umschreibungen von Abfragen, um Karte inalität zu bestimmen.

Unterschiedliche Versionen der Datenbank-Engine verwenden unterschiedliche Modellannahmen der Kardinalitätsschätzung, basierend darauf, wie Daten verteilt und abgefragt werden. Weitere Informationen finden Sie unter Versionen der Kardinalitätsschätzung.

Feedbackimplementierung der Kardinalitätsschätzung (CE)

Feedback zur Kardinalitätsschätzung (CE) lernt, welche CE-Modellannahmen im Laufe der Zeit optimal sind, und wendet dann die historisch richtigste Annahme an:

Das Feedback zur Kardinalitätsschätzung identifiziert modellbezogene Annahmen und wertet aus, ob sie für wiederholte Abfragen genau sind.
Wenn eine Annahme falsch aussieht, wird eine nachfolgende Ausführung derselben Abfrage mit einem Abfrageplan getestet, der die betroffene Modellannahme der Kardinalitätsschätzung anpasst und überprüft, ob diese Anpassung hilfreich ist. Wir identifizieren die Falschheit, indem wir die tatsächlichen und geschätzten Zeilen von Planoperatoren betrachten. Nicht alle Fehler können durch Modellvarianten korrigiert werden, die in CE-Feedback zur Verfügung stehen.
Wenn die Planqualität verbessert wird, wird der alte Abfrageplan durch einen Abfrageplan ersetzt, der den entsprechenden USE HINT-Abfragehinweis verwendet, der das Schätzungsmodell anpasst. Die Implementierung erfolgt den Mechanismus des Abfragespeicherhinweises.

Nur verifiziertes Feedback wird beibehalten. Feedback zur Kardinalitätsschätzung wird für diese Abfrage nicht verwendet, wenn die angepasste Modellannahme zu einer Leistungsregression führt. In diesem Zusammenhang gilt auch eine abgebrochene Abfrage eines Benutzers als Regression.

Feedbackszenarien der Kardinalitätsschätzung (CE)

Feedback zur Kardinalitätsschätzung (CE) befasst sich mit wahrgenommenen Regressionsproblemen, die sich aus falschen CE-Modellannahmen ergeben, wenn sie die Standard-CE (CE120 oder höher) verwenden und selektiv verschiedene Modellannahmen verwenden können. Zu den Szenarien gehören Korrelation, Join Containment und Optimiererzeilenziel.

Feedbackkorrelation zur Kardinalitätsschätzung (CE)

Bei der Schätzung der Selektivität von Prädikaten in einer bestimmten Tabelle oder Sicht oder der Schätzung der Anzahl von Zeilen, die das angegebene Prädikat erfüllen, verwendet der Abfrageoptimierer Korrelationsmodellannahmen. Folgende Annahmen für Prädikate können gelten:

Vollständig unabhängig (Standard für CE70): Die Kardinalität wird durch Multiplikation der Selektivitätswerte aller Prädikate berechnet.
Teilweise korreliert (Standard für CE120 und höher): Die Kardinalität wird anhand einer Variation im exponentiellen Backoff berechnet, und die Selektivitätswerte werden vom am meisten selektiven bis zum am wenigsten selektiven Prädikat sortiert.
Vollständig korreliert: Die Kardinalität wird anhand der Mindestwerte für die Selektivität für alle Prädikate berechnet.

Das folgende Beispiel verwendet eine partielle Korrelation, wenn die Datenbankkompatibilität auf 120 oder höher festgelegt ist:

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

Wenn die Datenbankkompatibilität auf 160 festgelegt ist und die Standardkorrelation verwendet wird, versucht CE-Feedback, die Korrelation nacheinander in die richtige Richtung zu verschieben, basierend darauf, ob die geschätzte Karte inalität im Vergleich zur tatsächlichen Anzahl von Zeilen unterschätzt oder überschätzt wurde. Verwenden Sie die vollständige Korrelation, wenn eine tatsächliche Anzahl von Zeilen größer als die geschätzte Kardinalität ist. Verwenden Sie die vollständige Unabhängigkeit, wenn eine tatsächliche Anzahl von Zeilen kleiner als die geschätzte Kardinalität ist.

Weitere Informationen finden Sie unter Versionen der Kardinalitätsschätzung.

Feedbackeinschluss zur Kardinalitätsschätzung (CE)

Wenn der Abfrageoptimierer die Selektivität von Joinprädikat und anwendbaren Filterprädikaten schätzt, verwendet er Modellannahmen zum Einschluss. Diese Annahmen sind:

Einfacher Einschluss (Standard für CE70): Hierbei wird davon ausgegangen, dass Joinprädikate vollständig korreliert ist. Dabei wird zuerst die Filterselektivität berechnet und dann die Joinselektivität einbezogen.
Basiseindämmung (Standardeinstellung für CE120 und höher) setzt keine Korrelation zwischen Verknüpfungs-Prädikaten und nachgeschalteten Filtern voraus, wobei die Verknüpfungsauswahl zuerst berechnet wird, und dann wird die Filterauswahl in faktoriert.

Das folgende Beispiel verwendet den Basiseinschluss, wenn die Datenbankkompatibilität auf 120 oder höher festgelegt ist:

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Weitere Informationen finden Sie unter Versionen der Kardinalitätsschätzung.

Feedback zur Kardinalitätsschätzung (CE) und zum Ziel der Abfrageoptimiererzeile

Wenn der Abfrageoptimierer die Kardinalität eines Ausführungsplans schätzt, geht er in der Regel davon aus, dass alle qualifizierten Zeilen aus allen Tabellen verarbeitet werden müssen. Einige Abfragemuster führen jedoch dazu, dass der Abfrageoptimierer nach einem Plan sucht, der weniger Zeilen zurückgibt, um die E/A-Last zu reduzieren. Wenn die Abfrage eine Zielanzahl von Zeilen (Zeilenziel) angibt, die zur Laufzeit mit einem TOPoder INEXISTS Schlüsselwort (keyword)s, dem FAST Abfragehinweis oder einer SET ROWCOUNT Anweisung erwartet werden, wird dieses Zeilenziel als Teil des Abfrageoptimierungsprozesses verwendet, z. B. im folgenden Beispiel:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Bei Anwendung des Zeilenzielplans wird die geschätzte Anzahl von Zeilen im Abfrageplan reduziert, da der Abfrageoptimierer davon ausgeht, dass eine kleinere Anzahl von Zeilen verarbeitet werden muss, um das Zeilenziel zu erreichen.

Während das Zeilenziel eine vorteilhafte Optimierungsstrategie für bestimmte Abfragemuster ist, werden, wenn Daten nicht einheitlich verteilt sind, werden möglicherweise mehr Seiten gescannt als geschätzt, was bedeutet, dass das Zeilenziel ineffizient wird. Das Feedback zur Kardinalitätsschätzung kann die Zeilenzielüberprüfung deaktivieren und einen Suchvorgang aktivieren, wenn diese Ineffizienz erkannt wird.

Im Ausführungsplan gibt es kein für CE-Feedback spezifisches Attribut, es wird jedoch ein Attribut für den Abfragespeicher Hinweis aufgeführt. Suchen Sie nach dem QueryStoreStatementHintSource zu sein CE feedback.

Überlegungen zur Karte inalitätsschätzung (CE)-Feedback

Aktivieren Sie die Datenbankkompatibilitätsebene 160 für die Datenbank, mit der Sie bei der Ausführung der Abfrage verbunden sind, um Karte inalitätsschätzung (CE)-Feedback zu aktivieren. Die Abfragespeicher muss für jede Datenbank, in der CE-Feedback verwendet wird, im READ_WRITE Modus aktiviert sein.
Verwenden Sie die CE_FEEDBACKDatenbankbereichskonfiguration, um CE-Feedback auf Datenbankebene zu deaktivieren. Beispiel: In der Benutzerdatenbank:
```
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
```
Um das Feedback zur Kardinalitätsschätzung auf Abfrageebene zu deaktivieren, verwenden Sie den Abfragehinweis DISABLE_CE_FEEDBACK.

Die Feedbackaktivität zur Kardinalitätsschätzung ist über die XEvents query_feedback_analysis und query_feedback_validation sichtbar.

Hinweise, die vom Feedback zur Kardinalitätsschätzung festgelegt werden, können mithilfe der Katalogsicht sys.query_store_query_hints nachverfolgt werden.

Feedbackinformationen können mithilfe der sys.query_store_plan_feedback Katalogansicht nachverfolgt werden.

Wenn für eine Abfrage durch den Abfragespeicher ein Abfrageplan erzwungen wird, wird das Feedback zur Kardinalitätsschätzung für diese Abfrage nicht verwendet.

Wenn eine Abfrage hartcodierte Abfragehinweise enthält oder benutzerseitig festgelegte Abfragespeicherhinweise verwendet, wird das Feedback zur Kardinalitätsschätzung für diese Abfrage nicht verwendet. Weitere Informationen finden Sie unter Hinweise (Transact-SQL) – Abfrage und Abfragespeicherhinweis.

Ab SQL Server 2022 (16.x) ist CE-Feedback für sekundäre Replikate in Verfügbarkeitsgruppen nicht replizierbar, wenn Abfragespeicher für sekundäre Replikate aktiviert ist. CE-Feedback profitiert derzeit nur von primären Replikaten. Beim Failover gehen Feedback, das auf primäre oder sekundäre Replikate angewendet wird, verloren. Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.

Persistenz für Karte inalitätsschätzungsfeedback (CE)

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Feedback zur Kardinalitätsschätzung (CE) kann Szenarien erkennen, wenn die Optimierung des Zeilenziels beibehalten werden soll, und diese Änderung beibehalten, indem sie im Abfragespeicher in Form eines Abfragespeicherhinweiss beibehalten wird. Die neue Optimierung wird für zukünftige Ausführung der Abfrage verwendet. CE-Feedback behält andere Szenarien außerhalb von Abfragemustern zur Optimierung von Zeilenzielen bei, wie in Feedbackszenarien beschrieben. CE-Feedback behandelt derzeit Prädikatauswahlszenarien, die vom Korrelationsmodell des CE verwendet werden, und verbinden Prädikatszenarien, die vom Eindämmungsmodell des CE behandelt werden.

Dieses Feature wurde in SQL Server 2022 (16.x) eingeführt, diese Leistungsverbesserung steht jedoch für Abfragen zur Verfügung, die in der Datenbankkompatibilitätsebene 160 oder höher ausgeführt werden, oder den QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n Hinweis von 160 und höher, und wenn Abfragespeicher für die Datenbank aktiviert ist und sich im Zustand "Lese-Schreibzugriff" befindet.

Bekannte Probleme mit Karte inalitätsschätzung (CE)-Feedback

Abgang	Entdeckt am	Status	Gelöst am
Langsame SQL Server-Leistung, nachdem Sie kumulatives Update 8 für SQL Server 2022 (16.x) unter bestimmten Bedingungen angewendet haben. Möglicherweise kommt es zu einer dramatischen Plancachespeicherauslastung zusammen mit unerwarteten Erhöhungen der CPU-Auslastung, wenn CE-Feedback aktiviert ist.	Dezember 2023	Hat Problemumgehung

Details zu bekannten Problemen

Langsame SQL Server-Leistung nach Anwendung des kumulativen Updates 8 für SQL Server 2022 unter bestimmten Bedingungen

Ab SQL Server 2022 (16.x) kumulativem Update 8 kann SQL Server unerwartete Zunahmen der CPU- und Speicherauslastung aufweisen. Darüber hinaus kann auch eine Zunahme der RESOURCE_SEMAPHORE_QUERY_COMPILE Wartezeiten beobachtet werden. Sie können auch feststellen, dass die Anzahl der Plancacheobjekte stetig zunimmt, die die Grenzwerte für den Plancache nähern und den Plancache manuell mit Techniken wie ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, oder DBCC FREEPROCCACHEDBCC FREESYSTEMCACHEnicht unterstützen. Dieses Verhalten wurde nur von einer kleinen Anzahl von Kunden beobachtet.

Dieses Problem wirkt sich nicht auf alle Arbeitslasten aus und hängt von der Anzahl der verschiedenen Pläne ab, die generiert wurden, sowie von der Anzahl der Pläne, die für die Ce-Feedbackfunktion geeignet waren. Während des Zeitraums, in dem CE-Feedback Planoperatoren analysiert, bei denen erhebliche Modellfehler aufgetreten sind, gibt es ein Szenario, in dem während dieser Analysephase ein Plan, auf den verwiesen wird, im Arbeitsspeicher abgeleitet werden kann, ohne dass der Plan anschließend über den normalen LRU-Algorithmus (Least Recently Used) aus dem Speicher entfernt werden kann. Der LRU-Mechanismus ist eine Möglichkeit, wie SQL Server Planerzwingungsrichtlinien erzwingt. SQL Server entfernt auch Pläne aus dem Arbeitsspeicher, wenn das System unter Arbeitsspeicherdruck liegt. Wenn SQL Server versucht, die Pläne zu entfernen, die nicht ordnungsgemäß abgeleitet wurden, können diese Pläne nicht aus dem Plancache entfernt werden, wodurch der Cache weiterhin vergrößert wird. Der wachsende Cache kann beginnen, zusätzliche Kompilierungen zu verursachen, die letztendlich mehr CPU und Arbeitsspeicher verwenden. Weitere Informationen finden Sie unter Plan Cache Internals.

Symptom: Die Anzahl der verwendeten Plancacheeinträge und werden als modifiziert von SQL-Plänen oder Objektplänen im Laufe der Zeit auf 50.000 oder mehr markiert. Wenn Sie Plancacheeinträge beobachten, die mit dieser Ebene beginnen, zusammen mit unerwarteten Erhöhungen der CPU-Auslastung zu erreichen, tritt dieses Problem möglicherweise auf Ihrem System auf. Ein verwandter Fix wurde in SQL Server 2022 (16.x) kumulatives Update 9 bereitgestellt. Siehe KB5030731. Der Fix hat versucht, ein Problem zu beheben, bei dem Plancacheeinträge entfernt werden, wenn das Feedback zur Kardinalitätsschätzung (Cardinality Estimation, CE) versucht, das zugeordnete Profil abzurufen, was zu einer Speicherbeschädigung führt. Zusätzliche Korrekturen für dieses Problem werden in einem bevorstehenden kumulativen Update verfügbar sein.

Um die Anzahl der vom System verwendeten Plancacheeinträge zu überwachen, können die folgenden Beispiele als Zeitansicht der Anzahl der vorhandenen Plancacheeinträge verwendet werden. Beispielsweise ist das Beobachten der Anzahl der Plancacheeinträge, die als modifiziert gekennzeichnet sind, im Laufe der Zeit eine Möglichkeit, dieses Phänomen zu überwachen.

SELECT
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END AS PlanType,
  COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp 
  ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
AND ecp.bucketid is NULL
GROUP BY
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END;

Ein weiterer Satz von Abfragen, die auch dieselben Informationen wie im vorherigen Beispiel bereitstellen und gleichzeitig zusätzliche Leistungsmetriken beobachten können. Das Cachetrefferverhältnis wird verringert, sowie die Anzahl der Kompilierungen im Verhältnis zur Anzahl der Batchanforderungen/Sek. Die folgenden Abfragen können verwendet werden, um Ihr System im Laufe der Zeit zu überwachen. Achten Sie auf das Cachetrefferverhältnis (unvorgesehene Dips), die verwendeten Cacheobjekte (Erhöhung der Anzahl der Ebenen, die sich auf 50.000 ohne Abnehmen nähern) und ein niedrigeres Verhältnis als erwartet Batchanforderungen/Sek . im Vergleich zu einem Anstieg der Kompilierungen/Sek.

--SQL Plan (Adhoc and Prepared plans)
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (SQL Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (Object Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT
    CASE
        WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
        WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
    END AS [SQLServer:SQL Statistics],
    FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec' 
);

Problemumgehung: Das CE-Feedback-Feature kann auf Datenbankebene deaktiviert werden, bis zusätzliche Korrekturen verfügbar werden, wenn ihr System die zuvor beschriebenen Symptome aufweist. Um den Plancachespeicher zurückzugeben, der von diesem Problem übernommen wurde, ist ein Neustart der SQL Server-Instanz erforderlich. Diese Neustartaktion kann ausgeführt werden, nachdem das CE-Feedback-Feature deaktiviert wurde. Verwenden Sie die CE_FEEDBACKDatenbankbereichskonfiguration, um CE-Feedback auf Datenbankebene zu deaktivieren. Beispiel: In der Benutzerdatenbank:

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

Feedback und Melden von Problemen

Für Feedback oder Fragen, E-Mail CEFfeedback@microsoft.com