Оценка кратности (SQL Server)

Применимо к:SQL ServerAzure SQL База данныхAzure SQL Управляемый экземплярSQL База данных в Microsoft Fabric

Оптимизатор запросов SQL Server — это оптимизатор запросов на основе затрат. То есть оптимизатор выбирает планы запросов с наименьшей оценочной стоимостью их выполнения. Оптимизатор запросов определяет стоимость выполнения плана запроса исходя из двух основных факторов:

• Общее число строк, обрабатываемых на каждом уровне плана запроса, называемое кардинальностью плана.
• Модель стоимости алгоритма определяется операторами, используемыми в запросе.

Первый фактор, количество элементов, передается в качестве входного параметра второму фактору, модели стоимости. Следовательно, улучшенная оценка кардинальности приводит к более точным оценкам стоимости и, в свою очередь, к более быстрым планам выполнения.

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

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

• Запросы с предикатами, которые содержат операторы сравнения столбцов одной и той же таблицы.
• Запросы с предикатами, использующими операторы, и при этом выполняется любое из следующих условий:
  • Отсутствуют статистические данные для столбцов, используемых по обе стороны операторов.
  • Распределение значений в статистике неравномерно, а запрос выполняет поиск весьма ограниченного набора значений. Это может быть особенно верно, если оператор — любой, кроме оператора равенства (=).
  • предикат использует оператор неравенства (!=) или логический оператор NOT.
• Запросы, использующие любую из встроенных функций SQL Server или определяемую пользователем функцию со скалярным значением, аргумент которой не является постоянным значением.
• Запросы, включающие объединение столбцов с помощью арифметических операторов или операторов конкатенации строк.
• Запросы, сравнивающие переменные, значения которых неизвестны при компиляции и оптимизации запроса.

В этой статье показано, как оценить и выбрать оптимальную конфигурацию CE для вашей системы. В большинстве систем используется последняя версия CE, так как она наиболее точна. CE прогнозирует, сколько строк, вероятно, вернёт ваш запрос. Прогноз кратности используется оптимизатором запросов для создания оптимального плана запроса. Чем точнее оценки, тем лучше оптимизатор запросов, как правило, справляется с построением более оптимального плана запроса.

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

Версии CE

В 1998 году основное обновление CE было частью SQL Server 7.0, для которого уровень совместимости составил 70. Эта версия модели CE основана на четырех допущениях.

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

• Единообразие: отдельные значения равномерно распределены и имеют одинаковую частоту. Говоря точнее, отдельные значения равномерно распределены на каждом шаге гистограммы и все значения имеют одинаковую частоту.

• Содержательность (простая): Пользователи запрашивают данные, которые существуют. Например, в случае соединения по равенству между двумя таблицами учитывайте избирательность предикатов ¹ в каждой входной гистограмме перед соединением гистограмм для оценки избирательности соединения.

• Включение: для предикатов фильтра, где Column = Constant, предполагается, что константа фактически существует для связанного столбца. Если соответствующий шаг гистограммы не пуст, предполагается, что одно из конкретных значений шага совпадает со значением из предиката.

  ¹ Число строк, удовлетворяющее предикат.

Последующие обновления начались с SQL Server 2014 (12.x), что означает уровни совместимости 120 и выше. Обновления CE для уровней 120 и выше включают обновленные допущения и алгоритмы, которые хорошо сочетаются с современными хранилищами данных и рабочими нагрузками OLTP. Из допущений CE 70 были изменены следующие допущения моделей, начиная с CE 120:

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

Использование хранилища запросов для оценки версии CE

Начиная с SQL Server 2016 (13.x), хранилище запросов — это удобное средство для изучения производительности запросов. Когда хранилище запросов включено, оно отслеживает производительность запросов с течением времени, даже если планы выполнения меняются. Мониторинг хранилища запросов позволяет выявлять запросы с высокими накладными расходами или сниженной производительностью. Дополнительные сведения см. в статье "Мониторинг производительности с помощью хранилища запросов".

При подготовке к обновлению до SQL Server или повышении уровня совместимости базы данных на любой платформе SQL Server рассмотрите возможность обновления баз данных с помощью помощника по настройке запросов, что может помочь сравнить производительность запросов на двух разных уровнях совместимости.

Использование расширенных событий для оценки версии CE

Другим вариантом отслеживания процесса оценки кратности является использование расширенного события с именем query_optimizer_estimate_cardinality. Следующий пример кода Transact-SQL выполняется в SQL Server. Он записывает XEL-файл в папку C:\Temp\ (хотя этот путь можно изменить). При открытии XEL-файла в Management Studio его подробные сведения отображаются понятным образом.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;
go

CREATE EVENT SESSION Test_the_CE_qoec_1
ON SERVER
ADD EVENT sqlserver.query_optimizer_estimate_cardinality
 (
 ACTION (sqlserver.sql_text)
  WHERE (
  sql_text LIKE '%yourTable%'
  and sql_text LIKE '%SUM(%'
  )
 )
ADD TARGET package0.asynchronous_file_target
 (SET
  filename = 'c:\temp\xe_qoec_1.xel',
  metadatafile = 'c:\temp\xe_qoec_1.xem'
 );
