Как читать план запроса ms sql

Основные операции плана выполнения SQL Server

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

Index Scan

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

RID Lookup

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

Key Lookup

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

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

Sort

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

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

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

Spool

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

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

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

Merge Join

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

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

Операция неприменима, если входные данные не отсортированы.

Nested Loops Join

Соединение вложенными циклами. Встречаются очень часто. Выполняют довольно эффективное соединение относительно небольших наборов данных.

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

Операция неприменима, если данные слишком велики для хранения в памяти.

Hash Match Join

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

Соединяемые наборы данных настолько велики, что они могут быть обработаны только с помощью Hash Match Join.
Наборы данных не упорядочены по столбцам соединения, и SQL Server думает, что вычисление хэшей и цикл по ним будет быстрей, чем сортировка данных.

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

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

Операторы Hash Match Join достаточно эффективны тогда, когда не сбрасывают данные в tempdb.

Parallelism

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

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

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

Stream Aggregate

Статистическое выражение потока. Группирует строки в один или несколько столбцов и вычисляет одно или несколько агрегатных выражений (пример: COUNT, MIN, MAX, SUM и AVG), возвращенных запросом. Выход этого оператора может быть использован последующими операторами запроса, возвращен клиенту или то и другое. Оператору Stream Aggregate необходимы входные данные, упорядоченные по группируемым столбцам. Оптимизатор использует перед этим оператором оператор Sort, если данные не были ранее отсортированы оператором Sort или используется упорядоченный поиск или просмотр в индексе.

Compute Scalar

Оператор Compute Scalar вычисляет выражение и выдает вычисляемую скалярную величину. Затем эту величину можно вернуть пользователю или сослаться на нее в каком-либо запросе, а также выполнить эти действия одновременно. Примерами одновременного использования этих возможностей являются предикаты фильтра или соединения. Всегда возвращает одну строку. Часто применяется для того, чтобы конвертировать результат Stream Aggregate в ожидаемый на выходе тип int (когда Stream Aggregate возвращает bigint в случае с COUNT, AVG при типах столбцов int).

Concatenation

Оператор просматривает несколько входов, возвращая каждую просмотренную строку. Используется в запросах с UNION ALL. Копирует строки из первого входного потока в выходной поток и повторяет эту операцию для каждого дополнительного входного потока.

Filter

Оператор просматривает входные данные и возвращает только те строки, которые удовлетворяют критерию фильтрации (предикату).

О, эти планы запросов

Нужно выбрать первых 100 записей People, отсортированных по Cites.

Засучив рукава, бодро пишем:

select top 100 p.Name, c.Name as City from People p
left join Cities c on c.Id=p.CityId
order by c.Name

При этом мы получим что-то вроде:

За… 6 секунд. (MS SQL 2008 R2, i5 / 4Gb)

Но как же так! Откуда 6 секунд?! Мы ведь знаем, что в первых 100 записях будет исключительно Алматы! Ведь записей – 10 миллионов, и значит на город приходится по 100 тыс. Даже если это и не так, мы ведь можем выбрать первый город в списке, и проверить, наберется ли у него хотя бы 100 жителей.

Почему SQL сервер, обладая статистикой, не делает так:

select * from People p
left join Cities c on c.Id=p.CityId
where p.CityId
in (select top 1 id from Cities order by Name)
order by c.[Name]

Данный запрос возвращает примерно 100 тыс. записей менее чем за секунду! Убедились, что есть искомые 100 записей и отдали их очень-очень быстро.

Однако MSSQL делает все по плану. А план у него, «чистый термояд» (с).

Вопрос к знатокам:
каким образом необходимо исправить SQL запрос или сделать какие-то действия над сервером, чтобы получить по первому запросу результат в 10 раз быстрее?

