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

Материал из Lexema.ru
Перейти к: навигация, поиск

При разработке прикладных модулей системы Lexema.ru периодически возникает потребность в анализе запросов к БД при работе экранных форм, запросов, отчётов, хранимых процедур и других объектов для диагностирования проблем. Для решения подобных задач предназначены инструменты профилирования SQL-запросов. Они позволяют:

  • отслеживать события разных типов на сервере БД (выполнение запросов, хранимых процедур и т.п.)
  • фильтровать события по различным критериям (имя БД, логин пользователя и т.п.)
  • записывать последовательность действий в виде трассировки событий. В последствии трассировка может быть проанализирована, а также сохранена в файл или в БД.
  • анализировать производительность (скорость выполнения) запросов для поиска и устранения "узких мест" системы
  • и т.п.

В данной статье рассмотрены два инструмента:

  • Lexema SQL Profiler, встроенный в моделлер приложения
  • MS SQL Server Profiler, входящий в состав MS SQL Server

Lexema SQL Profiler

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

Для использования Lexema SQL Profiler запустите моделлер приложения. Нажмите на кнопку с изображением бочки в левом верхнем углу окна:

LexemaSqlProfilerButton.png

Для начала записи трассировки SQL-запросов моделлера к серверу БД нажмите кнопку "Запустить" на панели инструментов.

Выполните в моделлере действия, для которых требуется изучить запросы к БД. Например, после открытия списка моделей будет создана трассировка из нескольких запросов:

LexemaSqlProfilerWindow.png

Таблица сверху содержит список событий (SQL-запросов), поле внизу - содержимое (SQL-код)

Поля таблицы:

  • EventClass
  • TextData
  • Duration
  • StartTime
  • EndTime
  • CPU
  • ApplicationName
  • Reads
  • Writes
  • SPID
  • Transaction

К примеру, из трассировки событий при открытии списка моделей можно сделать следующие выводы: запрашиваются данные из трёх таблиц (L8_Model, L8_ModelProperty и L8_Namespace); самый длительный запрос производится к таблице L8_ModelProperty (242 мс).

MS SQL Server Profiler

MS SQL Server Profiler — это инструмент, входящий в состав пакета MS SQL Server, позволяющий перехватывать события сервера БД. События могут быть сохранены в файле трассировки или в БД для дальнейшего анализа или использования с целью повторения определенной серии шагов для воспроизведения проблемы для её диагностики. Типовые сценарии использования SQL Server Profiler:

  • контроль производительности экземпляра SQL Server Database Engine
  • отладка инструкций Transact-SQL и хранимых процедур
  • анализ производительности путем выявления медленно работающих запросов
  • выполнение стресс-тестирования и контроля качества посредством воспроизведения трассировок
  • воспроизведение трассировки одного или нескольких пользователей
  • проверка инструкций Transact-SQL и хранимых процедур на стадии разработки проекта в пошаговом режиме для гарантии правильного выполнения кода
  • устранение проблем в SQL Server с помощью перехвата событий в производственной системе (production-версии) и воспроизведения их в отладочной (тестовой версии). Это очень полезная возможность, поскольку позволяет во время проверки или отладки продолжать использовать производственную систему.
  • аудит и отслеживание действий, происходящих в экземпляре SQL Server. Эта возможность позволяет администратору безопасности просматривать любые события аудита, в частности успешные и неудачные попытки входа в систему и разрешений доступа к инструкциям и объектам
  • сохранение результатов трассировки в формате XML, что обеспечивает стандартизованную иерархическую структуру хранения результатов трассировки. Это позволяет вносить изменения в существующие трассировки или создавать их вручную для последующего воспроизведения
  • статистический анализ результатов трассировки, позволяющий производить группирование и анализ похожих классов событий. В результатах содержатся счетчики, полученные на основе группирования по одному столбцу
  • предоставление возможности создания трассировки пользователям, не являющимся администраторами
  • настройка шаблонов трассировки, которые затем могут быть использованы для последующих трассировок

Запуск и подключение к серверу

Запустить MS SQL Server Profiler можно из меню ОС Windows (меню "Пуск") или из меню программы MS SQL Server Management Studio (пункт Сервис - "SQL Server Приложение Profiler"). После запуска необходимо авторизоваться на сервере - ввести адрес сервера, имя учётной записи и пароль:

ProfilerAuth.png

Настройка параметров трассировки

Затем перед началом трассировки необходимо задать её свойства:

ProfilerTraceProperties.png

  • Имя трассировки - целесообразно задавать в том случае, если её планируется сохранить
  • Использовать шаблон - определяет конфигурацию трассировки по умолчанию. А именно, он включает классы событий, которые нужно контролировать в SQL Server Profiler. Например, можно создать шаблон, указывающий используемые события, столбцы данных и фильтры.Шаблоны не выполняются, а сохраняются в файлах с расширением TDF.После сохранения шаблон управляет захватом данных, если запускается трассировка, основанная на этом шаблоне.
  • Сохранить в файл с целью повторного открытия и анализа
  • Сохранить в таблицу - в этому случае трассировка будет сохранена в БД и её можно будет анализировать средствами SQL
  • Включить время остановки трассировки - необходимо в случае длительных наблюдений

