Sql profiler как настроить для 1с

Обновлено: 06.07.2024

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

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

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

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

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

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

Зачастую в работе возникает ситуация, когда запрос в 1С по каким-то причинам работает медленно, но анализ текста запроса не говорит нам о каких-либо проблемах.

В таком случае приходится изучать эту проблему на более низком уровне. Для этого нам нужно посмотреть текcт SQL-запроса и план запроса. Для этого можно использовать SQL Profiler.

SQL Profiler – предназначение

SQL Profiler – это программа, входящая в MS SQL Server, которая предназначена для просмотра всех событий, которые происходят в SQL-сервере. Иначе говоря, она нужна для записи трассировки.

В каких случаях данный инструмент может быть полезен 1С программисту? Прежде всего, можно получить текст запроса на языке SQL и посмотреть его план. Это также можно сделать и в технологическом журнале (ТЖ), но план запроса в ТЖ получается не таким удобным и требует наличия некоторых навыков и умений. К тому же в профайлере можно посмотреть не только текстовый, но и графический план выполнения запроса, что является более удобным.

Также профайлер позволяет узнать:

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

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

Зачастую 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С.

13. Через меню Файл – Сохранить как можно сохранить всю трассировку в различные форматы:

В формат самого профайлера, то есть с расширением *.trc
В формат xml
Сделать из трассировки шаблон (См. следующий пункт)
Cохранить полученную трассировку в виде таблицы базы данных. Это весьма удобный способ, когда, к примеру, нужно найти самый медленный запрос в трассировке или отфильтровать запросы по какому-либо параметру.

Используем меню Файл – Сохранить как – Таблица трассировки – Выбираем сервер СУБД и подключаемся к нему.

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

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

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

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

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

Для создания шаблона используем меню Файл – Шаблоны – Новый шаблон:

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

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

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

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

PDF-версия статьи для участников группы ВКонтакте

Статья в PDF-формате

Станьте экспертом по оптимизации 1С, изучив наш курс
«Ускорение и оптимизация систем на 1С:Предприятие 8.3 (2016). Подготовка на 1С:Эксперт по технологическим вопросам»

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

Комментарии / обсуждение (9):

Имя индекса отображается одинаково и в MS SQL и в обработке структура хранения метаданных и начинается с 1.

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

Создание трассировки в Profiler и 1С

Для того чтобы отследить действия, выполняющиеся в данный момент, необходимо запустить MS SQL Profiler, создать новую трассу и настроить анализ показателей:

Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания - попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>

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

Запуск трассировки в Profiler для 1С

После того как трассировка получена, необходимо её проанализировать.

Анализ данных из Profiler

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

После сохранения таблицы с ней можно полноценно работать, как с обычной таблицей SQL. Например, отсортируем по duration (время выполнение операции) с помощью нехитрого запроса:

Ранее, я рассматривал, причины неоптимальной работы запросов в 1С. Однако, эти действия иногда могут не помочь в борьбе за повышение производительности системы. В этом случае, необходимо обратиться к внесистемным средствам анализа на уровне СУБД, к SQL Server Profiler.

Приложение SQL Server Profiler — это графический пользовательский интерфейс для трассировки SQL, с помощью которого программист 1С может наблюдать за экземпляром компонента Компонент Database Engine или службами Analysis Services. С помощью данной утилиты, мы может анализировать план выполнения запроса в пошаговом режиме. Приложение позволяет собирать и сохранять данные о каждом событии в файле или в таблице для последующего анализа.

Признаки выбора неоптимального плана запроса СУБД

Как правило, MS SQL подбирает для 1С оптимальный план запроса, но бывает СУБД ошибается. Связано это может быть, например, с неактуальной статистикой или высокой загруженностью системы. Программисту 1С, для того, что бы помочь оптимизатору строить правильный план запроса, необходимо проверить настройку регламентных операция на СУБД MS SQL. Среди признаков неоптимального построения плана запроса для 1С могут быть конструкции:

NESTED LOOPS

Алгоритм соединения вложенными циклами, по сути своей, это простой перебор двух таблиц и вывод удовлетворяющих соединению строк. Данный вид соединения недопустим для ведущей таблицы, с большим количество записей. Однако данный вид соединения самый простой и часто используется когда СУБД MS SQL не может подобрать другой вариант соединения. В целом, NESTED LOOPS допустим к использованию, когда в ведущей таблице не более двух записей и скорее всего он быстрее отработает в такой ситуации.

Аналог сканирования (SCAN), за тем исключением, что сканируется часть таблицы, по установленному условию.

Читайте также: