vk fb tw rss

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

Analysis

 

 

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

 

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

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

Зачем SQL Profilerможет понадобиться программисту 1С?

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

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

Так же с помощью профайлера можно определить:

запросы длиннее определенного времени

запросы к определенной таблице

ожидания на блокировках

таймауты

взаимоблокировки

и многое другое…

 

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

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

Что бы отловить запрос в трассировке делаем следующее:

1. Запускаем SQL Profiler

Пуск — Все программы — Microsoft SQL Server 2008 R2 — Средства обеспечения производительности — SQL Profiler

2. Создаем новую трассировку

Файл – Создать трассировку (Ctrl+N)
3. Указываем сервер СУБД на котором находится наша база данных и нажимаем «Соединить».

1 Подключение к серверу

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

4. В появившемся окне «Свойства трассировки» переходим на вторую закладку «Выбор событий»

2 Свойства трассировки

5. Теперь необходимо указать события и свойства этих событий, которые мы хотим видеть в трассировке.

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

Далее нужно выбрать только события, приведенные на рисунке ниже, все остальные события нужно отключить.

3 Cобытия SQL Profiler

Описание событий:

ShowplanStatisticsProfile– текстовый план выполнения запроса

ShowplanXMLStatisticsProfile– графический план выполнения запроса

RPC:Completed– текст запроса, если он выполняется как процедура (если выполняется запрос 1С с параметрами).

SQL:BatchCompleted– текст запроса, если он выполняется как обычный запрос (если выполнялся запрос 1С без параметров).

 

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

Нажимаем кнопку «Фильтры столбцов» и указываем фильтр по имени базы данных

4 Фильтр по БД

Теперь мы будем видеть в трассировке только запросы к базе данных «TestBase_8_2»

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

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

a) Фильтр по базе данных, пример показан выше

b) Фильтр по длительности в миллисекундах.

5 Фильтр по длительности

c) Фильтр по тексту запроса

6 фильтр по тексту

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

 

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

Для управления трассировкой можно использовать кнопки на командной панели.

7 Запуск и остановка трассировки

Слева на право:

Ластик – очищает окно трассировки

Пуск – запускает трассировку

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

Стоп – останавливает трассировку

 

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

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

 

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

8 Запрос 1С

9 Запрос SQL

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

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

В моем случае запрос выполнялся 2 миллисекунды, сделал 4 логических чтения и вернул 1 строку.

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

10 План запроса

 

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

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

12 Сохранение плана запроса

12. Если подняться еще выше, то мы увидим тот же план запроса, но уже в текстовом виде.

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

11 Текстовый план

13. Использую меню «Файл-Сохранить как», всю трассировку можно сохранить в различные форматы:

a) В формат самого профайлера, т.е. с расширением *.trc

b) В формат xml

c) Можно сделать из трассировки шаблон. См. следующий пункт.

d) Можно сохранить трассировку в виде таблицы базы данных. Удобный способ, если нам нужно найти например самый медленный запрос во всей трассировке либо отобрать запросы по какому-либо параметру.

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

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

13 Выгрузка трассировку в таблицу

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

14 Запрос к таблице трассировки

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

 

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

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

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

15 Шаблон 1

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

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

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

16 Порядок столбцов

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

17 Выбор шаблона

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

Если у вас остались вопросы по использованию SQL Profiler, задавайте их в комментариях, буду рад ответить.

 



Лучшие материалы по теме

Расскажите своим друзьям
Вам ничего не стоит, а им будет интересно
Подпишитесь на обновления
Ваш e-mail: * Ваше имя: *


Обсудить Вконтакте


Обсудить в Facebook

5 комментариев: Анализ запросов с помощью SQL Profiler

  • Андрей, у меня вопрос следующего порядка: Есть запрос:
    В нем идет соединение справочника с виртуальной таблицей (срез регистра) без параметров.
    В ней порядка 120 тыс. записей.
    Теоретически должно быть выбраны все эти записи сгруппированные определенным образом,
    а затем соединением обрезаны до нужных данных.

    Сам запрос на SQL
    exec sp_executesql N’SELECT
    T1._IDRRef,
    T2.Period_,
    T2.Fld24RRef
    FROM dbo._Reference9 T1
    LEFT OUTER JOIN (SELECT
    T5._Period AS Period_,
    T5._Fld23RRef AS Fld23RRef,
    T5._Fld24RRef AS Fld24RRef
    FROM (SELECT
    T4._Fld23RRef AS Fld23RRef,
    T4._Fld24RRef AS Fld24RRef,
    MAX(T4._Period) AS MAXPERIOD_
    FROM dbo._InfoRg22 T4
    GROUP BY T4._Fld23RRef,
    T4._Fld24RRef) T3
    INNER JOIN dbo._InfoRg22 T5
    ON T3.Fld23RRef = T5._Fld23RRef AND T3.Fld24RRef = T5._Fld24RRef AND T3.MAXPERIOD_ = T5._Period) T2
    ON (T1._IDRRef = T2.Fld23RRef)
    WHERE (T1._IDRRef = @P1)’,N’@P1 varbinary(16)’,0xB6B814DAE9E2F88B11E4EF11DA4BF641

    Но в плане запроса я вижу выполнения один раз с возвратом 3 строк.
    Не видно выборку всех записей из вложенного запроса (виртуальной таблицы)
    Почему это происходит?

    ВЫБРАТЬ
    ДелаКлиентов.Ссылка,
    ЗадачиПоДеламСрезПоследних.Период,
    ЗадачиПоДеламСрезПоследних.ВидЗадачи
    ИЗ
    Справочник.ДелаКлиентов КАК ДелаКлиентов
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗадачиПоДелам.СрезПоследних КАК ЗадачиПоДеламСрезПоследних
    ПО ДелаКлиентов.Ссылка = ЗадачиПоДеламСрезПоследних.Дело
    ГДЕ
    ДелаКлиентов.Ссылка = &Ссылка

    • Здравствуйте.
      Пришлите пожалуйста плана запроса в формате sqlplan, его можно получить из профайлера через контекстное меню по события Showplan XML Statistics.
      В идеале бы конечно получить минимальную базу для воспроизведения.

    • Оптимизатор штука очень умная.
      Вы фильтруете левую часть по одному значению, оптимизатор принимает верное решение, сначала находит нужную запись, затем использует Nested Loops и обращается к таблице регистра, там по индексу он быстро находит 3 ваши строки и их возвращает. Таким образом ему незачем обрабатывать все строки.

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

  • Какой вообще смысл тут соединений не могу понять? Вроде как запрос должен быть таким:

    ВЫБРАТЬ
    ЗадачиПоДеламСрезПоследних.Дело,
    ЗадачиПоДеламСрезПоследних.Период,
    ЗадачиПоДеламСрезПоследних.ВидЗадачи
    ИЗ
    РегистрСведений.ЗадачиПоДелам.СрезПоследних(, Дело= &Ссылка) КАК ЗадачиПоДеламСрезПоследних

    и анализировать уже даже нечего… =)

    • Это да, но возможно человек просто хочет научиться читать план запроса.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *