SQL-Ex blog

В терминах SQL Server ядром базы данных выполняются следующие основные шаги.

Parsing (синтаксический разбор) -> Binding (связывание) -> Query Optimization (оптимизация запроса -> Query Execution (выполнение запроса)

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

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

Что такое тривиальный план запроса?

Оптимизатор запросов в SQL Server основан на стоимости, он генерирует различные варианты планов выполнения запроса, а затем выбирает план с наименьшей стоимостью. Однако этот сценарий изменяется, если полученный запрос очень простой, и имеется только один возможный план. В этой ситуации оптимизатор игнорирует поиск оптимального плана, поскольку в результате будет только потрачено время. Мы называем те типы планов ТРИВИАЛЬНЫМИ, которые получаются в результате пропуска процессов оптимизации для простых запросов. Например, возьмем следующий запрос. Этот запрос выполняет только фильтрацию по одному столбцу таблицы Production и возвращает столбцы Name и ProductNumber.

SELECT Name,ProductNumber FROM Production.Product

WHERE ProductNumber = 'CA-6738'

После выполнения плана этого запроса мы можем увидеть, что атрибут Optimization Level показывает TRIVIAL. Исходя из этого мы можем понять, что оптимизатор остановился на тривиальном плане для этого запроса.



Представление sys.dm_exec_query_optimizer_info возвращает подробную статистику о работе оптимизатора запросов. Отсюда мы можем получить некоторые знания о том, что делал оптимизатор под капотом. Выполним тестовый запрос дважды и посмотрим столбец occurrence, поскольку он показывает число произошедших событий оптимизатора. Чтобы правильно выполнить тест, сначала нам нужно почистить кэшированный план запроса. С помощью следующего запроса мы может очистить кэшированный план нашего запроса.

DECLARE @PlanHandle VARBINARY(64);

SELECT @PlanHandle=cp.plan_handle

FROM sys.dm_exec_cached_plans cp

OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) st

OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE st.TEXT LIKE '%Production%ProductNumber%'

AND st.TEXT NOT LIKE '%dm_exec_cached_plans%';

DBCC FREEPROCCACHE(@PlanHandle)

На следующем шаге мы выполним следующий пакет запросов.

SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'trivial plan';

GO

SELECT Name,ProductNumber FROM Production.Product WHERE ProductNumber = 'CA-6738';

GO

SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'trivial plan';

Как видно, столбец occurrence для тривиального плана получил приращение 1.

Тривиальный план и параллелизм

SQL Server может обрабатывать запросы более чем одним процессом. С помощью этой методологии время выполнения запроса ядром базы данных может сократиться. Maximum Degree of Parallelism и Cost Threshold for Parallelism - два параметра, которые должны быть правильно определены для перевода выполнения запроса в параллельный режим. Когда оценка стоимости поддерева тривиального запроса превышает пороговое значение стоимости параллелизма, оптимизатор переходит к следующим шагам оптимизации и также рассматривает параллельные планы запроса. Следующий запрос будет считать число строк в SalesOrderDetailEnlarged.

SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

Проверка атрибута Optimization Level показывает, что сгенерированный план выполнения является тривиальным, а предварительная стоимость поддерева составляет 21,0479. Это значение стоимости поддерева не превышает конфигурационного порога стоимости для параллелизма. Теперь мы уменьшим это значение ниже стоимости запроса и снова выполним тот же запрос.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE

GO

EXEC sp_configure 'cost threshold for parallelism', 10;

GO

RECONFIGURE

GO

EXEC sp_configure 'cost threshold for parallelism'

После установки стоимостного порога параллелизма выполним тот же запрос и посмотрим план выполнения.

SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

Можно увидеть, что план запроса полностью изменился, и оптимизатор отказался от тривиального плана и решил сгенерировать параллельный план выполнения.

Тривиальный план запроса и проверочные ограничения

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

select UnitPrice from Sales.SalesOrderDetail WHERE UnitPrice =-1

Теперь мы выполним другой запрос, и сгенерированный план выполнения этого запроса будет тривиальным.

SELECT CarrierTrackingNumber, UnitPrice FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 AND UnitPrice =-1

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

Взлом тривиальных планов

В некоторых случаях мы хотим запретить использование оптимизатором тривиальных планов. Для этого можно использовать флаг трассировки. Флаг трассировки 8757 делает невозможным генерацию тривиального плана. В следующем запросе мы будем использовать флаг трассировки 8757 и проанализируем изменения плана выполнения.

SELECT CarrierTrackingNumber, UnitPrice

FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659

and UnitPrice =-1

OPTION (QUERYTRACEON 8757)

План выполнения показывает, что оптимизатор завершил цикл оптимизации запроса, а затем нашел оптимальный план вместо тривиального. В качестве альтернативного метода мы можем добавить выражения 1=(SELECT 1), 1 IN (SELECT 1) или EXISTS(SELECT 1) в конец запроса.

SELECT CarrierTrackingNumber, UnitPrice

FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659

AND UnitPrice =-1

AND 1 IN (SELECT 1)

Хинт запроса ENABLE_PARALLEL_PLAN_PREFERENCE заставляет оптимизатор генерировать параллельный план. Незначительные изменения могут преобразовать тривиальный план в параллельный план выполнения.

SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

WHERE 1 IN (SELECT 1)

OPTION (QUERYTRACEON 8757,USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

Тривиальные планы и статистика

Статистика является жизненно важной входной информацией оптимизатора запросов, позволяющей рассчитать предварительную стоимость запросов. Если опция Auto Update Statistics для базы данных установлена в ON (включена), оптимизатор автоматически обновляет статистику во время выполнения запроса, когда счетчик модификаций для статистики превышает пороговое значение. Этот подход помогает улучшить производительность запросов, поскольку позволяет оптимизатору получить более правильные оценки. С другой стороны, обновление устаревшей статистик во время выполнения запроса может привести к потерям времени. Имея в виду эту информацию, рассмотрим поведение тривиального плана.

Сначала создадим очень простую таблицу и наполним её тестовым данными.

CREATE TABLE TestTrivialTable(IdNo INT IDENTITY(1,1),AnyNameColumn VARCHAR(10))

INSERT INTO TestTrivialTable

VALUES('TestValue')

GO 5000

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

CREATE EVENT SESSION [CaptureStatsUpdate] ON SERVER

ADD EVENT sqlserver.auto_stats(

ACTION(sqlserver.sql_text)

WHERE ([sqlserver].[session_id]=(72)))

WITH (TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

Мы запускаем мониторинг захвата данных расширенных событий, чтобы использовать опцию Watch Live Data. Теперь выполним следующий запрос.

SELECT * FROM TestTrivialTable WHERE AnyNameColumn='TestValue'

При первом выполнении запроса создается статистика, и оптимизатор использовал эту статистику.



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

INSERT INTO TestTrivialTable

VALUES('TestValue')

GO 10000

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

SELECT * FROM TestTrivialTable WHERE AnyNameColumn='TestValue'

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



На последнем шаге мы отказываемся от использования тривиального плана запроса, добавляя выражение 1=(SELECT 1) и повторно выполняя запрос.

SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

WHERE 1=(SELECT 1)

Статистика обновилась, когда мы запретили тривиальный план, при этом предварительное и фактические число строк стало одинаковым.