Informacja zwrotna na temat szacowania kardynalności (CE)

Dotyczy: SQL Server 2022 (16.x) i późniejszych wersji, Azure SQL Database, Azure SQL Managed Instance.

Począwszy od programu SQL Server 2022 (16.x), opinia dotycząca szacowania kardynalności (CE) jest częścią inteligentnej rodziny funkcji przetwarzania zapytań i rozwiązuje nieoptymalne plany wykonywania zapytań na potrzeby powtarzania zapytań, gdy te problemy wynikają z nieprawidłowych założeń modelu CE. Ten scenariusz pomaga zmniejszyć ryzyko regresji związane z domyślnym CE przy aktualizacji ze starszych wersji silnika bazy danych.

Ponieważ żaden pojedynczy zestaw modeli CE i założeń nie może pomieścić szerokiej gamy obciążeń klientów i dystrybucji danych, opinie CE zapewniają elastyczne rozwiązanie oparte na cechach środowiska uruchomieniowego zapytań. Opinie CE identyfikują i wykorzystują założenie modelu, które lepiej pasuje do danej dystrybucji zapytań i danych w celu poprawy jakości planu wykonywania zapytań. Obecnie informacje zwrotne CE mogą identyfikować operatory planu, dla których szacowana liczba wierszy i rzeczywista liczba wierszy są bardzo różne. Informacja zwrotna jest stosowana, gdy wystąpią znaczące błędy szacowania modelu i istnieje praktyczny alternatywny model do wypróbowania.

Aby uzyskać informacje o innych funkcjach opinii dotyczących zapytań, zobacz Informacja zwrotna dotycząca przydziału pamięci i Stopień równoległości (DOP) informacji zwrotnej.

Informacje zwrotne dotyczące szacowania kardynalności (CE)

Szacowanie kardynalności (CE) to sposób, w jaki optymalizator zapytań może oszacować łączną liczbę wierszy przetworzonych na każdym poziomie planu zapytania. Szacowanie kardynalności w programie SQL Server pochodzi głównie z histogramów tworzonych podczas tworzenia indeksów lub statystyk ręcznie lub automatycznie. Czasami program SQL Server używa również logicznych przekształceń zapytań i informacji o ograniczeniach w celu określenia kardynalności.

Różne wersje aparatu bazy danych używają różnych założeń modelu CE na podstawie sposobu dystrybucji i wykonywania zapytań dotyczących danych. Aby uzyskać więcej informacji, zobacz wersje CE.

Implementacja sprzężenia zwrotnego dla szacowania kardynalności (CE)

Opinie dotyczące szacowania kardynalności (CE) zbierają dane na temat tego, które założenia modelu CE są optymalne w dłuższym okresie, a następnie wykorzystują najbardziej precyzyjne historyczne założenia.

1. Opinie CE identyfikują założenia związane z modelem i oceniają, czy są one dokładne w przypadku powtarzających się zapytań.

2. Jeśli założenie wygląda na niepoprawne, przy kolejnym wykonaniu tego samego zapytania stosowany jest plan zapytania, który dostosowuje wpływowe założenie modelu CE i sprawdza, czy pomaga. Identyfikujemy niepoprawność, patrząc na rzeczywiste a szacowane wiersze z operatorów planu. Nie wszystkie błędy mogą być poprawiane przez warianty modelu dostępne w opiniach CE.

3. Jeśli poprawi jakość planu, stary plan zapytania zostanie zastąpiony nowym planem zapytania używającym odpowiedniej wskazówki USE HINT dotyczącej zapytania, która dostosowuje model szacowania, wdrożony przy użyciu mechanizmu wskazówek Query Store.

Zapisywane są tylko zweryfikowane opinie. Opinie CE nie są używane dla tego zapytania, jeśli dostosowane założenie modelu powoduje regresję wydajności. W tym kontekście zapytanie anulowane przez użytkownika jest również postrzegane jako regresja.

Scenariusze opinii zwrotnych dotyczące szacowania kardynalności (CE)

Opinie zwrotne dotyczące szacowania kardynalności (CE) odnoszą się do postrzeganych problemów regresji wynikających z nieprawidłowych założeń modelu CE, kiedy wykorzystuje się domyślny model CE (CE120 lub wyższy), i mogą wybiórczo stosować inne założenia modelu. Scenariusze obejmują korelację, zawieranie sprzężeń i cel wiersza optymalizatora.

Korelacja danych zwrotnych szacowania kardynalności (CE)

Gdy Optymalizator zapytań szacuje selektywność predykatów w danej tabeli lub widoku, lub liczbę wierszy spełniających określony predykat, używa założeń modelu korelacji. Te założenia mogą być takie, że predykaty to:

