Feedback zur Kardinalitätsschätzung (CE)

Gilt für: Gilt für: SQL Server 2022 (16.x) und höhere Versionen, Azure SQL-Datenbank, Azure SQL verwaltete Instanz.

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. Das Feedback zur Kardinalitätsschätzung identifiziert und nutzt eine Modellannahme, die besser zu einer bestimmten Abfrage- und Datenverteilung passt, um die Qualität des Abfrageausführungsplans zu verbessern. Derzeit kann das Feedback zur Kardinalitätsschätzung 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 ein geeignetes alternatives Modell zum Ausprobieren vorhanden ist.

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:

1. Das Feedback zur Kardinalitätsschätzung identifiziert modellbezogene Annahmen und wertet aus, ob sie für wiederholte Abfragen genau sind.

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

3. 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 Feedback zur Kardinalitätsschätzung spezifisches Attribut, es wird jedoch ein Attribut für den Abfragespeicherhinweis aufgeführt. Achten Sie darauf, dass QueryStoreStatementHintSource CE 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 CE_FEEDBACK für die Datenbank-Konfiguration. 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 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 Abfragehinweise und Abfragespeicherhinweis.

Ab SQL Server 2022 (16.x) ist Feedback zur Kardinalitätsschätzung auch für sekundäre Replikate in Verfügbarkeitsgruppen nicht replizierbar, wenn der Abfragespeicher für sekundäre Replikate aktiviert ist. 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: Gilt für: SQL Server 2022 (16.x) und höhere Versionen, Azure SQL-Datenbank, Azure SQL verwaltete Instanz.

Feedback zur Kardinalitätsschätzung (CE) kann Szenarien erkennen, wenn die Optimierung des Zeilenziels beibehalten werden soll, und diese Änderung beibehalten, indem sie in der Abfragespeicher in Form eines Abfragespeicher Hinweises beibehalten wird. Die neue Optimierung wird für die künftige Ausführung der Abfrage verwendet. Feedback zur Kardinalitätsschätzung 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)

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. Außerdem werden Sie möglicherweise feststellen, dass die Anzahl der verwendeten Plancache-Objekte stetig zunimmt und sich den Grenzen des Plancache nähert, und dass das manuelle Löschen des Plancache mit Techniken wie ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE oder DBCC FREEPROCCACHE keine Hilfestellung bietet. Dieses Verhalten wurde nur von wenigen Kund*innen beobachtet.

Dieses Problem wirkt sich nicht auf alle Workloads aus und hängt von der Anzahl der unterschiedlichen Pläne ab, die generiert wurden, sowie von der Anzahl der Pläne, die für das Feature des Feedbacks zur Kardinalitätsschätzung geeignet waren. Wenngleich das Feedback zur Kardinalitätsschätzung die Planoperatoren auf größere Modellfehleinschätzungen hin analysiert, gibt es ein Szenario, in dem ein referenzierter Plan in dieser Analysephase dereferenziert werden kann. Dadurch wird verhindert, dass der Plan mithilfe des üblichen LRU-Algorithmus (Least Recently Used) aus dem Arbeitsspeicher entfernt wird. Der LRU-Mechanismus ist eine Möglichkeit, wie SQL Server Planzurücksetzungsrichtlinien erzwingt. SQL Server entfernt Pläne auch dann aus dem Arbeitsspeicher, wenn das System unter Speicherauslastung arbeitet. Wenn SQL Server versucht, die Pläne zu entfernen, die nicht ordnungsgemäß dereferenziert wurden, können diese Pläne nicht aus dem Plancache entfernt werden, wodurch der Cache weiter anwächst. Der wachsende Cache beginnt möglicherweise, zusätzliche Kompilierungen zu verursachen, die letztendlich mehr CPU und Arbeitsspeicher in Anspruch nehmen. 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 Plancache-Einträge beobachten, die sich diesem Umfang annähern, zusammen mit einem unerwarteten Anstieg der CPU-Auslastung, tritt bei Ihrem System unter Umständen dieses Problem auf. Eine Fehlerbehebung wurde mit dem kumulativem Update 12 SQL Server 2022 (16.x) 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 dieselben Informationen wie im vorherigen Beispiel bereitstellen, aber zugleich die Beobachtung zusätzlicher Leistungsmetriken ermöglichen. Die Plancache-Trefferraten nehmen ab, ebenso wie die Anzahl der Kompilierungen im Verhältnis zur Anzahl der Batch-Anforderungen pro Sekunde. Mithilfe der folgenden Abfragen können Sie Ihr System im Zeitverlauf ü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 das System weiterhin die zuvor beschriebenen Symptome aufweist, kann das Feature des Feedbacks zur Kardinalitätsschätzung nach der Anwendung des kumulativen Updates 12 KB5033663 auf Datenbankebene deaktiviert werden.

Um den Plancache-Speicher freizugeben, der von diesem Problem in Anspruch genommen wurde, 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 CE_FEEDBACK für die Datenbank-Konfiguration. 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)