GO

ALTER EVENT SESSION Test_the_CE_qoec_1
ON SERVER
STATE = START;  --STOP;
GO

Сведения о расширенных событиях, адаптированных для SQL Database, см. в статье Расширенные события в SQL Database.

Процедура оценки версии CE

Ниже приведены действия, которые помогут вам оценить, не работает ли какой-либо из ваших наиболее важных запросов хуже в последней версии CE. Для выполнения некоторых шагов нужно выполнить пример кода из предыдущего раздела.

1. Откройте SQL Server Management Studio (SSMS). Убедитесь, что для вашей базы данных SQL Server задан наивысший доступный уровень совместимости.

2. Выполните следующие подготовительные действия:

   1. Откройте SQL Server Management Studio (SSMS).

   2. Запустите Transact-SQL, чтобы убедиться, что для вашей базы данных SQL Server задан наивысший доступный уровень совместимости.

   3. Убедитесь, что у вашей базы данных LEGACY_CARDINALITY_ESTIMATION конфигурация OFF.

   4. Очистите хранилище запросов. В базе данных убедитесь, что хранилище запросов включен.

   5. Выполните инструкцию SET NOCOUNT OFF;.

3. Выполните инструкцию SET STATISTICS XML ON;.

4. Выполните свой важный запрос.

5. На вкладке Сообщения в области результатов обратите внимание на фактическое число затронутых строк.

6. На вкладке Результаты в области результатов дважды щелкните ячейку, которая содержит статистику в формате XML. Отобразится графический план запроса.

7. Щелкните правой кнопкой мыши первое поле в графическом плане запроса и выберите Свойства.

8. Для последующего сравнения с другой конфигурацией обратите внимание на значения следующих свойств:

   • CardinalityEstimationModelVersion.

   • Предполагаемое количество строк.

   • Предполагаемые затраты на ввод/выводи несколько аналогичных предполагаемых свойств, которые имеют отношение к фактической производительности, а не прогнозу числа строк.

   • Логическая операция и Физическая операция. Параллелизм — подходящее значение.

   • Фактический режим выполнения. Пакет — хорошее значение (лучше чем Строка).

9. Сравните предполагаемое количество строк с фактическим. Показатель CE неточен на 1 % (в большую или меньшую сторону) или на 10 %?

10. Запуск: SET STATISTICS XML OFF;

11. Запустите Transact-SQL, чтобы снизить уровень совместимости базы данных на 1 (например, со 130 до 120).

12. Повторно выполните все шаги, кроме подготовительных.

13. Сравните значения свойства CE в обоих прогонах.

    • Процент неточности в новой версии CE ниже, чем в старой версии CE?

14. Наконец, сравните различные значения свойств производительности в обоих прогонах.

    • Ваш запрос обрабатывался по двум разным планам из-за того, что оценочные значения CE различались?

    • Ваш запрос выполнялся медленнее с последним CE?

    • В большинстве случаев следует использовать последний CE. Исключение составляют ситуации, когда при использовании старого CE запрос обрабатывался быстрее и по другому плану.

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

Активация оптимального плана запроса

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

• Можно установить более низкий уровень совместимости (не самый высокий из доступных) для всей базы данных.

  • Например, при задании уровня совместимости 110 или ниже активируется CE 70, но все запросы подчиняются предыдущей модели CE.

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

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

• Можно использовать подсказку запроса LEGACY_CARDINALITY_ESTIMATION, чтобы отдельный запрос использовал более старую версию CE, сохранив при этом остальные улучшения в оптимизаторе запросов.

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

• Принудительно использовать другой план с помощью хранилища запросов.

Уровень совместимости базы данных

Вы можете убедиться, что база данных находится на определенном уровне, используя следующий код Transact-SQL для ALTER DATABASE уровня совместимости (Transact-SQL).

SELECT ServerProperty('ProductVersion');
GO

SELECT d.name, d.compatibility_level
FROM sys.databases AS d
WHERE d.name = 'yourDatabase';
GO

Для существующих баз данных, работающих на более низких уровнях совместимости, если приложению не нужно использовать улучшения, доступные только на более высоком уровне совместимости базы данных, это допустимый подход для поддержания предыдущего уровня совместимости базы данных. Для новых работ по разработке или если существующее приложение требует использования новых функций, таких как интеллектуальная обработка запросов в базах данных SQL, а также некоторые новые возможности Transact-SQL, планируйте обновление уровня совместимости базы данных до последней доступной. Дополнительные сведения см. в разделе Уровни совместимости и обновления ядра СУБД.

ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 150;
GO

Для базы данных SQL Server с уровнем совместимости 120 и выше включение флага трассировки 9481 заставляет систему использовать версию CE 70.

Прежнее средство оценки кратности

Для базы данных SQL Server с уровнем совместимости 120 и выше устаревший механизм оценки кратности (CE версии 70) можно активировать на уровне базы данных с помощью ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

Изменить запрос для использования подсказки

Начиная с SQL Server 2016 (13.x) с пакетом обновления SP1, измените запрос, чтобы использовать указание запросаUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Установить подсказку для Хранилища запросов

