Feedback zur Kardinalitätsschätzung (CE)
Gilt für: SQL Server 2022 (16.x) und höher.
Ab SQL Server 2022 (16.x) ist das Feedback zur Kardinalitätsschätzung Teil der Feature-Familie der intelligenten Abfrageverarbeitung und verbessert suboptimale Abfrageausführungspläne für wiederholte Abfragen, wenn diese Probleme aus falschen Modellannahmen der Kardinalitätsschätzung 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. CE-Feedback 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 Abfragefeedback-Features finden Sie unter Feedback zur Speicherzuweisung und Feedback zum Grad des Parallelismus (DOP).
Erläuterungen zum Feedback zur Kardinalitätsschätzung (CE)
Die Kardinalitätsschätzung (Cardinality Estimation, CE) gibt an, wie der Abfrageoptimierer die Gesamtanzahl von Zeilen schätzen kann, die auf jeder Ebene eines Abfrageplans verarbeitet wurden. 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. In manchen Fällen verwendet SQL Server auch Einschränkungsinformationen und logische Umschreibungen von Abfragen, um die Kardinalitä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.
Feedback-Implementierung der Kardinalitätsschätzung (CE)
Das Feedback zur Kardinalitätsschätzung (CE) lernt im Lauf der Zeit, welche Modellannahmen der Kardinalitätsschätzung optimal sind, und wendet dann die Annahme an, die sich im Verlauf als am zutreffendsten erwiesen hat:
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 zur Kardinalitätsschätzung (CE)
Das Feedback zur Kardinalitätsschätzung (CE) befasst sich mit wahrgenommenen Regressionsproblemen, die sich aus falschen Modellannahmen der Kardinalitätsschätzung ergeben, wenn der Standardwert für die Kardinalitätsschätzung (CE120 oder höher) verwendet wird. Das Feedback kann selektiv unterschiedliche Modellannahmen verwenden. Die Szenarien umfassen Korrelation, Joineigenständigkeit und Zeilenziel.
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 das Feedback zur Kardinalitätsschätzung, die Korrelation schrittweise in die richtige Richtung zu verschieben, basierend darauf, ob die Kardinalität im Vergleich zur tatsächlichen Anzahl von Zeilen unter- 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.
Joineigenständigkeit des Feedbacks 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.
Basis-Einschluss (Standard für CE120 und höher): Hierbei wird von keiner Korrelation zwischen Joinprädikaten und Filtern fürs Herunterstreamen ausgegangen wird. Dabei wird zuerst die Join-Selektivität berechnet und dann die Filterselektivität einbezogen.
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 Zeilenziel des Abfrageoptimierers
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 mit dem Schlüsselwort TOP, IN oder EXISTS, dem Abfragehinweis FAST oder einer SET ROWCOUNT-Anweisung eine Zielanzahl von Zeilen (Zeilenziel) angibt, die zur Laufzeit erwartet werden, wird dieses Zeilenziel als Teil des Abfrageoptimierungsprozesses verwendet, wie im folgenden Beispiel gezeigt:
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.
Das Zeilenziel ist zwar eine vorteilhafte Optimierungsstrategie für bestimmte Abfragemuster, wenn Daten nicht einheitlich verteilt sind, allerdings werden möglicherweise mehr Seiten überprüft 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, aber es gibt ein Attribut, das für den Abfragespeicher Hinweis aufgeführt ist. Achten Sie darauf, dass QueryStoreStatementHintSourceCE feedback entspricht.
Überlegungen zum Feedback zur Kardinalitätsschätzung (CE)
Legen Sie zum Aktivieren des Feedbacks zur Kardinalitätsschätzung (CE) den Datenbank-Kompatibilitätsgrad 160 für die Datenbank fest, mit der Sie beim Ausführen der Abfrage verbunden sind. Der Abfragespeicher muss für jede Datenbank aktiviert und im Modus READ_WRITE sein, in der das Feedback zur Kardinalitätsschätzung verwendet wird.
Um das CE-Feedback auf Datenbankebene zu deaktivieren, verwenden Sie die Konfiguration für den
CE_FEEDBACK-Datenbank. Beispielsweise 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 Katalogsicht sys.query_store_plan_feedback nachverfolgt werden.
Wenn eine Abfrage einen Abfrageplan hat, der durch Abfragespeicher erzwungen wird, wird DAS CE-Feedback nicht für diese Abfrage verwendet.
Wenn eine Abfrage hartcodierte Abfragehinweise verwendet oder Abfragespeicher vom Benutzer festgelegten Hinweise verwendet, wird DAS CE-Feedback für diese Abfrage nicht verwendet. Weitere Informationen finden Sie unter Abfragehinweise und Abfragespeicher Hinweis.
Ab SQL Server 2022 (16.x), wenn Abfragespeicher für sekundäre Replikate aktiviert ist, ist CE-Feedback für sekundäre Replikate in Verfügbarkeitsgruppen nicht replizierbar. CE-Feedback profitiert derzeit nur von primären Replikaten. Beim Failover geht Feedback, das auf primäre oder sekundäre Replikate angewendet wird, verloren. Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.
Persistenzfür Feedback zur Kardinalitätsschätzung (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 es 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 CE-Korrelationsmodell verwendet werden, und verbinden Prädikatszenarien, die vom CE-Eindämmungsmodell behandelt werden.
Dieses Feature wurde in SQL Server 2022 (16.x) eingeführt, jedoch steht diese Leistungsverbesserung für Abfragen zur Verfügung, die auf dem Datenbank-Kompatibilitätsgrad 160 oder höher, oder den QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n-Hinweis von 160 und höher ausgeführt werden, und wenn Abfragespeicher für die Datenbank aktiviert ist und sich in einem Status „Lese-/Schreibzugriff“ befindet.
Bekannte Probleme mit Feedback zur Kardinalitätsschätzung (CE)
| Abgang | Entdeckt am | Status | Gelöst am |
|---|---|---|---|
| Langsame SQL Server-Leistung, nachdem Sie das kumulatives Update 8 für SQL Server 2022 (16.x) unter bestimmten Bedingungen angewendet haben. Möglicherweise kommt es zu einer dramatischen Plancache-Speicherauslastung zusammen mit unerwarteten Erhöhungen der CPU-Auslastung, wenn CE-Feedback aktiviert ist. | Dezember 2023 | (Aktualisiert vom Mai 2024) Verfügt über Problemumgehung |
Details zu bekannten Problemen
Langsame SQL Server-Leistung nach Anwendung des kumulativen Updates 8 für SQL Server 2022 unter bestimmten Bedingungen
Ab dem kumulativen Update 8 von SQL Server 2022 (16.x) 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 einigen 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 das CE-Feedbackfeature berechtigt waren, sich zu engagieren. Während CE-Feedback Planoperatoren für erhebliche Modellfehler analysiert, gibt es ein Szenario, in dem ein referenzierter Plan während dieser Analysephase abgeleitet werden kann. Dadurch wird verhindert, dass der Plan aus dem Arbeitsspeicher entfernt wird, indem der übliche LRU-Algorithmus (Least Recently Used) verwendet wird. Der LRU-Mechanismus ist eine Möglichkeit, wie SQL Server Planzurücksetzungsrichtlinien 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 unter Plancache.
Symptom: Die Anzahl der verwendeten Plancache-Einträge 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 der Annäherung an diese Ebene beginnen, zusammen mit unerwarteten Erhöhungen der CPU-Auslastung, tritt dieses Problem möglicherweise auf Ihrem System auf. Ein Fix wird mit SQL Server 2022 (16.x) kumulativem Update 12 bereitgestellt. Siehe KB5033663.
Um die Anzahl der vom System verwendeten Plancache-Einträge zu überwachen, können die folgenden Beispiele als Zeitansicht der Anzahl der vorhandenen Plancache-Einträge verwendet werden. Beispielsweise ist das Beobachten der Anzahl der Plancache-Einträ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 die gleichen 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 50.000 ohne Abnehmen nähern) und ein niedrigeres Verhältnis als erwartet des Verhältnisse 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
Wenn Ihr System weiterhin die zuvor beschriebenen Symptome erlebt, kann das CE-Feedbackfeature nach der Anwendung des kumulativen Updates 12 KB5033663 auf Datenbankebene deaktiviert werden.
Um den von diesem Problem beanspruchten Plancachespeicher zurückzugeben, ist ein Neustart der SQL Server-Instanz erforderlich. Diese Neustartaktion kann ausgeführt werden, nachdem die CE-Feedback-Funktion deaktiviert wurde. Um das CE-Feedback auf Datenbankebene zu deaktivieren, verwenden Sie die Konfiguration für den CE_FEEDBACK-Datenbank. Beispielsweise in der Benutzerdatenbank:
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
Feedback und Melden von Problemen
Wenn Sie Feedback oder Fragen haben, senden Sie eine E-Mail an CEFfeedback@microsoft.com
Zugehöriger Inhalt
- Feedback zur Kardinalitätsschätzung in SQL Server 2022
- Intelligente Abfrageverarbeitung in SQL-Datenbanken
- Detallierte Features der intelligenten Abfrageverarbeitung
- Kardinalitätsschätzung (SQL Server)
- RECONFIGURE (Transact-SQL)
- Überwachen und Optimieren der Leistung
- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter: https://aka.ms/ContentUserFeedback.
Einreichen und Feedback anzeigen für