• W pełni niezależne (wartość domyślna dla CE70), gdzie kardynalność jest obliczana poprzez pomnożenie selektywności wszystkich predykatów.

• Częściowo skorelowane (domyślnie dla CE120 i wyższych), gdzie kardynalność jest obliczana przy użyciu odmiany wycofywania wykładniczego, porządkując selektywności od najbardziej do najmniej selektywnego predykatu.

• Całkowicie skorelowane, gdzie kardynalność jest obliczana przy użyciu minimalnych selektywności dla wszystkich predykatów.

W poniższym przykładzie użyto częściowej korelacji, gdy zgodność bazy danych jest ustawiona na 120 lub nowszą:

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

Gdy zgodność bazy danych jest ustawiona na 160, a domyślna korekcja jest stosowana, informacja zwrotna CE stara się przesunąć korekcję w właściwy kierunek krok po kroku na podstawie tego, czy szacowana kardynalność była niedoszacowana, czy przeszacowana w porównaniu z rzeczywistą liczbą wierszy. Użyj pełnej korelacji między danymi, jeśli rzeczywista liczba wierszy jest większa niż szacowana kardynalność danych. Użyj całkowitej niezależności, jeśli rzeczywista liczba wierszy jest mniejsza niż przewidywana kardynalność.

Aby uzyskać więcej informacji, zobacz wersje CE.

Sprzężenie informacyjnie zwrotne w szacowaniu kardynalności (CE)

Gdy optymalizator zapytań szacuje wybór predykatów sprzężenia i odpowiednich predykatów filtrów, używa założeń modelu zawierania. Te założenia są następujące:

• Proste ograniczenie (ustawienie domyślne dla CE70) zakłada, że predykaty sprzężenia są w pełni skorelowane, gdzie selektywność filtru jest obliczana najpierw, a następnie uwzględniana jest selektywność sprzężenia.

• Podstawowe ograniczenie (ustawienie domyślne dla CE120 i nowszych) zakłada brak korelacji między predykatami łączenia a filtrami dalszego przetwarzania, gdzie najpierw obliczana jest selektywność łączenia, a następnie uwzględniana jest selektywność filtru.

W poniższym przykładzie użyto podstawowego zawierania, gdy zgodność bazy danych jest ustawiona na 120 lub nowszą:

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

Aby uzyskać więcej informacji, zapoznaj się z wersjami CE.

Opinie zwrotne dotyczące szacowania kardynalności (CE) i docelowa liczba wierszy optymalizatora zapytań

Gdy optymalizator zapytań szacuje kardynalność planu wykonywania, zwykle zakłada, że wszystkie kwalifikujące się wiersze ze wszystkich tabel muszą być przetwarzane. Jednak niektóre wzorce zapytań powodują, że optymalizator zapytań wyszuka plan, który zwróci mniejszą liczbę wierszy w celu zmniejszenia liczby operacji we/wy. Jeśli zapytanie określa docelową liczbę wierszy (cel wiersza), które mogą być oczekiwane w czasie wykonywania przy użyciu TOPIN słów kluczowych , EXISTSFAST wskazówek zapytania lub SET ROWCOUNT instrukcji, ten cel wiersza jest używany w ramach procesu optymalizacji zapytania, na przykład w poniższym przykładzie:

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

Po zastosowaniu planu celu wiersza szacowana liczba wierszy w planie zapytania jest zmniejszana, ponieważ optymalizator zapytań zakłada, że mniejsza liczba wierszy będzie musiała zostać przetworzona w celu osiągnięcia celu wiersza.

Chociaż cel wiersza jest korzystną strategią optymalizacji dla niektórych wzorców zapytań, jeśli dane nie są równomiernie rozproszone, więcej stron może być skanowanych niż szacowane, co oznacza, że cel wiersza staje się nieefektywny. Opinie CE mogą wyłączyć skanowanie celu wiersza i włączyć wyszukiwanie po wykryciu tej nieefektywności.

W planie wykonywania nie ma atrybutu dotyczącego informacji zwrotnej CE, ale jest wymieniony atrybut dla wskazówki w Store zapytań. Szukaj, aby QueryStoreStatementHintSource stało się CE feedback.

Zagadnienia dotyczące szacowania kardynalności (CE): informacja zwrotna

• Aby włączyć informacje zwrotne dotyczące estymacji kardynalności (CE), ustaw poziom zgodności bazy danych 160 dla bazy danych, do której jesteś podłączony podczas wykonywania zapytania. Magazyn zapytań musi być włączony i w trybie READ_WRITE dla każdej bazy danych, w której jest używana informacja zwrotna CE.