P.S.
CREATE TABLE [dbo].[People] (
[Id] uniqueidentifier NOT NULL,
[Name] nvarchar(50) NOT NULL,
[CityId] uniqueidentifier
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Cities] (
[Id] uniqueidentifier NOT NULL,
[Name] nvarchar(50) NOT NULL,
)
ON [PRIMARY]
GO

P.P.S
Откуда растут ноги:
Задача вполне реальная. Есть таблица с основной сущностью, от нее по принципу «звезда» отходит множество измерений. Пользователю нужно ее отобразить в гриде, предоставив сортировку по полям.
Начиная с некоторого размера основной таблицы сортировка сводится к тому, что выбирается окно с одинаковыми (крайними) значениями, (вроде «Алматы») но при этом система начинает жутко тормозить.
Хочется иметь ОДИН параметризированный запрос, который будет эффективно работать как с малым размером таблицы People так и с большим.

P.P.P.S
Интересно, что если бы City были бы NotNull и использовался InnerJoin то запрос выполняется мгновенно.
Интересно, что ДАЖЕ ЕСЛИ поле City было бы NotNull но использовался LeftJoin – то запрос тормозит.

В комментах идея: Сперва выбрать все InnerJoin а потом Union по Null значениям. Завтра проверю эту и остальные безумные идеи )

P.P.P.P.S Попробовал. Сработало!

WITH Help AS
(
select top 100 p.Name, c.Name as City from People p
INNER join Cities c on c.Id=p.CityId
order by c.Name ASC
UNION
select top 100 p.Name, NULL as City from People p
WHERE p.CityId IS NULL
)
SELECT TOP 100 * FROM help

Дает 150 миллисекунд при тех же условиях! Спасибо holem.

Открытый вебинар «Порядок выполнения запроса SELECT и план запроса в MS SQL Server»

Коллеги, в последний день января мы запускаем курс «MS SQL Server разработчик», в связи с чем у нас прошёл тематический открытый урок. На нём мы поговорили о том, как MS SQL Server выполняет запрос SELECT, обсудили, в каком порядке и что анализируется, а также немного погрузились в чтение плана запроса.

Преподаватель — Кристина Кучерова, архитектор модели данных в Сбербанке России.

Цели и маршрут вебинара

В начале вебинара были поставлены следующие цели:

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

Зачем нужен план запроса?

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

Как мы видим запрос SELECT?

Давайте посмотрим, как выглядит запрос SELECT:

SELECT [поле1], [поле2]…	Какие поля выбираем?
FROM [таблица]	Откуда?
WHERE [условия]	Где условия такие-то
GROUP BY [поле1]	Сгруппируй по полям
HAVING [условия]	Имеющим такие-то условия
ORDER BY [поле1]	Упорядочи (отсортируй)

Как понять, куда идти за данными?

Первое, что пытается понять сервер при поступлении запроса — куда идти за данными. На этот вопрос отвечает команда FROM, т. к. именно здесь у нас будет список таблиц (либо имя одной таблицы).

Для наглядности давайте представим, что наш сервер — это некий дворецкий, которому мы приказываем собрать нас в отпуск. Соответственно, дворецкий начинает думать, а в каком же шкафу лежат нужные вещи (в какой таблице нужно брать данные)? И чтобы наш дворецкий без затруднений выполнил свою задачу, мы используем FROM.

Как понять, какие данные брать?

Допустим, дворецкий нашёл нужный шкаф и открыл его. Но какие вещи брать? Может, мы едем на горнолыжный курорт? А может, на жаркий солнечный пляж? Чтобы наши вещи соответствовали погоде, нам пригодится команда WHERE, определяющая условия, то есть позволяющая отфильтровать данные. Если жарко, берём сланцы, майки и купальники, если холодно — варежки, вязаные носки, свитера)).

Следующий этап — вложить эти данные в группы, что происходит с помощью GROUP BY (майки отдельно, носки отдельно). По результатам группировки можно наложить ещё одно условие, используя HAVING (например, отсеиваем непарные вещи). В конечном итоге всё складываем с помощью ORDER BY, получая на выходе готовый чемодан вещей, а точнее — упорядоченный блок данных.