Запросы можно принудительно настроить на использование устаревшего оценщика кратности без изменения запроса с помощью подсказок хранилище запросов.

1. Найдите запрос в представлениях каталога хранилища запросов sys.query_store_query_text и sys.query_store_query. Например, выполните поиск выполненного запроса по текстовому фрагменту:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt
   INNER JOIN sys.query_store_query q ON
   qt.query_text_id = q.query_text_id
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'
   AND query_sql_text not like N'%query_store%';
   

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

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Как принудительно использовать конкретный план запроса

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

Хранилище запросов предоставляет различные способы принудительно использовать определенный план выполнения запроса системой:

• Выполните процедуру sys.sp_query_store_force_plan.

• В SQL Server Management Studio (SSMS) разверните узел Хранилище запросов, щелкните правой кнопкой мыши Top Resource Consuming Nodes (Ключевые узлы — потребители ресурсов) и выберите View Top Resource Consuming Nodes (Просмотр ключевых узлов — потребителей ресурсов). На дисплее отображаются кнопки Принудительное использование плана и Отменить принудительное использование плана.

Дополнительные сведения о хранилище запросов см. в статье "Мониторинг производительности с помощью хранилища запросов".

Свертка констант и вычисление выражений во время оценки кардинальности

Ядро СУБД оценивает некоторые константные выражения раньше, чтобы повысить производительность запросов. Это называет сверткой констант. Константа — это литерал Transact-SQL, например3, 'ABC', '2005-12-31', 1.0e3 или 0x12345678. Дополнительные сведения см. в статье Свертка констант.

Кроме того, некоторые выражения, которые не подвергаются константной свёртке, но чьи аргументы известны во время компиляции, независимо от того, являются ли эти аргументы параметрами или константами, вычисляются во время оптимизации оценщиком размера набора результатов (кардинальности), который является частью оптимизатора запросов. Дополнительные сведения см. в статье Вычисление выражений.

Лучшие практики: использование свертки констант и вычисления выражений во время компиляции для создания оптимальных планов запросов

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

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

• Избегайте использования локальных переменных в запросах. Вместо этого используйте параметры, литералы или выражения в запросе.

• Ограничить использование операторов и функций, внедренных в запрос, содержащий параметр, к тем, которые перечислены в разделе "Оценка выражений во время компиляции" для оценки кратности.

• Убедитесь, что выражения, содержащие только константы, в условии запроса либо допускают константную свёртку, либо могут быть вычислены во время компиляции.

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

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

  • Постройте строку, которая содержит запрос, основанный на значении локальной переменной, и выполните строку с помощью динамического SQL (EXEC или лучше процедуры sp_executesql).

  • Параметризируйте запрос и выполните его с помощью процедуры sp_executesql, передайте значение переменной в запрос в качестве параметра.

Примеры улучшений CE

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

Пример A. CE учитывает, что максимальное значение может быть выше, чем при последнем сборе статистики

Предположим, статистические данные для OrderTable в последний раз были собраны 2016-04-30, когда максимальное значение OrderAddedDate составляло 2016-04-30. В CE 120 (а также в более поздних версиях) учитывается то, что столбцы в OrderTable с данными по возрастанию содержали значения, превышающие записанный в статистике максимум. Это понимание улучшает план запроса для инструкций Transact-SQL SELECT , таких как ниже.

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01';

Пример Б. CE учитывает, что предикаты с фильтрацией для одной и той же таблицы часто коррелируют

В приведенном ниже примере SELECT отображаются отфильтрованные предикаты Model и ModelVariant. Мы интуитивно понимаем, что если Model имеет значение "Xbox", есть вероятность, что ModelVariant имеет значение "One", учитывая, что у консоли Xbox есть модель One.

Начиная с CE 120, SQL Server понимает, что между двумя столбцами в одной и той же таблице, Model и ModelVariant, может существовать корреляция. CE более точно оценивает, сколько строк будет возвращено запросом, а оптимизатор запросов создает оптимизированный план.

SELECT Model, Purchase_Price
FROM dbo.Hardware
WHERE Model = 'Xbox' AND
ModelVariant = 'Series X';

Пример В. При расчете CE мы более не допускаем никаких корреляций между фильтрованными предикатами из разных таблиц

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

SELECT s.ticket, s.customer, r.store
FROM dbo.Sales AS s
CROSS JOIN dbo.Returns AS r
WHERE s.ticket = r.ticket AND
s.type = 'toy' AND
r.date = '2016-05-11';

Связанный контент

• Обратная связь по оценке кратности (CE)
• Наблюдение и настройка производительности
• Оптимизация планов выполнения запросов с помощью оценщика кратности SQL Server 2014
• Подсказки запросов (Transact-SQL)
• Указания запросов USE HINT
• Обновление баз данных с помощью помощника по настройке запросов
• Мониторинг производительности с использованием хранилища запросов
• Руководство по архитектуре обработки запросов
• Указания хранилища запросов
• Интеллектуальная обработка запросов в базах данных SQL
• Установка флагов трассировки с помощью DBCC TRACEON (Transact-SQL)
• sys.query_store_query_hints