Обратная связь по оценке кардинальности (CE)

Применимо к: SQL Server 2022 (16.x) и более поздних версий База данных Azure SQL Управляемый экземпляр Azure SQL

Начиная с SQL Server 2022 (16.x), обратная связь для оценки кратности (CE) является частью семейства функций интеллектуальной обработки запросов и позволяет устранить неоптимальные планы выполнения для повторяющихся запросов, если эти проблемы вызваны неверными предположениями модели CE. Этот сценарий помогает снизить риски регрессии, связанные со средством оценки кратности по умолчанию, при переходе с более ранних версий ядра СУБД.

Так как ни один из наборов моделей CE не может удовлетворить широкий спектр клиентских рабочих нагрузок и распределений данных, обратная связь CE предоставляет адаптируемое решение, основанное на характеристиках среды выполнения запросов. Обратная связь CE выявляет и использует допущение модели, которое лучше соответствует данному запросу и распределению данных, чтобы повысить качество плана выполнения запроса. В настоящее время обратная связь CE позволяет выявлять операторы плана, для которых оценочное и фактическое число строк сильно различаются. Обратная связь применяется при возникновении значительных ошибок оценки модели, и есть жизнеспособная альтернативная модель, которую следует попробовать.

Сведения о других функциях обратной связи по запросам см. в разделе Обратная связь по выделению памяти и Обратная связь по степени параллелизма (DOP).

Сведения об обратной связи по оценке кратности (CE)

Оценка кратности (CE) заключается в том, как оптимизатор запросов может оценить общее количество строк, обработанных на каждом уровне плана запроса. Оценка кратности в SQL Server главным образом определяется на основе гистограмм, которые создаются автоматически или вручную после создания индексов или статистик. Иногда SQL Server также использует сведения об ограничениях и логические перезаписи запросов для определения кратности.

В разных версиях ядра СУБД используются разные предположения модели CE в зависимости от того, как распределяются и запрашиваются данные. Дополнительные сведения см. в статье о версиях CE.

Реализация обратной связи для оценки мощности (кардинальности) (CE)

Обратная связь по оценке кардинальности (CE) со временем определяет, какие предположения модели CE являются оптимальными, а затем применяет предположение, которое исторически оказывалось наиболее точным:

Обратная связь CE определяет предположения, связанные с моделью, и оценивает, точны ли они для повторяющихся запросов.
Если предположение выглядит неверным, последующее выполнение того же запроса проверяется с помощью плана запроса, который корректирует важное предположение модели CE и проверяет, помогает ли оно. Мы определяем некорректность, сравнивая фактическое и оценочное количество строк у операторов плана выполнения. Не все ошибки могут быть исправлены вариантами модели, доступными в отзыве CE.
Если это улучшает качество плана, старый план запроса заменяется на план запроса, который использует соответствующую подсказку USE HINT, корректирующую модель оценки, реализованную через механизм подсказок хранилище запросов.

Сохраняется только проверенный отзыв. Обратная связь CE не используется для этого запроса, если скорректированное предположение модели приводит к снижению производительности. В этом контексте отмененный пользователем запрос также воспринимается как регрессия.

Сценарии обратной связи для оценки кратности (CE)

Обратная связь по оценке мощности (CE) помогает устранить предполагаемые проблемы регрессии, возникающие из-за неверных допущений модели CE при использовании CE по умолчанию (CE120 и выше), и может выборочно использовать другие допущения модели. К сценариям относятся корреляция, присоединение и цель строки оптимизатора.

Корреляция обратной связи для оценки кратности (CE)

Когда оптимизатор запросов оценивает выборку предикатов для заданной таблицы, представления или количества строк, удовлетворяющих указанному предикату, он использует предположения модели корреляции. Эти предположения могут заключаться в том, что предикаты:

Полностью независимый (по умолчанию для CE70), где кратность вычисляется путем умножения избирательности всех предикатов.
Частичная корреляция (по умолчанию для CE120 и выше), при которой кардинальность вычисляется с использованием варианта экспоненциального уменьшения, а предикаты упорядочиваются от наиболее селективного к наименее селективному.
Полностью коррелированный, где кратность вычисляется с помощью минимальной избирательности для всех предикатов.