Кстати, тут есть нюанс, а заключается он в том, что есть разница, какие условия следует прописывать в WHERE, а какие в HAVING. Но об этом лучше посмотреть в видео.

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

Виды соединений в плане запроса

Существуют три соединения, которые вы можете встретить в плане запроса:

Nested Loop.
Merge join.
Hash join.

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

Допустим, нам нужно соединить данные из разных таблиц. Давайте представим эти таблицы в виде… небольшого количества конфет Skittles и полной упаковки M&M’s.

При соединении типа Nested Loop мы берём конфету Skittles, а потом достаём вслепую конфету из пакета M&M’s. Если нам не попадается конфета такого же цвета (это наше условие), мы достаём следующую, то есть происходит обычный перебор. В результате можно сказать, что соединение Nested Loop больше подходит для небольших объёмов данных. Очевидно, что если данных много, перебор — не самый оптимальный вариант.

Посмотрим, как это выглядит в SQL-панели:

--drop table skittles --drop table mms --запрос для окна слева create table mms (id int identity(1,1), color varchar(25), taste varchar(15)) insert into mms (color, taste) values ('yellow', 'chocolate') insert into mms (color, taste) values ('red', 'nuts') create clustered index IX_mms_color ON mms(color); create table skittles (id int identity(1,1), color varchar(25), taste varchar(15)) create index IX_skittles_id ON skittles(id); create clustered index IX_skittles_color ON skittles(color); insert into skittles (color, taste) values ('red', 'cherry') insert into skittles (color, taste) values ('blue', 'strange') insert into skittles (color, taste) values ('yellow', 'lemon') insert into skittles (color, taste) values ('green', 'apple') insert into skittles (color, taste) values ('orange', 'orange') --запрос для правого окна select mms.* from mms join skittles on mms.color = skittles.color select * from mms join skittles on mms.color = skittles.color

Соединение используется для больших объёмов данных. Когда у вас Merge join, у вас обе таблицы имеют индекс, по которому их можно соединить. В случае с конфетами – это как будто они у нас заранее разложены по цветам.

Выглядит всё следующим образом:

--2 tables 50000 rows, only clustered index by color, color is not unique select COUNT(*) from mms_big join skittles_big on mms_big.color = skittles_big.color

Merge join хорош в следующих случаях:

большие наборы данных;
одинаковые поля соединения одного типа;
по полям соединения есть индексы.

Hash join используется при неотсортированных больших объёмах данных. Для соединения таблиц в данном случае нужно построить что-то, имитирующее индекс.

Пример соединения Hash join:

--drop table skittles --drop table mms --запрос для окна слева create table mms (id int identity(1,1), color varchar(25), taste varchar(15)) insert into mms (color, taste) values ('yellow', 'chocolate') insert into mms (color, taste) values ('red', 'nuts') insert into mms (color, taste) values ('blue', 'strange') insert into mms (color, taste) values ('green', 'chocolate') insert into mms (color, taste) values ('orange', 'chocolate') create table skittles (id int identity(1,1), color varchar(25), taste varchar(15)) insert into skittles (color, taste) values ('red', 'cherry') insert into skittles (color, taste) values ('blue', 'strange') insert into skittles (color, taste) values ('yellow', 'lemon') insert into skittles (color, taste) values ('green', 'apple') insert into skittles (color, taste) values ('orange', 'orange') --запрос для правого окна select * from mms join skittles on mms.color = skittles.color

Для наглядности вспомним наши конфеты:

Применение Hash join предполагает 2 фазы действий:

Build – строится хэш-таблица по наименьшей таблице. Для каждого значения в таблице № 1 считается хэш. Сохраняется значение в хэш-таблицу, а высчитанный хэш используется как ключ.
Probe. Для каждой строки из таблицы № 2 считается значение хэш по полям, которые указаны в join (оператор =). Ищется хэш в хэш-таблице, проверяются значения полей.

