Оценка количества элементов (SQL Server)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

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

• Запросы с предикатами, которые содержат операторы сравнения столбцов одной и той же таблицы.
• Запросы с предикатами, которые используют операторы, и любое из следующих значений имеет значение true:
  • отсутствует статистика для столбца, указанного с любой стороны от оператора;
  • Распределение значений в статистике неравномерно, а запрос выполняет поиск весьма ограниченного набора значений. Эта ситуация особенно вероятна, если оператор не является оператором равенства (=);
  • предикат использует оператор неравенства (!=) или логический оператор 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  

Примечание.

Событие sqlserver.query_optimizer_estimate_cardinality недоступно для База данных SQL Azure.

Сведения о расширенных событиях, адаптированных для База данных SQL, см. в База данных SQL расширенных событий.

Процедура оценки версии 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.

   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 ниже, чем со старым?

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

    • Ваш запрос обрабатывался по двум разным планам из-за того, что оценочные значения 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 для COMPATIBILITY_LEVEL.

Внимание

Номера версий ядра СУБД для SQL Server и базы данных SQL Azure не сравнимы друг с другом; они являются внутренними номерами сборок этих отдельных продуктов. Ядро СУБД для Azure SQL Server основано на той же базе кода, что и ядро СУБД SQL Server. Что важнее всего, ядро СУБД в базе данных SQL Azure всегда имеет самые новые части ядра СУБД SQL. Версия 12 базы данных SQL Azure более новая, чем версия 15 SQL Server. По состоянию на ноябрь 2019 года в База данных SQL Azure уровень совместимости по умолчанию составляет 150 для вновь созданных баз данных. Корпорация Майкрософт не обновляет уровень совместимости базы данных для существующих баз данных. Это осуществляют заказчики по собственному усмотрению.

SELECT ServerProperty('ProductVersion');  
GO  

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

Для существующих баз данных, работающих на более низких уровнях совместимости, если приложению не нужно использовать улучшения, доступные только на более высоком уровне совместимости базы данных, это допустимый подход для поддержания предыдущего уровня совместимости базы данных. При разработке нового приложения или добавлении новых возможностей в существующее, например интеллектуальная обработка запросов или новые инструкции 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) с пакетом обновления 1 (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''))';
   

Примечание.

Дополнительные сведения см. в статье Указания хранилища запросов (предварительная версия). В настоящее время эта функция доступна только в База данных SQL Azure.

Настройка принудительного использования определенного плана запросов

Для еще более точного контроля можно назначить принудительное использование системой плана, составленного с 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. Дополнительные сведения см. в статье Свертка констант.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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