• Aby wyłączyć informację zwrotną CE na poziomie bazy danych, użyj CE_FEEDBACKkonfiguracji ograniczonej do bazy danych. Na przykład w bazie danych użytkownika:

  ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
  

• Aby wyłączyć informacje zwrotne CE na poziomie zapytania, użyj DISABLE_CE_FEEDBACK podpowiedzi zapytania.

Widoczność opinii CE jest możliwa za pomocą zdarzeń query_feedback_analysis i query_feedback_validation XEvents.

Można śledzić wskazówki ustawione na podstawie opinii CE za pomocą widoku katalogu sys.query_store_query_hints.

Informacje zwrotne można śledzić przy użyciu widoku katalogu sys.query_store_plan_feedback .

Jeśli zapytanie ma plan zapytania wymuszony za pośrednictwem Query Store, informacja zwrotna CE nie jest używana dla tego zapytania.

Jeśli zapytanie używa zakodowanych na stałe podpowiedzi dotyczących zapytań lub używa podpowiedzi z Magazynu Zapytań ustawionych przez użytkownika, informacje zwrotne dotyczące CE nie są używane dla tego zapytania. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań i Wskazówka Query Store.

Począwszy od programu SQL Server 2022 (16.x), gdy "Magazyn Zapytań" dla replik pomocniczych jest włączony, informacje zwrotne CE nie uwzględniają replik dla replik pomocniczych w grupach dostępności. Opinie zwrotne CE obecnie przynoszą korzyści tylko podstawowym replikom. Podczas przełączenia awaryjnego, informacja zwrotna zastosowana do replik podstawowych lub pomocniczych zostanie utracona. Magazyn zapytań jest dostępny w pomocniczych replikach grup dostępności, począwszy od programu SQL Server 2025 (17.x). Aby uzyskać więcej informacji, zobacz Magazyn zapytań dla replik pomocniczych.

Trwałość informacji zwrotnych dotyczących szacowania kardynalności (CE)

Dotyczy: SQL Server 2022 (16.x) i nowszych wersji, Azure SQL Database, Azure SQL Managed Instance.

Opinie dotyczące szacowania kardynalności (CE) mogą wykrywać scenariusze, w których optymalizacja celu wiersza powinna być utrwalana, i zachować tę zmianę, utrwalając ją w magazynie zapytań w postaci wskazówki magazynu zapytań. Nowa optymalizacja jest używana do przyszłych wykonań zapytania. CE feedback występuje również w innych scenariuszach poza wzorcami zapytań związanymi z optymalizacją celu wiersza, zgodnie z opisem w scenariuszach informacji zwrotnej. Opinia zwrotna CE obecnie obsługuje scenariusze selektywności predykatu, które są używane przez model korelacji CE, oraz scenariusze predykatów sprzężenia, które są obsługiwane przez model zawartości CE.

Ta funkcja została wprowadzona w programie SQL Server 2022 (16.x), jednak ulepszenie wydajności jest dostępne dla zapytań działających na poziomie zgodności bazy danych 160 lub wyższym, sugestii QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n dotyczącej poziomu 160 i wyższego, oraz gdy magazyn zapytań jest aktywowany dla bazy danych i jest w trybie "odczytu i zapisu".

Znane problemy dotyczące szacowania kardynalności (CE) — opinie

Problematyka	Data odnalezienia	Status	Data rozwiązania
Niska wydajność programu SQL Server po zastosowaniu aktualizacji zbiorczej 8 dla programu SQL Server 2022 (16.x) w określonych warunkach. Może wystąpić dramatyczne wykorzystanie pamięci podręcznej planów oraz nieoczekiwany wzrost wykorzystania procesora po włączeniu opinii zwrotnej CE.	Grudzień 2023 r.	Rozwiązano	22 kwietnia 2024 r. (CU 12)

Szczegóły znanych problemów

Niska wydajność programu SQL Server po zastosowaniu aktualizacji zbiorczej 8 dla programu SQL Server 2022 w określonych warunkach

Począwszy od programu SQL Server 2022 (16.x) Aktualizacja zbiorcza 8, program SQL Server może wykazywać nieoczekiwane wzrosty użycia procesora CPU i pamięci. Ponadto można zaobserwować wzrost liczby oczekiwań typu RESOURCE_SEMAPHORE_QUERY_COMPILE. Można również zauważyć stały wzrost liczby obiektów pamięci podręcznej planu w użyciu, które zbliżają się do jej limitów, a ręczne czyszczenie pamięci podręcznej planu za pomocą takich technik jak ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE, lub DBCC FREEPROCCACHE okazuje się nieskuteczne. To zachowanie zaobserwowano tylko przez kilku klientów.

