3.14.Планы запросов. Получение плана запроса в профайлере SQL. Операторы плана, наиболее важные для нас
Получение плана запроса в профайлере SQL
План выполнения запроса – это последовательность операций, необходимых для получения результата запроса в СУБД. По фактическому плану можно понять, какой именно алгоритм СУБД выбрала для решения поставленной перед ней задачи, какие операторы решила использовать. Проанализировав, как выполнился каждый из операторов, можно определить, не ошиблась ли СУБД в этом выборе.
Чтобы получить текст запроса так, как его получает SQL Server, и увидеть план запроса, нужно сделать следующее:
1. Запустить SQL Server Profiler.
2. Создать трассировку. Можно использовать стандартный шаблон.
3. В свойствах трассировки:
- Убедиться, что в нее входят события SQL:BatchStarted, SQL:BatchCompleted, RPC:Completed.
- Установить галочку «все события».
- Добавить события Showplan Statistics Profile и Showplan XML Statistics Profile из узла Performance.
- Снять галочку «все события», останутся только выбранные.
- Установить галочку «все столбцы».
- Добавить столбец «DatabaseName».
- Зайти в «фильтры столбцов», поставить фильтр «DatabaseName» «Похоже на (Like)» <написать_имя_своей_базы>.
- Убедиться, что у события Showplan Statistics Profile включен столбец BinaryData.
- Снять галочку «все столбцы», останутся только выбранные.
- В итоге настройка трассировки как минимум должна содержать все поля, показанные на рис. 3.14.1.
4. Если запрос выполняется из «1С», в конфигураторе «1С» установить один останов на отладчике на строке интересующего нас запроса, и второй – сразу после него. Дойти до первого останова.
5. Запустить трассировку.
6. Позволить приложению выполнить запрос (если запрос выполняется из «1С», это значит продолжить отладку и дойти до следующего останова на отладчике; если запрос выполняется из SQL Management Studio, это значит запустить его).
7. Остановить трассировку в приложении SQL Server Profiler («Паузой», если нужно, чтобы при следующем выполнении трассировки запроса строки, полученные при нынешней трассировке, остались; «Стопом», если нужно, чтобы они очистились).
8. Найти свой запрос в трассировке. Обычно ориентируются по наибольшему значению в поле Duration для событий SQL:BatchCompleted или RPC:Completed, потому что есть основания считать, что именно исследуемый запрос имеет наибольшую длительность. События Showplan Statistics Profile и Showplan XML Statistics Profile, относящиеся к исследуемому запросу, находятся непосредственно над ними.
Выбрать событие SQL:BatchStarted, SQL:BatchCompleted или RPC:Completed, просмотреть текст запроса так, как его получает SQL Server.
9. Выбрать событие Showplan Statistics Profile, просмотреть план запроса, обратить внимание на используемые операторы и столбцы Rows (количество строк) и Executes (количество выполнений) для них.
10. Выбрать событие Showplan XML Statistics Profile, просмотреть графический план запроса с помощью всплывающей подсказки (подробности по ней будут ниже).
11. При необходимости сохранить трассировку.
12. По совокупности полученных показателей сделать вывод об оптимальности выбранного плана.
Рис. 3.14.1. Настройка трассировки для получения планов запроса
Проиллюстрируем это на примерах. Возьмем демобазу «Бухгалтерии предприятия», редакция 3.0 (3.0.19.15).
Пример 1
Выполним из SQL Management Studio следующий запрос, включив трассировку профайлером (_AccRg7172 – это основная таблица регистра бухгалтерии «Хозрасчетный»).
SELECT * FROM _AccRg7172
План запроса, полученный через Showplan Statistics Profile, будет следующим:
Rows Executes StmtText
---- -------- --------
1181 1 Clustered Index Scan (OBJECT:([unt3].[dbo].[_AccRg7172].[_AccRg7172_ByPeriod_TRN]))
Пример 2
Теперь выполним, казалось бы, точно такой же запрос, но уже средствами «1С»:
ВЫБРАТЬ * ИЗ РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
План запроса, полученный через Showplan Statistics Profile, будет следующим:
Rows Executes StmtText
---- -------- --------
1181 1 Clustered Index Seek (OBJECT:([unt3].[dbo].[_AccRg7172].[_AccRg7172_ByPeriod_TRN] AS [T1]), SEEK:([T1].[_Fld10847]=[@P1]) ORDERED FORWARD)
Сразу видим, что в плане используется другой оператор. Посмотрим на текст запроса, как он попадает в RPC:Completed.
exec sp_executesql N'SELECT
T1._Period,
T1._RecorderTRef,
T1._RecorderRRef,
T1._LineNo,
T1._Active,
T1._AccountDtRRef,
T1._AccountCtRRef,
T1._Fld7173RRef,
T1._Fld7174DtRRef,
T1._Fld7174CtRRef,
T1._Fld7175DtRRef,
T1._Fld7175CtRRef,
T1._Fld7176,
T1._Fld7177Dt,
T1._Fld7177Ct,
T1._Fld7178Dt,
T1._Fld7178Ct,
T1._Fld7179Dt,
T1._Fld7179Ct,
T1._Fld7180Dt,
T1._Fld7180Ct,
T1._Fld7181Dt,
T1._Fld7181Ct,
T1._Fld7182,
T1._Fld7183
FROM _AccRg7172 T1 WITH(NOLOCK)
WHERE (T1._Fld10847 = @P1)',N'@P1 numeric(1)',0
То, что «*» заменена на перечень полей, это нормально. Но откуда взялось условие по полю _Fld10847? Понятно, что это из-за него мог быть выбран другой оператор.
Когда мы рассматривали структуру хранения базы, то обращали внимание, что через метод ПолучитьСтруктуруХраненияБазыДанных() можно получить не только имена таблиц, но и индексы (правда, как было показано, с некоторыми ограничениями), и имена полей. На рис. 3.14.2 показано, как это сделать для расшифровки полей.
Рис. 3.14.2. Получение структуры полей в терминах метаданных «1С»
Теперь, расшифровав имена полей таблицы _AccRg7172, получим, что поле _Fld10847 – это ОбщийРеквизит.ОбластьДанныхОсновныеДанные. Посмотрев конфигурацию, убеждаемся, что этот общий реквизит там действительно есть.
Мы таким образом встретились с работой механизма разделения данных там, где этого совсем не ждали. Нам это, впрочем, сейчас не мешает, и просто примем это как данность.
Посмотрим теперь на графический план запроса из примера 2, встав в трассировке на Showplan XML Statistics Profile. Наведя курсор на значок оператора, увидим всплывающую подсказку, как это показано на рис. 3.14.3, которая показывает детали для каждого из операторов.
Рис. 3.14.3. Графический план запроса и всплывающая подсказка
Что собой представляет каждая из строк всплывающей подсказки, описано в таблице 3.14.1. Порядок следования, состав и даже названия этих строк в действительности могут несколько отличаться от примеров, представленных в таблице 3.14.1, например, из-за того, что используется другая версия SQL Server.
Таблица 3.14.1. Дополнительная информация о плане запроса, выводимая в том числе в виде всплывающей подсказки
| Элемент всплывающей подсказки (англ. и рус.) | Описание |
|---|---|
| Physical Operation Физическая операция | Используемый оператор |
| Logical Operation Логическая операция | Логический оператор, который соответствует физическому оператору |
| Actual Rows Фактическое количество строк | Фактическое количество строк |
| Estimated I/O Cost Предполагаемая стоимость операций ввода-вывода | Приблизительные затраты на выполнение действий ввода-вывода для данной операции. С точки зрения СУБД это значение должно быть как можно меньше |
| Estimated CPU Cost Предполагаемая стоимость процессного ресурса | Приблизительные затраты на всю работу процессора (CPU) для данной операции |
| Estimated Operator Cost Предполагаемая стоимость оператора | Затраты оптимизатора запросов на выполнение этой операции. Затраты на выполнение этой операции в процентном отношении к общим затратам на выполнение запроса отображаются в скобках. С точки зрения СУБД это значение должно быть как можно меньше |
| Estimated Subtree Cost Предполагаемая стоимость поддерева | Общие затраты оптимизатора запросов на выполнение этой и всех предшествующих операций в данном поддереве |
| Estimated Number of Rows Предполагаемое количество строк | Ожидаемое количество строк, выдаваемых оператором |
| Estimated Row Size Предполагаемый размер строки | Предполагаемый размер строки, получаемой на выходе оператора (в байтах) |
| ActualRebinds Фактическое число повторных привязок | Счетчики указывают на число инициализаций физического оператора (вызовов метода Init()). Физический оператор может получать много вызовов Init(), хотя обычно получает лишь один. Графический план отображает нули для действительных повторных привязок и сбросов на начало, когда те отсутствуют |
| ActualRewinds Фактическое число сбросов на начало | |
| Ordered Отсортировано | Отсортировано или нет |
| Node ID Идентификатор узла | Номер узла (оператора) в плане текущего запроса |
План запроса состоит из операторов. Фактически план запроса – это дерево физических операторов.
Операторы описывают, как SQL Server выполняет запрос. Оптимизатор запросов использует операторы для построения плана запроса, чтобы создать результат, заданный в запросе.
Операторы классифицируются как логические и физические.
Логические операторы описывают операции реляционной алгебры, используемые для обработки запроса: описывают концепцию, какие действия надо совершить.
Физические операторы реализуют действия, описанные логическими операторами. Каждый физический оператор является объектом или процедурой, выполняющей операцию. Физическим операторам соответствует их стоимость. Оптимизатор строит дерево логических операций, а затем, используя подход, основанный на стоимости, подбирает физические операторы в дерево плана. Обычно логическую операцию можно реализовать несколькими способами, используя разные физические операторы. Наоборот, один физический оператор очень редко может реализовывать несколько логических операций.
