11.2.3. Работа с профилировщиком

Профилировщик (profiler) SQL Server 2005, отслеживание запросов приложений, шаблоны трассировки, группировка информации о запросах

Одно из самых полезных средств мониторинга активности пользователей — это профилировщик (Profiler). При помощи этого программного средства можно узнать, какие команды в настоящее время выполняет сервер SQL Server. Необходимость в применении профилировщика возникает очень часто. Вот несколько стандартных ситуаций, когда без него обойтись бывает очень сложно:

q      вы хотите проанализировать работу приложения и посмотреть, какие команды оно выполняет на сервере. Эта информация может пригодиться:

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

·                чтобы выяснить, насколько оптимальные с точки зрения производительности запросы передает на сервер приложение. На практике при использовании профилировщика часто можно выявить совсем неоптимальные запросы, например, когда фильтрация или сортировка данных выполняется на клиенте;

·                чтобы понять, при выполнении какой команды Transact-SQL из приложения на сервере возникает ошибка;

q      для сбора информации о пользовательской активности в течение продолжительного промежутка времени (например, можно собрать все запросы, которые передавались на сервер определенным приложением в течение рабочего дня). Затем собранную информацию можно проанализировать вручную или передать программе Database Tuning Advisor для проведения автоматизированного анализа;

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

В SQL Server 2005 у профилировщика появилось много нового:

q      добавилась возможность профилировки Analysis Services. Теперь вы можете просматривать команды и события не только для обычных баз данных, но и для баз данных OLAP;

q      появилась профилировка событий Integration Services. Теперь вы можете при помощи профилировщика отслеживать ход выполнения новых пакетов DTS;

q      появилась возможность при записи информации выполнения команды записывать также показания счетчиков из Системного монитора;

q      в профилировщик добавлено много новых событий и источников информации, которые могут выбираться для записи в файл трассировки. Определение того, что нужно записывать в файл трассировки, теперь можно сохранять в формате XML;

q      в формате XML теперь можно сохранять и результаты трассировки (возможность записи в форматы ANSI, OEM, UNICODE также сохранена);

q      в формате XML можно сохранять даже планы выполнения команд Transact-SQL, перехваченных профилировщиком. Затем эти планы можно открыть в SQL Server Management Studio для дальнейшего анализа;

q      появилась возможность группировать события прямо в окне профилировщика. С помощью группировки, например, можно очень просто посчитать, сколько раз в течение дня на сервере выполнялась та или иная команда Transact-SQL.

Работа с профилировщиком выглядит очень просто. Это приложение можно запустить из меню Пуск | Программы | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler. Для того чтобы начать работу, в открывшемся окне профилировщика в меню File (Файл) нужно выбрать New Trace (Новая трассировка) и подключиться к серверу SQL Server 2005, работу которого вы будете отслеживать. Под словом "трассировка" подразумевается сеанс сбора информации о работе SQL Server 2005. Однако перед тем, как приступать к сбору информации, нужно настроить параметры этого сеанса. Эта настройка производится в окне Trace Properties (Свойства трассировки), которое открывается автоматически перед началом сеанса трассировки (рис. 11.1).

Рис. 11.1. Настройка параметров сеанса трассировки

На вкладке General (Общие) в списке Use the template (Использовать шаблон) вы можете выбрать наиболее подходящий шаблон для сбора информации в рамках вашего сеанса. В принципе, можно и не обращать внимание на настройки шаблона, а вручную определить параметры сбора информации (при помощи соседней вкладки Events Selection (Выбор событий)). Однако указание правильного шаблона поможет сэкономить время и избежать ошибок. Поэтому на шаблонах остановимся подробнее.

Шаблон — это сохраненные в специальном файле с расширением tdf настройки сеанса трассировки. Работа с шаблонами (добавление новых, изменение существующих, импорт и экспорт отчетов в другие каталоги) производится при помощи меню File | Templates (Файл | Шаблоны) в SQL Server Profiler. Изначально в вашем распоряжении есть восемь шаблонов:

q      Standard (default) — как понятно из названия, этот шаблон подходит для большинства ситуаций и поэтому выбирается по умолчанию. Он позволяет отслеживать все запускаемые на выполнение хранимые процедуры и команды Transact-SQL;

q      SP_Counts — собирается информация о запускаемых на выполнение хранимых процедурах и функциях. При этом информация в окне профилировщика сортируется (в терминологии профилировщика — группируется) по именам хранимых процедур;

