План выполнения запроса в Microsoft SQL Server – что это такое и для чего он нужен

Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы с Вами поговорим о том, что же такое план выполнения запроса в Microsoft SQL Server, узнаем, для чего он нужен и как используется, а также какие виды планов выполнения запросов бывают и почему разработчик T-SQL должен уметь работать с планами запросов.

Заметка! Что нужно знать и уметь разработчику T-SQL.

Введение

Ранее, в предыдущем материале, мы с Вами рассмотрели архитектуру обработки SQL запросов в Microsoft SQL Server, где выяснили, что происходит с момента, когда мы нажали кнопку «Выполнить», т.е. послали SQL запрос на сервер, до того момента, когда мы увидели запрашиваемые данные. Иными словами, SQL Server перед тем, как вернуть нам результат SQL инструкции, выполняет достаточно много сложных различных операций.

Компонент, который отвечает за обработку SQL запросов в Microsoft SQL Server называется Relational Engine.

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

Текст SQL запроса внутри компонента Relational Engine проходит через несколько этапов обработки. Можно выделить 3 основных этапа, это:

• Query Parsing
• Query Optimization
• Query Execution

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

Таким образом, план выполнения запроса формируется на этапе Query Optimization, а формируется он, так называемым, многим известным «Оптимизатором запросов».

Более подробно про архитектуру обработки SQL запросов в Microsoft SQL Server можете почитать в материале

Архитектура обработки SQL запросов в Microsoft SQL Server

План выполнения запроса в Microsoft SQL Server

Оптимизатор запросов – это компонент Microsoft SQL Server, который отвечает за построение плана выполнения запроса.

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

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

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

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

Например, если в запросе участвует несколько таблиц, у SQL Server появляется несколько вариантов последовательного обращения к этим таблицам. Допустим, в запросе участвует 3 таблицы: A, B, C, так вот SQL Server может сначала извлечь данные из A, потом, используя полученные данные, обратиться к B, а затем к C. Но в то же время он может обратиться сначала к B, потом к A, а потом к C, или сначала к C, затем к A, потом к B, и различные другие варианты. В данном случае SQL Server будет перебирать возможные варианты последовательности обращения к таблицам, т.е. искать наиболее эффективный вариант.

Заметка! Транзакции в T-SQL – основы для новичков с примерами.

Также в процессе этого SQL Server будет искать наиболее эффективный метод соединения данных двух таблиц, дело в том, что в синтаксисе мы пишем JOIN, но физически данные могут быть соединены с использование разных алгоритмов, таких как: NESTED LOOPS, HASH MATCH, MERGE – это физические операторы соединения таблиц.

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

Таким образом, оптимизатор запросов перед тем, как сформировать итоговый план выполнения запроса, анализирует много различных факторов, и перебирает много различных планов выполнения, чтобы в конечном итоге сформировать оптимальный план. И здесь стоит отметить, что оптимизатор ищет именно оптимальный, так называемый «достаточно хороший план» выполнения запроса, дело в том, что на поиск самого лучшего плана оптимизатору может потребоваться очень много времени, т.е. в результате это может быть неэффективно, так как, допустим, оптимизатор может потратить 1 секунду на поиск самого лучшего плана, который выполнится за 0.1 секунды (т.е. суммарно запрос выполнится за 1.1 секунды), хотя, если оптимизатор не искал бы самый лучший план, он мог, например, за 0.1 секунды найти план, который выполнит запрос за 0.2 секунды (т.е. суммарно запрос выполнится за 0.3 секунды), что в конечном счете будет намного быстрее, чем при самом лучшем плане. Поэтому здесь нужен компромисс и оптимизатор ищет именно оптимальный план выполнения запроса.

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

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

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

Стадии оптимизации запроса

Как было уже отмечено, работа оптимизатора заключается в поиске оптимального плана выполнения запроса, поэтому процесс оптимизации запроса, включает несколько этапов, в частности:

• Simplification
• Trivial Plan Optimization
• Full Optimization
  • Search 0
  • Search 1
  • Search 2

