В данной теме я хочу поговорить об очень полезном инструменте — SQL Server Profiler.
Как описано на MSDN, приложение SQL Server Profiler — это графический пользовательский интерфейс для трассировки SQL, с помощью которого можно наблюдать за экземпляром компонента Database Engine. Приложение позволяет собирать и сохранять данные о каждом событии в файле или в таблице для последующего анализа. Данное приложение представляет исключительную важность в задачах анализа производительности исполняемых запросов, а также при анализе проблем параллельности работы в базе данных.
На текущий момент Microsoft продвигает другой аналогичный инструмент — Extended Events и рекомендует пользоваться им, тем не менее я считаю полезным уметь работать и с инструментом Profiler.
Настройка приложения
В профайлере, начиная с версии 2005, в настройках приложения присутствует флажок «Показывать значения в столбце «Продолжительность» в микросекундах» (Show values in Duration column in microseconds). Данный флажок управляет как отображением значения в соответствующей колонке, так и значением, устанавливаемым для отбора по данной колонке. На мой взгляд, при работе с Profiler удобнее использовать микросекунды, поэтому советую данный флажок установить. Настройка находится в меню Сервис (Tools) → Параметры (Options).
Запуск трассировки в Profiler
Для того чтобы запустить новую трассировку в Profiler необходимо:
- Открыть приложение SQL Server Profiler
- Выбрать пункт основного меню «Файл» (File), в нем «Создать трассировку» (New Trace)
- В открывшемся диалоге подключиться к нужному экземпляру SQL Server
- В открывшемся окне настроить трассировку
- Запустить трассировку
Настройка трассировки
Из вышеприведенного списка действий, самым сложным (а по своей сути — единственным) является настройка трассировки. Она имеет множество вариантов, попробуем разобрать основные из них.
Вкладка общие
Первым пунктом предлагается задать имя трассировки, имя можно оставить по умолчанию, но если будет открыто несколько трассировок, удобно именовать их чем-то осознанным.
Следующим пунктом предлагается выбрать шаблон трассировки из списка. В данном списке приводятся некоторые предопределенные шаблоны трассировок. Помимо этого, шаблоны можно дополнить своими, пользовательскими шаблонами. Данная возможность облегчит вам жизнь, поскольку каждый раз настраивать с нуля — не самое приятное занятие.
Вывод данных трассировки может происходить:
- На экран в новом окне — вывод происходит на экран, при этом в дальнейшем трассировку можно будет сохранить как в файл, так и в таблицу в СУБД (даже если опции записи в файл и/или таблицу не были включены)
- Записывать в файл на диске (опционально) — дополнительно к выбранным опциям, данные будут записываться в файл на диске. Далее этот файл можно открыть через профайлер. Эта опция удобна для сохранения и/или для передачи трассировки.
- Записывать в таблицу базы данных (опционально) — дополнительно к выбранным опциям, данные будут записываться в таблицу базы данных. Далее, посредством возможностей предоставляемых СУБД, можно произвести анализ данных, например, найти самые длительные события или просуммировать общую длительность.
Последним пунктом настройки предлагается установить время остановки трассировки, если это требуется.
Перед продолжением настройки установим шаблон «Пустой» (Blank), имя трассировки может быть произвольным, все остальные флажки могут быть сняты.
Вкладка выбора событий
Событие — это действие экземпляра SQL Server Database Engine. Для анализа проблем, возникающих при работе с 1С, существуют определенные наборы событий, с которыми необходимо уметь работать.
Выбор событий — это основная часть настройки трассировки, он предполагает работу с матрицей: «Событие» — «Свойство события». Таким образом, в этой матрице надо установить флажки по тем событиям и их свойствам, которые мы хотим трассировать.
Помимо матрицы событий и их свойств, на форме присутствуют флажки: «Показать все события» (Show all events) и «Показать все столбцы» (Show all columns). При установленном флажке в матрице раскрываются все события/столбцы, при снятом остаются только выбранные. Помимо этого, флажок «Показать все столбцы» влияет на отображение данных в «Фильтры столбцов» — отображаемый список соответствует отображаемым столбцам в матрице. При этом, даже если столбец скрыт (не выбран в матрице и снят флаг «Показать все столбцы»), но отбор на него был установлен — отбор сработает.
«Фильтры столбцов» (Column Filters) — открывает список столбцов по которым можно установить отборы. Если значение события при трассировке не подходит под значение отбора в столбце, данное событие не будет отражено в трассировке. Таким образом, можно установить отбор на информационную базу, по которой необходимо произвести трассировку.
«Упорядочить столбцы» (Organize Columns) — используется для изменения (организации) порядка следования выводимых колонок.
События для получения плана выполнения запроса
Для того чтобы получить план запроса в Profiler следует добавить следующие события:
| Событие | Описание |
|---|---|
| Showplan All | Выводит подробную информацию о предполагаемом плане запроса в текстовом виде |
| Showplan Statistics Profile | Выводит подробную информацию о действительном плане запроса в текстовом виде |
| Showplan XML | Выводит подробную информацию о предполагаемом плане запроса в XML формате (может быть представлен графически) |
| Showplan XML Statistics Profile | Выводит подробную информацию о действительном плане запроса в XML формате (может быть представлен графически) |
Помимо вышеприведенных событий, для получения полной картины происходящего полезно добавить события:
| Событие | Описание |
|---|---|
| RPC:Completed | Происходит при завершении удаленного вызова процедуры |
| SQL:BatchCompleted | Возникает при завершении выполнения инструкции Transact-SQL |
Среди столбцов, выводимых в трассировке, рекомендуется включить: TextData, BinaryData, Reads, Writes, CPU, Duration, SPID.
Также полезно установить фильтры по длительности и базе данных. Как это сделать описано ниже в статье.
Другие способы получения плана запроса (без использования Profiler) описаны в статье «Методы получения плана запроса в СУБД MS SQL Server»
События для получения графа взаимоблокировки
Для получения графа взаимоблокировки достаточно добавить одноименное событие Locks: Deadlock graph.
Событие Deadlock graph возникает одновременно с классом событий Lock: Deadlock. Класс событий Deadlock graph предоставляет XML-описание взаимоблокировки.
Среди столбцов, выводимых в трассировке, рекомендуется включить: EventSequence, SPID, StartTime, TextData.
События для получения информации об эскалации
Для получения информации об эскалации достаточно добавить событие Locks: Escalation.
Событие Escalation возникает при эскалации блокировки, т.е. когда блокировка более мелких фрагментов преобразуется в блокировку более крупных фрагментов.
Также можно ограничить набор выводимых колонок теми данными, которые требуются для анализа.
Установка фильтров столбцов
Установить фильтры можно нажав на кнопку «Фильтры столбцов».
Важно понимать, не все события содержат те или иные колонки. Если событие не содержит колонку по которой установлен фильтр, данное событие отфильтровано не будет.
Очень полезным фильтром является отбор по имени базы или ее идентификатору (если в экземпляре находится несколько баз, а трассировать необходимо какую-то определенную). Для установки фильтра по имени базы необходимо для колонки DatabaseName установить значение «Похоже на» или «Не похоже на». Стоит отметить: если установленному значению будут отвечать несколько баз, тогда события будут собираться по каждой из них. Второй вариант фильтрации событий по определенной базе — установка отбора по колонке DatabaseID. Узнать идентификатор базы данных можно выполнив запрос в SQL Server Management Studio:
|
1 |
SELECT DB_ID('MyBase') |
где MyBase — имя базы, для которой необходимо получить идентификатор.
Еще одним одним полезным фильтром является отбор по SPID (идентификатору серверного процесса). Он помогает произвести трассировку событий по определенному пользователю в то время когда в базе работают другие пользователи. Получить SPID пользователя, работающего в 1С, можно при помощи консоли кластера серверов. Получение данной информации в этой статье не рассматривается.
Обычно задача получения плана выполнения запроса связана с тем что запрос выполняется достаточно долго и для его оптимизации необходимо разобраться с планом выполнения. При этом настройка для получения плана выполнения собирает большое количество коротких по длительности событий. И именно для того чтобы отсечь ненужные короткие события можно применить отбор по длительности. Для установки фильтра по длительности необходимо установить значение отбора для колонки Duration. Отмечу что значение используемое для отбора может быть выражено в миллисекундах или микросекундах. То, какое значение используется, устанавливается в «Общих параметрах» профайлера (см. раздел «Настройка приложения»).
Работа с трассировкой
После запуска, в окно трассировки начнут выводиться события.
Если трассировка запущена, ее можно приостановить (дальнейшее возобновление трассировки оставит в окне накопленные события) или остановить (после возобновления накопленные события будут очищены).
Если трассировка приостановлена или остановлена, тогда ее можно возобновить.
Помимо этого, в любой момент существует возможность очистить окно трассировки.
Для всех вышеприведенных действий на панели инструментов присутствуют необходимы кнопки.
Для изменения настройки уже созданной трассировки необходимо ее приостановить (или остановить) и перейти в пункт меню Файл (File) → Свойства (Properties). Выполнив изменения в трассировке, ее можно запустить вновь.
Работа с шаблонами трассировки
Наличие готовых шаблонов трассировки экономит время на настройке новой трассировки, поэтому я рекомендую сохранять необходимые для работы шаблоны. Шаблоны можно создавать, изменять, экспортировать и импортировать; для данных действий предназначен раздел меню «Файл» (File) → «Шаблоны» (Templates).
Создание шаблона
Для создания шаблона трассировки можно воспользоваться пунктом меню «Файл» (File) → «Шаблоны» (Templates) → «Новый шаблон» (New template).
Вторым (и на мой взгляд наиболее удобным) вариантом создания шаблона является сохранение настройки текущей трассировки в виде шаблона. Для этого требуется воспользоваться пунктом меню «Файл» (File) → «Сохранить как» (Save as) → «Шаблон трассировки» (Trace template)
Сохранение трассировки
Как было сказано ранее, трассировка может быть сохранена в файл на диске и/или в таблицу базы данных. Эти действия можно произвести даже если при настройке трассировки не были установлены соответствующие флажки.
Для того чтобы сохранить трассировку в файл, в меню присутствует пункт «Файл» (File) → «Сохранить как» (Save as) → «Файл трассировки» (Trace File). Для сохранения трассировки в таблицу существует аналогичный пункт меню: «Файл» (File) → «Сохранить как» (Save as) → «Таблица трассировки» (Trace Table)