Когда хорош Hash join:

большой набор данных;
нет индексов на полях.

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

P. S. Преподаватель Кристина Кучерова выражает признательность Jes Schultz Borland за её презентацию с PASS Summitt Execution Plans: The Secret to Query Tuning Success, которая была использована при подготовке открытого урока.

Блог компании OTUS
SQL
Microsoft SQL Server

MS SQL Server: изучаем планы запросов

Многие знают, что для ускорения работы запроса нужно «изучить план». При этом сам план обычно обескураживает: куча разноцветных иконок и стрелочек; ничего не понятно, но очень интересно! Аналитик производительности Александр Денисов на конференции Infostart Event 2021 Moscow Premiere рассказал, как выполняется план запроса и что нужно сделать, чтобы с его помощью находить проблемы производительности.

Когда заходит речь про оптимизацию тяжелых запросов, разработчик рано или поздно приходит к мысли: «Хорошо, у меня есть план запроса. Это – красивая картинка с кучей каких-то непонятных символов, объектов. И что с этим делать дальше?». Вроде мы много раз слышали, что именно в плане запроса скрыты все нюансы поведения системы, и изучив план, можно разобраться, в чем именно заключается проблема запроса. Но как к нему подступиться?

Как выполняется план

Прежде чем мы поговорим о том, куда смотреть в плане и как его анализировать, давайте разберемся, что такое план в принципе, и как он выполняется. Обычный план на MS SQL выглядит примерно так, как на слайде. Это большое дерево, на котором много объектов. Это дерево — инструкция, что нужно сделать, чтобы получить результат запроса. Операторы в плане вызываются по порядку слева направо (с левого верхнего угла), а данные в плане перемещаются справа налево. Сейчас подробно разберём, как это работает. Каждый блок на слайде – это оператор, который что-то умеет. У каждого оператора есть несколько методов, но самый главный из них – Next(), который возвращает одну строку. Следующий оператор его спрашивает: «Дай строку» и тот отдает. Если надо еще, то снова: «Дай строку» и т.д. И так по одной строке любой оператор выдает вот эти строки. Например, Index Scan отдал строку оператору Sort. Потом Nested Loops спрашивает у оператора Sort: «Дай строку» – тот ему строку отдал. Потом уже у Nested Loops спрашивают: «Дай строку». Тот говорит, что не может, так как у него в этом плане есть еще одно поддерево, нужно его сначала выполнить, соединить результаты выполнения. Только после этого он сможет отдать строку. И в этом поддереве повторяется то же самое: по цепочке с правого угла эти строки выдаются. И после того, как мы получили все строки в Nested Loops, передаем из дальше в Top и выполняем. Важно понимать, что план выполняется справа налево, и именно так движутся данные в вашем плане. Зная это правило выполнения, вы понимаете, куда смотреть. Конечно, нужно еще учитывать, что там есть какие-нибудь блокирующие операторы, есть какие-то операторы, которые что-то могут тормозить и т.д. Если нет опыта изучения планов, это может быть иногда обескураживающе. Не очень понятно, как действительно выполнятся операции, и где находится какое-то узкое место.

Читать:

Как сделать отступы в таблице html

Живой план выполнения