Problem nie wpływa na wszystkie zadania robocze i zależy od liczby różnych planów, które zostały wygenerowane, a także od liczby planów kwalifikujących się do uruchomienia funkcji opinii CE. Chociaż informacja zwrotna CE analizuje operatory planów pod kątem znaczących błędów oceny modeli, istnieje scenariusz, w którym w trakcie tej fazy analizy można dereferencjonować odwołany plan. Taka sytuacja uniemożliwia usunięcie planu z pamięci przy użyciu zwykłego algorytmu LRU (Least Recently Used). Mechanizm LRU jest jednym ze sposobów, w jaki program SQL Server wymusza zasady usuwania planów. Program SQL Server usuwa również plany z pamięci, jeśli system jest pod presją pamięci. Gdy program SQL Server próbuje usunąć plany, które zostały nieprawidłowo wyłuszone, nie może usunąć tych planów z pamięci podręcznej planu, co powoduje, że pamięć podręczna będzie nadal rosła. Rosnąca pamięć podręczna może zacząć powodować dodatkowe kompilacje, które ostatecznie wykorzystują więcej CPU i pamięci. Aby uzyskać więcej informacji, zobacz Plan Cache Internals.

Objaw: Liczba wpisów pamięci podręcznej planu używanych i jest oznaczona jako zanieczyszczona z planów SQL lub planów obiektów zwiększa się wraz z upływem czasu do 50 000 lub więcej. Jeśli widzisz wpisy pamięci podręcznej planu, które zaczynają zbliżać się do tego poziomu wraz z nieoczekiwanym wzrostem wykorzystania CPU, to system może mieć do czynienia z tym problemem. Poprawka jest dostarczana z aktualizacją zbiorczą 12 programu SQL Server 2022 (16.x). Zobacz KB5033663.

pl-PL: Do monitorowania liczby wpisów pamięci podręcznej planu używanych przez system, poniższe przykłady mogą być używane jako przekrój czasowy liczby aktualnych wpisów pamięci podręcznej planu. Na przykład okresowe obserwowanie liczby wpisów pamięci podręcznej planu, które są oznaczone jako brudne, jest jednym ze sposobów monitorowania tego zjawiska.

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;

Inny zestaw zapytań, które również udostępniają te same informacje co w poprzednim przykładzie, a jednocześnie umożliwiają obserwowanie dodatkowych metryk wydajności. Wskaźniki trafień pamięci podręcznej planu oraz liczba kompilacji w stosunku do liczby żądań wsadowych na sekundę zmniejszają się. Następujące zapytania mogą służyć do monitorowania systemu w czasie. Monitorowanie współczynnika trafień pamięci podręcznej (nieoczekiwane spadki), obiektów pamięci podręcznej w użyciu (wzrost liczby do poziomów zbliżających się do 50 000 bez zmniejszenia) oraz niższy niż oczekiwano współczynnik żądań wsadowych/s w porównaniu ze wzrostem liczby kompilacji/s.

--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'
);

Rozwiązanie

Jeśli system nadal doświadcza objawów opisanych wcześniej, po zastosowaniu aktualizacji zbiorczej 12 KB5033663 funkcja opinii CE może zostać wyłączona na poziomie bazy danych.

Aby odzyskać pamięć podręczną planów zajętą przez ten problem, konieczne jest ponowne uruchomienie instancji programu SQL Server. Tę akcję ponownego uruchomienia można wykonać po wyłączeniu funkcji zbierania opinii CE. Aby wyłączyć informacje zwrotne CE na poziomie bazy danych, użyj CE_FEEDBACKkonfiguracji zakresu bazy danych. Na przykład w bazie danych użytkownika:

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

Problemy z opiniami i raportowaniem

Aby uzyskać opinię lub pytania, wyślij wiadomość e-mail CEFfeedback@microsoft.com

Treści powiązane

• Informacja zwrotna dotycząca szacowania kardynalności w programie SQL Server 2022
• inteligentne przetwarzanie zapytań w bazach danych SQL
• Szczegółowe funkcje inteligentnego przetwarzania zapytań
• Estymacja kardynalności (SQL Server)
• KONFIGURUJ PONOWNIE (Transact-SQL)
• Monitorowanie i dostrajanie pod kątem wydajności
• ZMIEŃ KONFIGURACJĘ ZAKRESU BAZY DANYCH (Transact-SQL)