IE 2016

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

Опубликовал Andreynikus в раздел Администрирование - Оптимизация БД (HighLoad)

В своей работе мы довольно часто мы сталкиваемся с ситуацией, когда определенный запрос работает медленно, причем по тексту запроса невидно никаких очевидных проблем. Обычно в этом случае необходимо расследовать проблему на более глубоком уровне. Как правило, возникает необходимость посмотреть текст SQL-запроса и его план, и вот в этом нам как раз помогает 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

См. также

Лучшие комментарии

1. asved.ru 23.07.2014 03:55
Я бы переименовал статью в "Сбор информации для анализа запросов с помощью SQL Profiler". Т.к. собственно про анализ плана запроса ни слова не сказано даже на уровне определения неоптимальных операторов плана.
# Ответить
5. tormozit 23.07.2014 09:37
Профайлер SQL конечно крут, но техножурнал платформы все же круче, т.к. там видно только то, что в основном нужно с переводом в термины метаданных. А в сыром виде в профайлере анализировать логи затратнее, т.е. менее эффективно.
Ответили: (7)
+ 2 [ wowik; _also; ]
# Ответить
11. Andreynikus 24.07.2014 09:06
(10) awk, Разница только в восприятии.
Я же говорю, кому что удобнее
+ 1 [ awk; ]
# Ответить

Комментарии

1. asved.ru 23.07.2014 03:55
Я бы переименовал статью в "Сбор информации для анализа запросов с помощью SQL Profiler". Т.к. собственно про анализ плана запроса ни слова не сказано даже на уровне определения неоптимальных операторов плана.
# Ответить
2. rasswet 23.07.2014 08:35
спасибо, вполне понятно объяснили!
# Ответить
3. Scop 23.07.2014 08:57
Спасибо, отличная вводная статья в тему.
# Ответить
4. Al-X 23.07.2014 09:04
Спасибо !!! Все относительно ясно. Пойду смотреть, как все работает, а потом использовать для оптимизации моих запросов.
# Ответить
5. tormozit 23.07.2014 09:37
Профайлер SQL конечно крут, но техножурнал платформы все же круче, т.к. там видно только то, что в основном нужно с переводом в термины метаданных. А в сыром виде в профайлере анализировать логи затратнее, т.е. менее эффективно.
Ответили: (7)
+ 2 [ wowik; _also; ]
# Ответить
6. awk 23.07.2014 13:38
ShowplanXMLStatisticsProfile– графический план выполнения запроса
Не графический, а в XML. В графику его программа просмотра преобразовывает.
# Ответить
7. asved.ru 23.07.2014 15:37
(5) tormozit, есть минусы:
1) Получение планов запросов в ТЖ замедляет выполнение запросов.
2) ТЖ еще и парсить нужно.
Ответили: (8)
# Ответить
8. tormozit 23.07.2014 16:20
(7)
1. Видимо ты имеешь ввиду, что получение планов запросов в техножурнале платформы замедляет выполнение запросов сильнее, чем их получение в SQL профайлере. Думаю, что это конечно так, но велика ли разница?
2. Парсить логи профайлера SQL тоже нужно. Просто для него есть штатное быстрое общедоступное средство. Для техножурнала платформы быстрое средство есть (Enterprise Integrator Германа Кудякова), но оно не общедоступно. Есть менее быстрые средства, например мой "Анализ техножурнала" из подсистемы "Инструменты разработчика".
Ответили: (9)
+ 1 [ pt_olga; ]
# Ответить
9. Andreynikus 23.07.2014 18:10
(8) tormozit,
Лучший инструмент тот, которым лучше всего умеешь пользоваться -)
Мне профайлером просто быстрее получить план запроса, с ТЖ получится дольше, и как я уже писал выше в ТЖ нельзя посмотреть план запроса в графическом виде, да и в текстовом виде придется самому помнить какая там последовательность колонок.
Тут как всегда, кому что удобнее.

Зато получение плана в ТЖ конечно более универсально, его можно использовать для любых СУБД, а не только для MS SQL Server.
Ответили: (10)
# Ответить
10. awk 23.07.2014 22:39
(9) Andreynikus, А какая разница в графическом или текстовом? В текстовом тоже понятно все. Более того мне больше понятно в текстовом виде. Для разбора любых текстовых файлов, лично я, пользуюсь grep + awk + sed. В windows через cygwin.
Ответили: (11)
# Ответить
11. Andreynikus 24.07.2014 09:06
(10) awk, Разница только в восприятии.
Я же говорю, кому что удобнее
+ 1 [ awk; ]
# Ответить
12. ivanov660 25.07.2014 13:03
По моему опыту для средних и сложных запросов графическое представление плана запросов, да и тестовое представление довольно сложно подвергается анализу.
Ответили: (13) (16)
# Ответить
13. Andreynikus 27.07.2014 09:02
(12) ivanov660,
Согласен, большие планы анализировать в профайлере очень не удобно, но эта статья и не про анализ планов, а про их получение.
Для больших и сложных планов рекомендую использовать "SQL Sentry Plan Explorer", там даже очень большие планы анализировать довольно просто, и данный инструмент гораздо более информативен чем профайлер.
# Ответить
14. DoctorRoza 29.07.2014 07:56
Отличная статья! Плюс однозначно!
# Ответить
15. serno 29.07.2014 14:23
Хорошая инструкция, но все таки ближе к теме "Как пользоваться профайлером", а не про анализ планов.
# Ответить
16. AlX0id 01.08.2014 15:16
(12) ivanov660,
Ну если и графическое представление сложно для восприятия, то может быть порекомендуете другие инструменты?
# Ответить
17. wolverine87 13.08.2014 05:07
Спасибо автору. Полезная статья. + 1
# Ответить
18. Aleksey.Bochkov 26.07.2015 01:13
(0) Обновили бы статью.. все ссылки битые.
Картинки лучше локально разместить.
# Ответить
19. hulio 27.07.2015 08:13
(0) Автор, обновите, пожалуйста, статью - без картинок неинтересно :)
# Ответить
Внимание! За постинг в данном форуме $m не начисляются.
Внимание! Для написания сообщения необходимо авторизоваться
Текст сообщения*
Прикрепить файл






IE 2016