q      TSQL — собирается информация о всех командах Transact-SQL, запускаемых на выполнение на сервере. Кроме кода команд, записывается также информация об идентификаторах пользовательских процессов и времени запуска. Обычно этот шаблон используется для мониторинга команд, передаваемых на сервер приложением;

q      TSQL_Duration — почти то же самое, что и предыдущий шаблон, но вместо записи информации о времени запуска команды Transact-SQL записывается время, которое потребовалось на ее выполнение. Обычно этот шаблон используется для мониторинга производительности работы сервера "вручную";

q      TSQL_Grouped — кроме информации о коде команды Transact-SQL и времени ее запуска, записывается информация о имени приложения, учетной записи пользователя в операционной системе и логине пользователя, который был использован для подключения. При этом записи группируются по логину. Обычно этот шаблон используется в тех ситуациях, когда вы хотите отследить активность конкретного приложения;

q      TSQL_Replay — будет записываться максимально подробная информация о выполняемых командах Transact-SQL. Потом эту информацию можно использовать для того, чтобы с максимальной точностью воспроизвести нагрузку на сервер. Обычно этот шаблон применяется для записи набора команд, который будет потом использоваться для тестирования разных настроек сервера с точки зрения производительности;

q      TSQL_SPs — кроме записи информации о начале запуска всей хранимой процедуры (событие SP:Starting), этот вариант трассировки записывает также информацию о выполнении каждой команды данной хранимой процедуры (событие SP:StmtStarting). Такой шаблон обычно используется для мониторинга работы сложных хранимых процедур;

q      Tuning — этот шаблон предназначен для записи информации, наиболее подходящей для передачи Database Tuning Advisor. Про работу с этим средством автоматизированного анализа и оптимизации производительности будет рассказано в разд. 11.5.5.

Как уже говорилось, совсем необязательно ограничиваться только набором готовых шаблонов. Можно использовать свои параметры сеанса трассировки, настроив их на вкладке Events Selection. В таблице на этой вкладке вы должны выбрать требуемые события (в строках) и информацию (в столбцах), которая будет для них записываться. Обратите внимание, что по умолчанию видна только небольшая часть доступных строк и столбцов. Чтобы включить отображение всех строк и столбцов, нужно установить флажки Show All Events (Показать все события) и Show All Columns (Показать все столбцы).

Очень часто бывает так, что нужно отслеживать только действия, выполняемые в определенной базе данных, или определенным приложением, или определенным пользователем, или выбрать все эти условия одновременно. Фильтры на сбор информации можно настроить, нажав кнопку Column Filters (Фильтры столбцов) на вкладке Events Selection. Для каждого столбца можно настроить запись только определенных значений (Like) или запрет записи определенных значений (Not Like). По умолчанию настроен единственный фильтр — Not Like для столбца ApplicationName. Он заставляет игнорировать все события приложения SQL Server Profiler, т. е. все события, относящиеся к самому процессу сбора информации трассировки. Этот фильтр лучше не удалять, потому что в противном случае может возникнуть положительная обратная связь с бесконечной записью информации.

При помощи еще одной кнопки Organize Columns (Организовать столбцы), которая расположена на вкладке Events Selection, можно настроить порядок столбцов для отображения или записи в профилировщике. Обратите внимание на раздел Group (Группа) в этом списке. Для тех столбцов, которые в него помещены, будет автоматически производиться группировка. Если вы поместите в этот раздел только один столбец, то при просмотре у вас появится возможность использовать очень удобный режим Aggregated View (Агрегированное представление) (когда информация автоматически сгруппирована, например, по базе данных, по приложению, имени пользователя и т. п., и записи для нужной базы данных, приложения или пользователя можно раскрывать и сворачивать).

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

Информация трассировки может быть запротоколирована в файл. Этот файл можно использовать в разных ситуациях:

q      можно передать в качестве источника информации Database Tuning Advisor;

q      можно "проиграть" повторно в профилировщике, повторив все записанные команды, например, для оценки производительности при разных настройках сервера;

q      можно предъявить разработчикам в подтверждение своих претензий к приложению.

Отметим некоторые моменты, которые связаны с протоколированием сеанса трассировки в файл:

q      5 Мбайт, которыми ограничивается размер файла по умолчанию, это очень мало. При профилировке рабочего сервера этот размер набирается за минуты. Правда, по умолчанию установлен флажок Enable file rollover (Включить смену файлов), т. е. после заполнения одного файла автоматически будет создан второй файл, к имени которого добавится номер 1, потом — 2 и т. п., но работать с большим количеством файлов не всегда удобно. Если вы собираете информацию для передачи Database Tuning Advisor, то лучше настроить предельный размер файла в 1 Гбайт (при помощи параметра Set maximum file size (Настроить максимальный размер файла) на вкладке General). Запись трассировки в файл чаще всего производится с рабочей станции администратора, поэтому место на диске потребуется именно на рабочей станции, а не на сервере;

q      параметр Server processes trace data (Сервер обрабатывает данные трассировки) можно использовать для увеличения надежности записи информации трассировки. По умолчанию обработкой данных трассировки занимается SQL Server Profiler, и происходит это на том компьютере, на котором он запущен (не обязательно на сервере). Если установить этот флажок, то обработкой информации трассировки будет заниматься сервер. Это гарантирует, что вся информация трассировки будет собрана (при снятом флажке в моменты пиковой нагрузки сервера часть информации может быть пропущена), но увеличит нагрузку на сервер.

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

Последний параметр на вкладке General — Enable Trace stop time (Включить время остановки трассировки). Вы можете указать время, когда трассировка будет отключена автоматически. Обычно имеет смысл отключать трассировку перед началом каких-то служебных операций, которые с точки зрения протоколирования вас не интересуют (резервное копирование, массовая загрузка данных, процессинг кубов OLAP и т. п.).

После того как все параметры трассировки будут настроены, можно нажать на кнопку Run (Запустить) на вкладке General и приступить к трассировке (рис. 11.2).

Рис. 11.2. Просмотр информации в ходе сеанса трассировки

Работа в окне просмотра информации трассировки достаточно очевидна: в верхней части показываются события, которые происходят на сервере, а в нижней части для них приводится подробная информация (например, код команд SQL). Отметим некоторые возможности, доступные в этом окне:

q      если на вкладке Organize Columns в свойствах шаблона вы выбрали столбцы для группировки, то можно сгруппировать по этим столбцам записи в окне просмотра. Для этой цели в меню View (Вид) предусмотрена команда Grouped View (Сгруппированное представление);

q      если на той же вкладке в свойствах шаблона в список Group был помещен только один столбец, то можно использовать еще более удобный режим отображения Aggregated View (рис. 11.3). Этот режим включается при помощи команды Aggregated View из того же меню View и позволяет превратить значения из выбранного вами столбца в узлы дерева, которые можно сворачивать и разворачивать. Кроме того, для каждого из этих узлов автоматически подсчитывается количество событий.

Рис. 11.3. Режим отображения Aggregated View

q      в профилировщике можно отобразить не только те события, которые были пойманы только что, но также сохраненные файлы и таблицы трассировки. Кроме того, вы можете открывать обычные скрипты SQL Server с командами Transact-SQL. Информация из этих файлов или таблиц может быть использована для того, чтобы повторить запротоколированные операции. Для этой цели предназначены команды меню Replay (Повторить);

q      в профилировщике SQL Server 2005 появилась новая возможность — связывание информации трассировки с показателями счетчиков производительности Системного монитора. Для того чтобы воспользоваться этой возможностью, нужно:

·                определить сеанс трассировки, в ходе которого обязательно должна записываться информация для столбцов StartTime и EndTime;

·                запустить сеанс трассировки с записью информации в файл или таблицу. Одновременно с ним собрать в файл протокол показаний счетчиков Performance Monitor;

·                открыть собранную информацию из файла трассировки в профилировщике, а затем воспользоваться командой Import Performance Data (Импортировать данные производительности) из меню File.

В SQL Server 2005 предусмотрен заменитель для профилировщика. Это хранимые процедуры трассировки. Их функциональные возможности практически идентичны возможностям профилировщика. Например, вы можете также выбрать события для трассировки и записать их в текстовый файл. Главное отличие заключается в том, что все настройки придется производить из кода Transact-SQL.

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

q      sp_trace_create — позволяет настроить параметры сеанса трассировки;

q      sp_trace_setevent — позволяет выбрать для созданного сеанса трассировки требуемые события;

q      sp_trace_setfilter — позволяет настроить фильтр для сбора информации трассировки;

q      sp_trace_setstatus — позволяет запустить трассировку, остановить ее или удалить созданное хранимой процедурой sp_trace_create текущее определение сеанса;

q      sp_trace_generateevent — позволяет сгенерировать пользовательское событие, которое будет перехвачено в ходе трассировки.

Получить учебные материалы по этому курсу