Выбор типов событий и их атрибутов

Важным шагом настройки трассировки является выбор событий (необходимо перейти на соответствующую вкладку). Вкладка "Выбор событий" содержит сетку - таблицу, которая содержит каждый из классов событий, доступных для трассировки. На каждый класс событий в таблице приходится по одной строке. Классы событий могут незначительно различаться в зависимости от типа и версии сервера, к которому они подключены. Классы событий идентифицируются в столбце События сетки и группируются по категориям событий. В оставшихся столбцах перечислены столбцы данных, которые могут быть возвращены для каждого класса событий. Чтобы включить события в трассировку, установите флажок в столбце События.

По умолчанию данный список содержит только некоторые категории и типы событий в соответствии с выбранным шаблоном (см. выше). Также отображаются не все столбцы. Для более детальной настройки рекомендуется поставить галочки "Показать все события" и "Показать все столбцы".

Если трассировка производится для отслеживания запросов, производимых приложением Lexema.ru, достаточно будет отметить 3 типа события в 2х группах:

  • Stored Procedures (хранимые процедуры)
    • RPC:Completed - происходит при завершении удалённого вызова процедуры (RPC)
    • SP:Completed - происходит при завершении хранимой процедуры
  • TSQL - отслеживание выполнения инструкций TransactSQL, передаваемых клиентами на сервер БД
    • SQL:BatchCompleted - возникает при завершении выполнения инструкции TransactSQL

Примечание: флажок в столбце Events может находится в трёх состояниях:

  • галочка отсутствует - событие не отслеживается
  • установлена чёрная галочка - выбраны все столбцы данных - для выбранного события будут собираться все возможные для него данные
  • установлена серая галочка - выбраны только некоторые столбцы данных - для выбранного события будут собираться только некоторые данные в соответствии с отметками в столбцах

По умолчанию для некоторых событий выбраны не все столбцы (стоит серая галочка). Для выбора всех столбцов необходимо снять галочку и установить её заново. При этом будут установлены галочки для всех видимых столбцов данных.

ProfilerTraceEvents.png

Ниже перечислены другие полезные категории и типы событий:

  • Security Audit

Настройка параметров фильтрации

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

При отслеживании событий, происходящих при использовании веб-интерфейса Lexema.ru конкретным пользователем, целесообразно установить фильтр "ApplicationName" похоже на <логин_пользователя>+&1, например, "PetrovAN&1", где PetrovAN - логин пользователя:

ProfilerTraceFilter.png

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

Отслеживание событий

Предположим, что после выставления настроек, описанных выше и запуска трассировки, пользователь с логином 'airat' входит в систему и открывает реестр категорий доходов и расходов модуля "Домашняя бухгалтерия", а затем открывает один из документов (в качестве примера):

ProfilerOpenRegistry.png

В результате в трассировке SQL Server Profiler будет отображён список событий:

ProfilerTraceData.png

Анализируя список событий трассировки можно увидеть, что одним из самых длительных запросов был следующий:

exec sp_executesql N'SELECT [COrg] AS [COrg], [WOrg] AS [WOrg], [Description] AS [Description], [CHost] AS [CHost], [CUser] AS [CUser], [WHost] AS [WHost], [WUser] AS [WUser], [Name] AS [Name], [User] AS [User], [ParentName] AS [ParentName], [FullName] AS [FullName], [VCode] AS [VCode], [PCode] AS [PCode], [IsPersonal] AS [IsPersonal], [CDate] AS [CDate], [WDate] AS [WDate], [TType] AS [TType]
FROM [VTransactionCategory]
',N'@PrimaryKeyBoundary bigint,@TopCount bigint',@PrimaryKeyBoundary=NULL,@TopCount=NULL

Судя по названию объекта (VTransactionCategory), это запрос на выборку списка категорий транзакций. Тип данного события - RPC:Completed (завершение выполнения удалённой процедуры).

Также в списке можно видеть событий типа SQL:BatchCompleted:

select VCode, Name
from TransactionCategory 
where PCode is null

Это результат выполнения запроса (QuerySource) Lexema.ru.

Для поиска в тексте запросов, отслеженных в трассировке, необходимо нажать кнопку "Найти строку" (со значком бинокля) на панели инструментов или нажать комбинацию клавиш Ctrl+F:

ProfilerFindTextInTrace.png

Затем необходимо ввести искомый текст и выбрать столбец для поиска (текст запроса содержится в столбце TextData). После нажатия на кнопку "Следующий" курсор будет позиционирован на строку события, содержащего искомый текст.

В целях тестирования и отладки данные запросы могут быть выполнены повторно вручную. Для этого необходимо скопировать их текст, открыть SQL Server Management Studio, подключиться к соответствующему серверу, выбрать БД, создать запрос, вставить его текст и выполнить:

ProfilerTestQueryInStudio.png

Дополнительные ссылки