На этапе обучения важно понять, как выполняются планы и в каком порядке все работает. К счастью, начиная с 16-й версии MS SQL (и нужна еще Management Studio 18) появилась такая штука, как живой план выполнения. Вы можете поместить план запроса в Management Studio, нажать «Получить живой план выполнения», запустить выполнение запроса, и план просто оживает. В этом представлении (в живом плане выполнения) видно, как считается статистика: собирается время выполнения, меняется количество строк, которое выбирается из плана по каждому оператору. Можно посмотреть, как идут данные и в каком порядке. Здесь процесс выполнения запроса показан в режиме ускоренного воспроизведения – я специально подобрал тяжелый запрос, который будет выполняться очень долго. Обратите внимание: есть поток данных от Index Scan до сортировки. И дальше данные никуда пока не идут, потому что в данном случае сортировка – это блокирующий оператор. До сортировки движение есть, а дальше – никакого движения нет. Это то, что вы можете наглядно в живом плане увидеть. Почему так происходит? Потому что сортировке сначала нужно получить весь объем данных, который она собирается сортировать, и пока она его не прочитает и не отсортирует – она не может продолжать выдавать данные дальше. Все ждут, когда сортировка получит эти данные. И только после этого весь остальной план оживает. Еще раз повторюсь, живой план выполнения – это неоценимая вещь, чтобы понимать, что от чего зависит, как движутся данные и т.д. Когда я разбираю планы и пытаюсь найти какие-то проблемы, для меня важно следить за потоком данных. Это как текущая река. В идеальном плане у вас от начала и до конца этот поток реки должен быть непрерывным и ровным. Как только вы видите какое-то нарушение потока (как Sort на примере), скорее всего именно в этом месте у вас и находится проблема, которая мешает быстро выполняться вашему плану. Именно сюда стоит смотреть, именно это стоит изучить, и здесь вы, скорее всего, и найдете проблему.

Виды планов

Давайте разберемся, какие планы бывают в MS SQL, и чем они друг от друга отличаются. Первый вид плана самый простой и самый доступный – Estimated Plan. Это то, что вы можете получить, не выполняя запроса. Даже более того: этот план создается перед выполнением запроса. По-английски он звучит, как «предполагаемый», «предположительный» план. Но более правильный перевод тут будет — “предварительный”. На самом деле, это тот же самый план с тем же порядком операторов, который будет выполняться дальше. Единственное, чего в нем нет – это статистики о времени выполнения. Estimated Plan знает, что мы сначала прочитаем из индекса, потом сделаем соединение. Он предполагает по статистике, сколько мы прочитаем из индекса, но точной информации в нем пока нет. Но это тот же самый план с тем же порядком операторов, который будет выполняться дальше. Live Plan – это то, что вы видели на моих слайдах ранее. Это тот же Estimated Plan, который ожил. В нем мы видим живую статистику выполнения в реальный момент времени. Его удобнее всего наблюдать в Management Studio, хотя есть специальные вьюхи, из которых вы тоже можете вытащить эту информацию и изучать ее сторонними средствами. Actual Plan – это самая полезная вещь, самая бесценная, но и самая дорогая. Мы не можем себе позволить получать Actual Plan для каждого запроса (это слишком медленно и очень сильно просаживает вашу производительность). Но именно в Actual plan у вас будет статистика по времени выполнения, вы будете знать реальные цифры: сколько времени прошло, сколько и чего было прочитано и т.д. Если мы посмотрим на Estimated Plan, у нас будет просто порядок операций и внизу будут оценки, на которые обычно очень любят ориентироваться. Не рассчитывайте на корректность этих оценок ни в коем случае! Если у вас более-менее сложный план (от десятка операторов), то скорее всего сумма этих процентов будет больше 100%. Во-первых, это само по себе смешно, а во-вторых, все эти оценки предварительные, они делаются на основе статистик. Если у вас статистики плохие, то и оценки тоже будут плохие, поэтому на них смотреть бессмысленно. В Live Plan предыдущие оценки пропали, но появились другие (на слайде подсвечены желтым). Первая строчка – это время выполнения на текущий момент. Что важно: обратите внимание, что под каждым оператором сейчас время выполнения одинаковое. В Live Plan у вас счетчик времени выполнения для каждого оператора считается синхронно. Две следующие строки – это оценка ошибки. Показывается, сколько строк прочитано и сколько ожидалось прочитать.

