Анализ запросов с помощью SQL Profiler

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

Что такое SQL Profiler и зачем оно вообще нужно

SQLProfilerэто программа поставляемая вместе с MS SQL Server и предназначена она для и просмотра всех событий, которые происходят в SQL сервер или говоря другими словами для записи трассировки. Зачем SQLProfiler может понадобиться программисту 1С? Хотя бы для того, что бы получить текст запроса на языке SQL и посмотреть его план. Конечно, это можно сделать и с помощью технологического журнала, но это требует некоторых навыков, да и план в ТЖ получается не такой красивый и удобочитаемый. В профайлере можно посмотреть не только текстовый, но и графический план выполнения запроса, что на  мой взгляд, гораздо удобнее. Так же с помощью профайлера можно определить: запросы длиннее определенного времени запросы к определенной таблице ожидания на блокировках таймауты взаимоблокировки и многое другое…  

Анализ запросов с помощью SQL Profiler

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

1. Запускаем SQL Profiler Пуск — Все программы — Microsoft SQL Server 2008 R2 — Средства обеспечения производительности — SQLProfiler
2. Создаем новую трассировку Файл – Создать трассировку (Ctrl+N)
3. Указываем сервер СУБД на котором находится наша база данных и нажимаем «Соединить».
  

Естественно, ничего не мешает выполнять трассировку сервера СУБД, который находится на другом компьютере. 4. В появившемся окне «Свойства трассировки» переходим на вторую закладку «Выбор событий»

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

Описание событий: ShowplanStatisticsProfile– текстовый план выполнения запроса.
ShowplanXMLStatisticsProfile– графический план выполнения запроса.
RPC:Completed– текст запроса, если он выполняется как процедура (если выполняется запрос 1С с параметрами).
SQL:BatchCompleted– текст запроса, если он выполняется как обычный запрос (если выполнялся запрос 1С без параметров).

6. Теперь необходимо настроить фильтр для событий. Если этого не сделать, то мы будем видеть запросы для всех баз данных расположенных на данном сервере СУБД. Нажимаем кнопку «Фильтры столбцов» и указываем фильтр по имени базы данных

Теперь мы будем видеть в трассировке только запросы к базе данных «TestBase_8_2» При желании можно поставить фильтр и по другим полям, наиболее интересные из них: Duration(Длительность), TextData(обычно это текст запроса) и RowCounts (количество строк возвращаемых запросом).

Например, если мне нужно отловить все запросы к таблице «_InfoRg4312» длительностью более 3-х секунд в базе данных «TestBase_8_2», то я делаю:
 a) Фильтр по базе данных, пример показан выше
 b) Фильтр по длительности в миллисекундах.
 

 c) Фильтр по тексту запроса
 
Здесь мы указываем маску. Если необходимо отслеживать запросы, которые обращаются к нескольким таблицам, то создаем несколько элементов в разделе «Похоже на». Условия всех фильтров работают вместе.  

7. Теперь можно запускать трассировку. Нажимаем «Запустить», после этого трассировка начинает работу, и вы можете видеть те события, отображение которых вы настроили и которые попадают под ваши фильтры. Для управления трассировкой можно использовать кнопки на командной панели.

Слева на право: Ластик – очищает окно трассировки, Пуск – запускает трассировку, Пауза – ставит трассировку на паузу, при нажатии на Пуск трассировка возобновляется, Стоп – останавливает трассировку

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

9. Выполним запрос в консоли запросов 1С и посмотрим как он отразится в профайлере.  

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

10. По свойствам событий можно понять: сколько секунд выполнялся запрос (Duration), сколько было логических чтений (Reads), сколько строк запрос вернул в результате (RowCounts) и т.д. В моем случае запрос выполнялся 2 миллисекунды, сделал 4 логических чтения и вернул 1 строку.

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

Используя контекстное меню, графический план можно сохранить в отдельный файл с расширением *.SQLPlan и открыть его в профайлере на другом компьютере или с помощью более продвинутой программы SQL Sentry Plan Explorer.

12. Если подняться еще выше, то мы увидим тот же план запроса, но уже в текстовом виде. Именно этот план отображается в ТЖ, ЦУП и прочих средствах контроля производительности 1С. Для его анализа рекомендую использовать продвинутый текстовый редактор с подсветкой, например Notepad++.

13. Использую меню «Файл-Сохранить как», всю трассировку можно сохранить в различные форматы:
a) В формат самого профайлера, т.е. с расширением *.trc
b) В формат xml
c) Можно сделать из трассировки шаблон. См. следующий пункт.
d) Можно сохранить трассировку в виде таблицы базы данных. Удобный способ, если нам нужно найти например самый медленный запрос во всей трассировке либо отобрать запросы по какому-либо параметру. Файл – Сохранить как – Таблица трассировки – Выбираем сервер СУБД и подключаемся к нему Далее нужно выбрать базу данных на указанном сервере и указать имя таблицы, куда будет сохранена трассировка. Можно выбрать уже существующую таблицу, либо написать новое имя и тогда таблица будет создана автоматически в выбранной базе данных.
 

Далее вы можете строить запросы любой сложности к этой таблице, например искать наиболее долгие запросы.

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

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

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

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

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

Источник: http://1sprogress.ru/analiz-zaprosov-s-pomoshhyu-sql-profiler.html