SQL-Ex blog

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


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

В этом случае я изменил пороговое значение стоимости для параллелизма.

CTFP - это настройка уровня сервера, которая говорит: "Если стоимость запроса превышает это значение, рассмотрите возможность распараллеливания работы запроса между несколькими ядрами". Большинство контрольных списков по настройке SQL Server будут предполагать, что вы установили её в значение от принимаемого по умолчанию 5 до существенно большего, например, 40, 50 или 75. Я подробно рассматривал эту настройку в статье What is the CXPACKET Wait Type?

На первом скриншоте - где запрос выполняется в одном потоке - значение CTFP было установлено в 50, означающее, что SQL Server не должен учитывать распараллеливание запроса, если его стоимость будет ниже этого значения. Если навести указатель мышки на оператор SELECT первого запроса в плане выполнения, я могу увидеть оценку стоимости поддерева (Estimated Subtree Cost):


Estimated Subtree Cost - это представление SQL Server о том, сколько работы процессору и устройству ввода/вывода потребуется сделать для выполнения запроса. SQL Server не знает, насколько быстрые у вас процессор и хранилище, и эти числа жестко запрограммированы как у некоторого старого оборудования. Нам нужно было как-то отвечать, что означают эти цифры, поэтому Kendra Little придумала термин «Query Bucks» (баксы запросов).

Этот запрос стоит $5.57 баксов запроса, что меньше нашего порогового значения $50 для распараллеливания, и это был довольно простой запрос с точки зрения оптимизации, поэтому SQL Server рано вышел на построение плана запроса. Если вы выполните правый щелчок на операторе SELECT и щелкните Properties (свойства), то увидите, что свойство Optimization Level (уровень оптимизации) установлено в значение TRIVIAL.


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

Когда я сбрасываю пороговое значение распараллеливания обратно к значению по умолчанию 5:

• Стоимость запроса $5.79 сразу становится выше, чем CTPF ($5).


• SQL Server погружается глубже в оптимизацию запроса (Optimization Level переключается на “FULL”).


• Распараллеливание встраивается в план.


• В действие вступает код рекомендаций относительно отсутствующих индексов.

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


Стоимость теперь ВЫШЕ - $5.87 баксов запроса - но SQL Server считает, что он быстрей выполнит запрос, распределив работу по нескольким потокам. Это может оказаться неверным - но это будет неверным на основе ВАШИХ входных данных, которые определили пороговое значение для распараллеливания. Сам по себе SQL Server просто не знает достаточно хорошо вашу рабочую нагрузку, чтобы решить, какие запросы стоит распараллелить, а какие должны оставаться однопоточными для максимально эффективной балансировки нагрузки на нескольких ядрах. Необходима ваша заботливая рука на диске CTFP.

Любые изменения на вашем сервере могут изменить ваши планы.

Конечно, следующие вещи очевидны:

• Конфигурационные параметры уровня сервера (типа опций sp_configure).


• Аппаратные параметры (количество ядер и размеры доступной памяти меняют планы).


• Основная версия SQL Server (2019, 2017, 2016).


• Уровень пакета SQL Server (да, даже накопительные обновления меняют поведение оптимизатора).


• Количество данных - как много строк в ваших таблицах.


• Распределение различных значений.

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

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

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

Старайтесь держать рабочий сервер и сервер для разработки в идентичном состоянии, насколько это возможно. Чем ближе совпадают ваши серверы, тем ближе будут совпадать планы ваших запросов. Если на рабочем сервере стоит SQL Server 2017 с 48 ядрами и 2Тб оперативной памяти, а сервере для разработки - SQL Server 2019 c 4 ядрами и 16Гб памяти, и небольшое подмножество рабочих данных, у вас практически нет шансов получить хотя бы отдаленно похожие планы выполнения. Вам будет значительно сложнее - не невозможно, а просто сложнее - получить идентичные планы выполнения. Но в то же время...

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

В следующем посте мы расширим наш запрос, добавив действий, и посмотрим, как будут меняться планы.