Например, у оператора Sort сверху мы видим недооценку – 26 из 36. По статистике ожидали прочитать 36 строк, но пока прочитали 26. Либо это ошибка оптимизатора, либо мы просто пока не успели прочитать все, что хотели. Это же Live Plan, он продолжает выполняться. Возможно, сейчас время пройдет, и он дочитает все, что ему было нужно.
У оператора Nested Loops внизу та же самая недооценка: 142 из 1614, разрыв еще сильнее.
А у других операторов внизу мы видим 80 из 80 – сколько ожидалось, столько и прочитал.

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

Ну и Actual Plan – это то, что мы можем увидеть на плане без какого-либо детального изучения.

Здесь также отображается статистика, которая была в Estimated Plan. Продолжаем ее игнорировать, потому что смысла в ней никакого нет.
Время выполнения: то время, которое в Live Plan тикало для каждого оператора одинаково и синхронно, здесь превратилось в накопительное время. Соответственно каждый оператор знает сколько времени у него потратилось.
И осталась статистика по эффективности, по тому, сколько мы ожидали и сколько мы прочитали. Это очень важно именно в контексте Actual Plan, потому что Actual Plan завершился. Мы больше ничего не ждем: все, что могло быть прочитано – прочитано. Поэтому смотрим и изучаем.

Что можно увидеть в плане

Расскажу, какую дополнительную информацию можно увидеть в планах.

Во-первых, как я уже ранее говорил – в Actual Plan можно увидеть накопительное время выполнения.

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

Как на слайде: у нас есть Table Spool, на который ушло полчаса, предыдущая операция была выполнена за доли секунды, стало быть, все полчаса у нас ушли на формирование Table Spool.

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

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

Еще из интересного у нас есть условия поиска в индексе.

По каждому оператору доступа данных (Seek, Scan – что бы это ни было) мы в секции Seek Predicates можем увидеть, по каким условиям выполнялся поиск.

Это тоже может быть интересно. Например, бывает так, что поиск разваливается на два оператора – сначала мы ищем по некластерному индексу, потом по кластерному и т.д.

Когда понятно, что именно мы достаем из этого индекса, мы можем понять, как это улучшить.

Если мы откроем план в виде .xml (по правой кнопке в Management Studio – Show xml), в полученном тексте мы тоже увидим много интересных вещей.

Во-первых, увидим информацию о статистиках, которые использовались при подготовке этого плана. Вы можете посмотреть, какие статистики были и, самое главное, в каком они были состоянии. Для этого не нужно ждать выполнения запроса и получать Actual Plan: эта информация доступна в Estimated Plan.

ModificationCount – это сколько изменений в статистике накопилось с момента пересчета. Если это значение зашкаливает, то пересчитываем статистики и, теоретически, план должен нормализироваться. И наоборот: если здесь все хорошо, то скорее всего что-то не так с запросом, и мы его как-то плохо написали.

Еще одна крутая штука, которая появилась в MS SQL Server 2016 – это статистика по ожиданиям. Она доступна в Actual Plan.

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

Все эти типы ожиданий – стандартные системные, они очень легко гуглятся. На сайте Microsoft приведен полный список всех типов ожиданий, там все это подробно объяснено.

Если вы поймали Actual Plan по какому-то тяжелому запросу, статистика по ожиданиям тоже может многое вам объяснить.

И последний пример, который я привожу (но не последнее, что можно там найти) – это метаинформация о компиляции плана.

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

Вверху очень интересное сообщение, почему закончилась оптимизация плана: из-за TimeOut.

Если вы такое видите, скорее всего, у вас слишком сложный план, очень большое дерево перебора вариантов и SQL-сервер просто не успевает перебрать все предоставленные варианты. В какой-то момент он останавливается, говорит: «Все! Хватит! Timeout! Берем то, что есть!» Это обычный знак того, что вы работаете не самым лучшим планом, могло бы быть и лучше, если бы вы упростили свой запрос.

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

