SQL-Ex blog

Планы выполнения

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

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

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

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

Сейчас же мы поучимся получать планы выполнения наших запросов.

Просмотр планов выполнения

Чтобы увидеть план выполнения вашего запроса, выполните команду SET SHOWPLAN_ALL ON. Это даст текстовое представление дерева плана:

SET SHOWPLAN_ALL ON

GO

--Здесь напишите запрос

GO

SET SHOWPLAN_ALL OFF

GO

Это может выглядеть для вас знакомо, если вы просматривали планы в других средах реляционных баз данных, которые используют аналогичное представление плана в виде дерева. В SSMS у нас есть еще более визуальный вариант. Если щелкнуть по кнопке “Display Estimated Execution Plan” (показать предварительный план выполнения) или выполнить команду SET SHOWPLAN_XML ON, а затем ваш запрос, вы получите графический план выполнения вашего запроса:





Я предпочитаю именно это графическое представление (его я и буду использовать в демонстрационных примерах этой серии), но важно знать, что, как подсказывает последняя команда, вы можете также выполнить щелчок правой кнопкой на этом графическом плане выполнения и выбрать “Show Execution Plan XML”, чтобы увидеть все, что он представляет, в формате XML:





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

Актуальные планы

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





Часто путаница возникает из-за названий планов - "оценочный" (предполагаемый) и "актуальный". Разница состоит в том, что оценочный план рассчитывается до выполнения оператора SQL, и поэтому для него доступны только оценочные метаданные для отображения, в то время как актуальный план выполнения представляет собой тот же самый оценочный план выполнения, скорректированный информацией времени выполнения, например, количеством обработанных строк, объемом использованной памяти и т.д.

Живая статистика запроса (Live Query Statistics)

Живая статистика запроса дает вам лучшее из обоих планов, оценочного и актуального. При включении Live Query Statistics, SQL Server предоставляет оценочный план выполнения, но скорректированный реальной статистикой времени выполнения вверху плана в то время как запрос выполняется в реальном времени.





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

Исторические планы

Расчет планов выполнения обходится недешево, поэтому SQL Server кэширует планы выполнения для повторного использования. Эти кэшированные планы можно увидеть в динамическом представлении (DMV) sys.dm_exec_query_plan:

SELECT 

    *

FROM 

    sys.dm_exec_cached_plans      

    CROSS APPLY sys.dm_exec_query_plan(plan_handle)

    CROSS APPLY  sys.dm_exec_sql_text(plan_handle)

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

Если у вас включено хранилище запросов (Query Store) для вашей базы данных, вы можете также получит доступ к планам запросов, хранящихся в динамических представлениях Query Store, (или через его графический интерфейс):

SELECT 

    CAST(p.query_plan AS XML), 

    *

FROM 

    sys.query_store_query AS q

    INNER JOIN sys.query_store_plan AS p

        ON q.query_id = p.query_id

Заключение

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