В следующем примере используется частичная корреляция, когда для совместимости базы данных задано значение 120 или выше:

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

Если уровень совместимости базы данных установлен на 160 и используется корреляция по умолчанию, механизм обратной связи CE пытается пошагово корректировать корреляцию в нужном направлении в зависимости от того, была ли оценка мощности занижена или завышена по сравнению с фактическим количеством строк. Используйте полную корреляцию, если фактическое количество строк превышает предполагаемую кардинальность. Используйте полную независимость, если фактическое количество строк меньше, чем оценочная кардинальность.

Дополнительные сведения см. в статье о версиях CE.

Обратная связь по оценке кардинальности (CE) для включения соединения

Когда оптимизатор запросов оценивает избирательность предикатов соединения и применимых предикатов фильтров, он использует предположения модели автономности. К предположениям относятся:

Простая автономность (по умолчанию для CE70) предполагает, что предикаты соединения полностью коррелированы, при этом сначала вычисляется избирательность фильтра, а затем учитывается избирательность соединения.
Базовое включение (по умолчанию для CE120 и выше) предполагает отсутствие корреляции между предикатами соединения и последующими фильтрами, при этом сначала вычисляется селективность соединения, а затем учитывается селективность фильтра.

В следующем примере используется частичное сдерживание, если уровень совместимости базы данных установлен на 120 или выше:

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

Дополнительные сведения см. в статье о версиях CE.

Оценка кратности (CE) и цель строки оптимизатора запросов

Когда оптимизатор запросов оценивает кратность плана выполнения, он обычно предполагает, что должны быть обработаны все подходящие строки из всех таблиц. Однако из-за наличия некоторых шаблонов запросов оптимизатор запросов выполняет поиск плана, который будет возвращать меньшее количество строк для сокращения операций ввода-вывода. Если запрос указывает целевое число строк (цель строки), которые могут ожидаться во время выполнения с помощью TOPINEXISTS ключевых слов, FAST подсказки запроса или SET ROWCOUNT инструкции, цель строки используется как часть процесса оптимизации запросов, например в следующем примере:

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

Когда применяется план цели строки, расчетное количество строк в плане запроса уменьшается, так как оптимизатор запросов предполагает, что для достижения цели строки необходимо обработать меньшее количество строк.

Хотя оптимизация по целевому числу строк является полезной стратегией для некоторых типов запросов, если данные распределены неравномерно, может потребоваться просканировать больше страниц, чем предполагалось, из-за чего такая оптимизация становится неэффективной. Обратная связь CE может отключить сканирование цели строки и включить поиск при обнаружении этой неэффективности.

В плане выполнения нет атрибута, относящегося к обратной связи по CE, но есть атрибут, указанный для подсказки хранилище запросов. Убедитесь, что QueryStoreStatementHintSource имеет значение CE feedback.

Сохранение обратной связи для оценки кратности (CE)