Как искать проблемы в плане

Ну и самое интересное: как искать проблемы в плане?

Я сейчас дам несколько примеров. Важно, что это не какой-то исчерпывающий список, это просто несколько примеров, от которых можно оттолкнуться.

Ранее я уже говорил, что в Actual Plan у нас есть данные о примерном числе строк – сколько мы будем читать, и о том, сколько мы реально прочитали.

Это очень простая, но в то же время эффективная техника: берем и сравниваем, сколько мы ожидали и сколько мы прочитали.

Если у вас такой же разрыв, что ожидали 10 строк, а прочитали 4,5 миллиона – первая и наиболее вероятная причина, что у вас просто протухла статистика. Из-за неправильной статистики MS SQL Server неправильно оценил число строк в вашей операции и построил неправильный план.

Обратите внимание, как изначально выглядит этот план – у нас Scan, потом какая-то сортировка, а потом – Nested Loops.

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

Самое важное – теперь вместо Nested Loops у нас Merge Join. Это совершенно другой оператор.

Nested Loops очень хорошо работает, когда у вас мало строк на входе (как ожидалось 10 строк), а когда MS SQL понял, что у вас будет много строк, он сразу отменил Nested Loops и поставил вместо него Merge Join.

в первом исходном варианте у оператора Top время выполнения 19 минут;
когда мы пересчитали нашу статистику, время выполнения стало 9 минут.

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

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

Второй пример: ищем перепады в числе строк.

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

У нас есть справочник «Контрагенты», мы первые 25 строк выбираем, упорядочиваем по полю ОКВЭД (допустим, бухгалтеру так захотелось). Ожидаем, что у нас все будет достаточно быстро: всего-то надо забрать какие-то 25 строчек из таблицы.

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

Помните, я говорил, что данные можно представить, как реку или какой-то поток и что важно следить за порогами и водоворотами?

Здесь та ситуация, когда у нас возник порог – на вход на сортировку приходит много данных (широкая стрелка), а после сортировки очень мало.

Если вспомнить запрос, становится понятно, в чем дело – у нас просто нет индекса для сортировки по полю ОКВЭД. Соответственно, чтобы достать первые 25 строк, приходится прочитать вообще всю таблицу и отсортировать ее. Если таблица большая, то, возможно, что-то даже в tempdb выгрузится. И только после того как сортировка будет готова, мы вытаскиваем вот эти первые 25 строк.

Лечение очень простое: если мы понимаем, что по нашему полю упорядочивания нет индекса, просто добавляем индекс в конфигураторе.

План преобразуется – обратите внимание, что стрелки теперь сбалансированы, больше нет перепада. План поменялся, мы читаем те 25 строк, которые были нужны, и перепада в объемах данных больше нет. План преобразился и стал эффективным.

И последний пример: проверяем «попадание» условий в наши индексы.

Здесь запрос немного интереснее: мы соединяем контрагентов самих с собой, отбираем их по КПП, по наименованию группы и соединяем по родителю.

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

И план здесь тоже достаточно обычный – все сбалансировано, каких-то перепадов по стрелкам у нас нет. Все более-менее хорошо.

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

Здесь у оператора Index Seek условия делятся на две группы: Seek Predicate и Predicate. И то и другое – это условия отбора, и то и другое – фильтруется, но в разных ситуациях.

Seek Predicate – это то, что мы фильтруем и проверяем прямо на этапе чтения индекса с жесткого диска или из памяти.
Predicate – это то, что мы потом дофильтровываем. Если кто-то помнит, когда интернет был маленьким, у «Яндекс» была кнопка «Искать в найденном». Естественно, это гораздо хуже и медленнее, чем искать сразу то, что тебе нужно. И вот Predicate – это то самое «Искать в найденном». Если вы видите Predicate, вы понимаете, что условие у вас работает по вашему индексу, но оно работает недостаточно эффективно. Лучше бы он сразу искал в Seek Predicate в тот момент, когда он читает эти данные.

Разбираемся дальше, почему так происходит.

Смотрим состав индекса, из которого мы читаем. И видим вот такую картинку:

Зеленые – это те поля, которые мы вытаскиваем в рамках нашего запроса.
Желтое – это то поле, которого в запросе нет.

Все, что левее желтого поля Folder, попало в Seek Predicate. И СУБД шла по ключу индекса, искала по полям, по каким могла, а потом споткнулась о Folder, потому что Folder в условиях запроса не было, она не знает, какой Folder должен быть.

MS SQL споткнулся об этот Folder и все, что после него, ушло дальше в условия Predicate.

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

Решение тоже достаточно простое – мы просто берем и добавляем дополнительное условие «ЭтоГруппа = ЛОЖЬ», чтобы полностью попадать в индекс.

После этого условия оператора Index Seek преображаются – то, что было в Predicate, уходит в Seek Predicate. Мы максимально эффективно ищем наши данные, и все счастливы.

Это важно, потому что таким образом мы:

во-первых, экономим чтение и не засоряем память лишними страницами, которые мы бы отсекли на этапе Predicate;
во-вторых, мы экономим время, потому что происходит оптимизация, запрос работает быстрее.

Что почитать по теме

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

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

Во-первых, фундаментальная статья Remus Rusanu о том, как вообще работает SQL Server и как выполняются запросы. Статья очень большая, в ней есть всего понемногу, но качественно и по делу. Я настолько восхитился, что подготовил перевод этой статьи.
Во-вторых, это блог Дмитрия Пилюгина. Это человек, который, как мне кажется, больше всех в мире знает, как работает оптимизатор запросов в MS SQL. Это признанный специалист, у которого безумно интересный блог. Пишет он очень хорошо и разбирает какие-то мельчайшие детали и нюансы. Если хочется куда-то глубоко погрузиться, то это – как раз то самое место, которое стоит изучить.
И последняя ссылка – это инструмент Microsoft, который показывает типичные ошибки в плане запроса. Он встроен в Management Studio начиная с версии 17.4. Похож на SonarQube для планов запросов. Это – статический анализ, там какие-то очень простые примеры. Но, чтобы набить руку и примерно представлять, как это работает и на что обращать внимание, можно посмотреть и этот источник. Сложные вещи он не выловит, но хотя бы какую-то базу вам даст.
И еще одна вещь, которую я забыл указать на слайде: есть очень старая книжка Дена Тоу «Настройка SQL. Для профессионалов». В бумажном виде вы ее никогда и нигде уже не найдете, потому что она была издана в 2004 году, и ее уже раскупили. Но в электронном виде где-то можно найти. Книжка старая, но до сих пор актуальная. Она описывает фундаментальный низовой уровень оптимизации запросов. Там примеры для четырех СУБД. Есть ощущение, что в фирме «1С» читали эту книжку, потому что там примеры как раз на SQL Server, PostgreSQL, Oracle и DB2 – все, что в 1С поддерживается. Если вы эту книжку достанете, прочитайте обязательно – она очень хорошо поправляет голову в плане оптимизации.

Статья написана по итогам доклада (видео), прочитанного на конференции Infostart Event 2021 Moscow Premiere.

30 мая — 1 июня 2024 года состоится конференция Анализ & Управление в ИТ-проектах, на которой прозвучит 130+ докладов.

Темы конференции:

Программная инженерия.
Инструментарий аналитика.
Решения 1С: архитектура, учет и кейсы автоматизации на 1С.
Управление проектом.
Управление продуктом.
Soft skills, управление командой проекта.

Конференция для аналитиков и руководителей проектов, а также других специалистов из мира 1С, которые занимаются системным и бизнес-анализом, работают с требованиями, управляют проектами и продуктами!