Simplification

На данном этапе происходит упрощение дерева запроса, например:

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

Trivial Plan Optimization

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

Full Optimization: Search 0

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

Full Optimization: Search 1

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

Full Optimization: Search 2

Это самый последний этап оптимизации, на котором в любом случае будет найден тот или иной план выполнения запроса.

Заметка! Чем отличаются функции от хранимых процедур в T-SQL.

Как определить, какой именно план найден

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

В данном случае необходимо смотреть на свойство Optimization Level (Уровень оптимизации):

• TRIVIAL – запрос выполнен с использованием тривиального плана;
• FULL – к запросу применены необходимые правила оптимизации.

Чтобы посмотреть, по какой причине оптимизатор остановил поиск плана запроса, т.е. какой именно план был использован, необходимо смотреть на свойство Reason For Early Termination (Причина преждевременного завершения оптимизации инструкции):

• Good Enough Plan Found – по внутренней системе оценок найден достаточно хороший план (наиболее предпочтительное значение);
• Time Out – оптимизатор не успел применить все правила оптимизации и перебрать все возможные варианты плана запроса, так как на весь процесс он затратил (по внутренней системе оценок) достаточно большое количество времени, поэтому он вернул лучшее, что у него было к этому моменту. Данное значение не очень хорошее и свидетельствует о том, что план запроса скорей всего не оптимален, т.е. SQL запрос необходимо оптимизировать;
• Memory Limit Exceeded – свидетельствует о больших проблемах с памятью, т.е. ее даже не хватает на генерацию планов запроса.

Как посмотреть план выполнения запроса в Microsoft SQL Server

Какие бывают планы выполнения запроса

Оптимизатор запросов Microsoft SQL Server формирует только один план выполнения для запроса, однако существует несколько типов планов выполнения запроса, которые можно отобразить с помощью SQL Server Management Studio (SSMS).

Заметка! Обзор функционала SQL Server Management Studio (SSMS).

Предполагаемый план выполнения

Предполагаемый план выполнения (Estimated Execution Plan) – это план, созданный оптимизатором запросов на основе оценок.

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

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

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

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

Действительный план выполнения

Действительный план выполнения (Actual Execution Plan) – это план, созданный оптимизатором запросов после фактического выполнения запроса. Иными словами, план становится доступным после выполнения SQL инструкции. Поэтому такой план отображает фактические метрики использования ресурсов.

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

Статистика активных запросов

Статистика активных запросов (Live Query Statistics) – это план, который создаётся в режиме реального времени. Такой план доступен во время выполнения SQL запроса и обновляется каждую секунду, что позволяет нам просматривать динамический план выполнения активного запроса.

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

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

Примечание! Эта функция предназначена в основном для диагностики. Ее использование может значительно снизить общую производительность запроса.

Чем полезен план выполнения запроса разработчику T-SQL

Что такое план выполнения запроса в Microsoft SQL Server и как он формируется, мы рассмотрели, но кто-то может спросить – «это все хорошо, но зачем нам, как разработчикам (или администраторам), это нужно знать?».

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

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

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

Заметка! Основы индексов в Microsoft SQL Server.

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

В плане выполнения запроса дополнительно отображается информация о так называемой стоимости того или иного оператора, т.е. для каждой операции в плане запроса есть оценка ее нагрузки относительно всего запроса. Иными словами, с помощью плана запроса мы можем сразу увидеть, какие из операторов и, соответственно, операций, самые высоконагруженные в данном запросе. Хотя стоит учитывать то, что это всего лишь предварительная оценка стоимости, и иногда она не соответствует действительности (за счет действий, которые в плане запроса могут не отображаться). Но в любом случае, если какой-нибудь оператор в плане запроса занимает 99% нагрузки, то на него определенно стоит обратить внимание.

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

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

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

Поэтому следите за выходом новых статей в моих группах в социальных сетях: ВКонтакте, Facebook, Одноклассники и Twitter. Подписывайтесь, и Вы не пропустите выход нового материала!

Удачи!