Область применения: SQL Server 2022 (16.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure.

Обратная связь по оценке кратности (CE) может выявлять ситуации, в которых следует сохранять оптимизацию по целевому числу строк, и сохранять это изменение в хранилище запросов в виде подсказки хранилище запросов. Новая оптимизация используется для будущих выполнения запроса. Обратная связь CE сохраняется и в других сценариях, помимо шаблонов запросов с оптимизацией по целевому числу строк, как описано в сценариях обратной связи. Обратная связь CE в настоящее время обрабатывает сценарии выбора предиката, используемые моделью корреляции CE, и сценарии объединения предиката, которые обрабатываются моделью хранения CE.

Эта функция была представлена в SQL Server 2022 (16.x), однако это улучшение производительности доступно для запросов, выполняющихся в базе данных с уровнем совместимости 160 или выше, или при использовании подсказки QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n со значением 160 и выше, а также когда для базы данных включено хранилище запросов и оно находится в состоянии "чтение-запись".

Известные проблемы с обратной связью по оценке мощности (CE)

Проблема	Дата обнаружения	Состояние	Дата решения
Низкая производительность SQL Server после применения накопительного обновления 8 для SQL Server 2022 (16.x) в определенных условиях. При включении функции обратной связи CE может наблюдаться резкое увеличение использования памяти кэша планов, а также неожиданное увеличение загрузки ЦП.	Декабрь 2023 г.	Решено	22 апреля 2024 г. (CU 12)

Известные проблемы

Низкая производительность SQL Server после применения накопительного обновления 8 для SQL Server 2022 в определенных условиях

Начиная с накопительного обновления 8 для SQL Server 2022 (16.x), SQL Server может демонстрировать неожиданное увеличение загрузки ЦП и использования памяти. Кроме того, может наблюдаться увеличение RESOURCE_SEMAPHORE_QUERY_COMPILE ожиданий. Кроме того, вы можете заметить устойчивое увеличение числа используемых объектов кэша планов, которое приближается к пределам кэша планов, а ручная очистка кэша планов с помощью таких методов, как ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE или DBCC FREEPROCCACHE, не помогает. Это поведение наблюдалось только несколькими клиентами.

Эта проблема не влияет на все рабочие нагрузки и зависит от количества различных планов, созданных, а также количества планов, которые были доступны для взаимодействия с функцией обратной связи CE. Пока механизм обратной связи CE анализирует операторы плана на предмет существенных ошибок оценки модели, возможен сценарий, при котором ссылка на используемый план может быть снята на этом этапе анализа. Эта ситуация предотвращает удаление плана из памяти с помощью обычного алгоритма наименее недавно использованного (LRU). Механизм LRU является одним из способов, с помощью которых SQL Server реализует политики вытеснения планов выполнения. SQL Server также удаляет планы из памяти, если система находится под давлением памяти. Когда SQL Server пытается удалить планы, на которые были некорректно сняты ссылки, ему не удается удалить эти планы из кэша планов, из-за чего кэш продолжает расти. Растущий кэш может привести к дополнительным компиляциям, которые в конечном итоге используют больше ЦП и памяти. Дополнительные сведения см. в разделе "Внутренние кэши планов".

Симптом: количество используемых записей кэша планов, помеченных как грязные, в кэше планов SQL или планов объектов со временем увеличивается до 50 000 и более. Если вы наблюдаете записи кэша планов, которые начинают приближаться к этому уровню вместе с непредвиденным увеличением использования ЦП, ваша система может столкнуться с этой проблемой. Исправление доступно в накопительном обновлении 12 для SQL Server 2022 (16.x). См . KB5033663.

Чтобы отслеживать количество записей в кэше планов, которые использует ваша система, можно использовать следующие примеры как срез на определённый момент времени, показывающий число существующих записей в кэше планов. Например, просмотр количества записей кэша планов, помеченных как грязные, периодически с течением времени является одним из способов отслеживания этого явления.

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;

Другой набор запросов, которые также предоставляют те же сведения, что и предыдущий пример, а также позволяет наблюдать за дополнительными метриками производительности. Показатели попаданий в кэш планов снижаются, как и число компиляций по отношению к числу пакетных запросов в секунду. Следующие запросы можно использовать для мониторинга системы в динамике. Следите за коэффициентом попаданий в кэш (неожиданные падения), используемыми объектами кэша (увеличение количества до значений, приближающихся к 50 000, без последующего снижения), а также за более низким, чем ожидалось, значением пакетных запросов/с по сравнению с ростом компиляций/с.

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

Обходное решение

Если система продолжает испытывать симптомы, описанные ранее, после применения накопительного обновления 12 KB5033663, функция обратной связи CE может быть отключена на уровне базы данных.

Чтобы освободить память кэша плана, занятую этой проблемой, требуется перезапуск экземпляра SQL Server. Это действие перезапуска можно предпринять после отключения функции обратной связи CE. Чтобы отключить обратную связь CE на уровне базы данных, используйте CE_FEEDBACKконфигурацию уровня базы данных. Например, в пользовательской базе данных:

ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = OFF;

Проблемы с отзывами и отчетами

Отзывы или вопросы, электронная почта CEFfeedback@microsoft.com