Мониторинг эффективности MS SQL Server. Практические рекомендации

SQL Profiler - программное средство, используемое для трассировки сервера SQL Server. "Трассировка" - сеанс сбора информации о работе SQL Server 2008

Основное назначение:

SQL Profiler используется администраторами для:

· анализа работы приложения;

· определения оптимальности запросов, направляемых на сервер;

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

· сбора информации о пользовательской активности в течение продолжительного промежутка времени;

· проведения мониторинга работы сервера в режиме реального времени.

Новые возможности:

a. профилировка Analysis Services;

b. профилировка событий Integration Services;

c. возможность при записи информации выполнения команды записывать показания счетчиков из Performance Monitor;

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

f. возможность группировать события в окне профилировщика.

Работа с SQL Server Profiler

1. Запустить SQL Server Profiler - из меню Пускà Программыà SQL Server 2008à Performance Toolsà SQL Server Profiler .

2. В открывшемся окне в меню File выбрать New Trace и подключиться к серверу SQL Server 2008, работу которого мы будем отслеживать.

3. Настроить параметры сеанса в окне Trace Properties , которое открывается автоматически переда началом сеанса трассировки (см. рис. 8.1).

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

a. На вкладке General выбрать в списке Use the template наиболее подходящий шаблон. Выбор шаблона производится при помощи меню File à Templates в SQL Server Profiler. Изначально в вашем распоряжении - восемь шаблонов:

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

2). SP_Counts - сбор информации о запускаемых на выполнение хранимых процедурах и функциях с сортировкой по именам;

3). TSQL - сбор информации о всех командах Transact-SQL, запускаемых на выполнение на сервере, с указанием идентификатора пользовательских процессов и времени запуска;

4). TSQL_Duration - аналогичен предыдущему шаблону, но вместо информации о времени запуска команды TSQL записывается время, которое потребовалось на ее выполнение;

5). TSQL_Grouped - помимо информации о коде команды Transact-SQL и времени ее запуска, записывается также информация о имени приложения, учетной записи пользователя в ОС и учетной записи пользователя, которая была использована для подключения;

6). TSQL_Replay - запись максимально подробной информации о выполняемых командах Transact-SQL;

7). TSQL_SPs - помимо записи информации о начале запуска хранимой процедуры (SP:Starting) регистрируется информация о выполнении каждой из команд хранимой процедуры (SP:StmtStarting);

8). Tuning - используется для сбора информации необходимой Database Tuning Advisor.

b. На вкладке General если необходимо указать место сохраннения трассировочной информации:

1). Информация трассировки может быть запротоколирована в файл (по умолчанию размером 5 Мбайт):

· параметр Enable File Rollover определяет, будет ли при заполнении одного файла автоматически создаваться следующий. Имя следующего файла будет таким же, как и имя предыдущего, но к его имени будет добавлен номер (1, 2, 3 и т.п.)

· параметр Server processes trace data можно использовать для увеличения надежности записи информации трассировки. После установки этого флажка обработкой информации трассировки будет заниматься сервер.

2). Информация трассировки может быть сохранена в таблице SQL Server. Таблица с нужным набором столбцов будет создана автоматически.

3). С помощью параметра Enable Trace Stop Time можно указать время, когда трассировка будет отключена автоматически.

c. На вкладке Events Selection определить параметры сбора информации. В таблице на этой вкладке вы должны выбрать требуемые события (в строках) и информацию (в столбцах), которая будет для них записываться. Для отображения всех строк и столбцов, нужно установить флажки Show All Events и Show All Columns .

1). с помощью кнопки Column Filters (Фильтры столбцов) настроить фильтры на сбор необходимой информации (отслеживать действия, выполняемые в определенной БД, или определенным приложением, или определенным пользователем) – Like или Not Like ;

2). при помощи кнопки Organize Columns (Организовать столбцы) настроить порядок столбцов для отображения или записи в профилировщике с возможностью группировки данных - раздел Group.

4. После задания всех параметров трассировки нажать на кнопку Run (Запустить) (см. рис. 8.2)

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

В верхней части окна отображаются события, происходящие на сервере, а в нижней части - приводится подробная информация по каждому событию (например, код команд SQL).

Возможности, доступные в окне трассировки:

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

2. Если в список Group был помещен только один столбец, то у вас появляется возможность использовать режим отображения Aggregated View (см. рис. 8.3). Этот режим включается при помощи команды Aggregated View из того же меню View .

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

3. Иы можете открывать в профилировщике события, сохраненные в файлах и таблицах трассировки. Также существует возможность повторять запротоколированные операции, с помощью меню Replay ;

4. Вы можете связывать информацию трассировки с показателями счетчиков производительности System Monitor. Для этого:

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

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

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

Что Вы узнаете из этой статьи?

Предназначение инструмента трассировки SQL Profiler
Как отследить текст запроса к СУБД, в который транслируется запрос 1С
Настройки фильтров трассировки
Как выполнить персональную настройку SQL Profiler

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

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

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

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С.

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

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

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

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

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

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

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

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

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

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

Бурмистров Андрей

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

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

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

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

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

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

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

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

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

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

В предыдущих выпусках (см. КомпьютерПресс № 1, 3-5, 7, 9’2006) мы рассмотрели вопросы, касающиеся перехода на SQL Server 2005 путем миграции или обновления, а также основных сценариев использования SQL Server 2005. В настоящей статье речь пойдет о различных методах оптимизации запросов и о возможных подходах к решению задач, связанных с поиском проблем производительности.

SQL Server предоставляет сервисы, которые выполняются в динамической, постоянно изменяющейся среде. Поэтому регулярный мониторинг работы сервера позволяет определять проблемы еще на этапе их возникновения и оперативно принимать меры по их устранению. С накоплением статистики появляется понимание основных тенденций работы сервера. Регулярный сбор данных - даже в тех случаях, когда сервер работает без каких-либо проблем, - позволит создать так называемый базовый критерий производительности (server performance baseline), который может служить в качестве эталона при дальнейших замерах работы сервера.

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

Базовый эталон может использоваться для определения времени пиковой загрузки сервера и времени его простоя (peak и off-peak hours), времени отклика запросов и пакетов команд, времени выполнения процедур создания резервных копий и восстановления данных и т.п.

Производительность запросов

Производительность запросов следует рассматривать с двух точек зрения - ресурсов, применяемых для выполнения того или иного запроса (к ресурсам относятся объекты, к которым мы обращаемся в процессе выполнения запроса, заблокированные объекты и т.п.), и времени, затрачиваемого на выполнение запроса, - чем меньше время, необходимое для выполнения запроса, тем ниже вероятность того, что в процессе запроса мы заблокируем другие запросы и транзакции.

В состав SQL Server 2005 входят два основных средства для измерения производительности запросов - Performance Monitor и SQL Server Profiler. Помимо этого для получения информации о выполнении запросов можно использовать конструкции языка T-SQL из группы SET STATISTICS - SET STATISTICS IO, SET STATISTICS PROFILE и SET STATISTICS TIME - и динамические представления (Dynamic Management Views). Далее мы рассмотрим применение Performance Monitor и SQL Server Profiler более подробно.

Использование Performance Monitor

Утилита Performance Monitor применяется для анализа производительности аппаратных и программных ресурсов, включая память, использование сети, время работы процессора, а также информацию, относящуюся к работе SQL Server и других программных продуктов - Microsoft Message Queuing (MSMQ), Microsoft .NET Framework и Microsoft Exchange Server. В частности, можно применять Performance Monitor для мониторинга таких ресурсов SQL Server, как блокировки и транзакции.

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

В меню Start выбрать команду Run и в диалоговой панели Run ввести perfmon . В результате будет запущена консоль Performance Microsoft Management Console (MMC), отображающая графическое представление ряда счетчиков производительности.
В окне System Monitor нажать правую кнопку на графике и выбрать команду Add Counters .
В диалоговой панели Add Counters выбрать интересующий нас компьютер и объект для мониторинга. Объекты, относящиеся к SQL Server, имеют соответствующий префикс.
Для выбора счетчиков необходимо выбрать либо опцию All counters , либо один или несколько счетчиков из раскрывающегося списка (рис. 1).

После этого следует выбрать экземпляры базы данных или включить опцию All Instances .
Нажатие кнопок Add и Close завершает добавление счетчиков.

После того как интересующие нас счетчики добавлены, мы можем либо просматривать их значения в виде графика, либо сохранять их в trace-файле (рис. 2).

В последнем случае необходимо раскрыть элемент Performance Logs and Alerts и щелкнуть по команде New Log Settings . В диалоговой панели New Log Settings необходимо задать имя протокола и нажать кнопку OK. Далее в диалоговой панели CounterLogName мы выбираем команду Add Counters . Добавление интересующих нас счетчиков происходит так же, как было описано выше. Обратите внимание на то, что большое количество счетчиков может повлиять на производительность системы. По завершении добавления счетчиков нажмите кнопку Close .

На вкладке General в разделе Sample data every можно задать частоту сэмплинга (снятия данных). Рекомендуется начать со средней частоты, например раз в 5 мин, а затем, при необходимости, уменьшить или увеличить ее. Обратите внимание на то, что чем короче интервал сэмплинга, тем больше требуется системных и дисковых ресурсов. Необходимо помнить, что интервалы короче, чем период квантования счетчика, также могут привести к неверному отображению данных. На вкладке Log Files можно сконфигурировать свойства файла-протокола, а на Schedule задается расписание мониторинга. Нажатие кнопки OK приводит к созданию протокола на начало сбора данных (рис. 3).

Для ручного завершения протоколирования нужно нажать правую кнопку на элементе Counter Logs и выбрать команду Stop.

Для просмотра собранных в файле протокола данных в Performance Monitor нужно выбрать элемент System Monitor , в панели задач - команду View Log Data , а в диалоговой панели System Monitor Properties на вкладке Source указать имя файла протокола.

Наиболее часто применяемые счетчики Performance Monitor, информацию из которых можно использовать для определения проблем, связанных с производительностью, разделены на следующие категории:

доступ к данным - для аудита методов доступа к данным, применяемым самим SQL Server, используется объект SQLServer:Access Methods ;
статистика - для мониторинга компиляции и рекомпиляции запросов применяется объект SQLServer:SQL Statistics . Он предоставляет информацию о том, как быстро и эффективно SQL Server обрабатывает запросы;
транзакции - для определения числа транзакций в секунду используются счетчики, относящиеся к объектам SQLServer:Databases и SQLServer:Transactions ;
блокировки - для аудита блокировок SQL Server, устанавливаемых на определенные типы ресурсов, применяется объект SQLServer:Locks (табл. 1).

Таблица 1. Счетчики SQL Server Profiler

Название счетчика	Описание

	Подсчитывает число сканирований диапазонов (range scans) для индексов в секунду
	Подсчитывает число полных сканирований, выполненных за последнюю секунду
Index Searches/sec	Подсчитывает число поисков по индексу за последнюю секунду
Table Lock Escalations/sec	Подсчитывает число блокировок для таблицы
Worktables Created/sec	Подсчитывает число рабочих таблиц, созданных за последнюю секунду

Batch Requests/sec	Подсчитывает число пакетов команд Transact-SQL в секунду. Большое число пакетов означает хорошую пропускную способность
SQL Compilations/sec	Подсчитывает число компиляций запросов в секунду. Значение этого счетчика должно стать практически постоянным после выполнения пользователем основных действий
SQL Re-Compilations/sec	Подсчитывает число рекомпиляций запросов в секунду

Объект SQLServer:Databases. Счетчик Transactions/sec	Подсчитывает число транзакций, запущенных в базе данных за последнюю секунду
Объект SQLServer:Transactions. Счетчик Longest Transaction Running Time	Вычисляет число секунд с начала транзакции, которая была активна дольше, чем любая другая текущая транзакция. Если этот счетчик показывает очень длинную транзакцию, используйте системную хранимую процедуру sys.dm_tran_active_transactions() для получения информации о данной транзакции
Объект SQLServer:Transactions. Счетчик Update conflict ratio	Подсчитывает процент транзакций, применяющих изоляцию образов (snapshot isolation) для решения возникающих конфликтов при обновлении данных за последнюю секунду

Average Wait Time (ms)	Подсчитывает среднее время ожидания для каждого запроса на блокировку, вызвавшего ожидание
Lock Requests/sec	Подсчитывает число блокировок и преобразований блокировок в секунду
Lock Wait Time (ms)	Подсчитывает суммарное ожидание для блокировок за последнюю секунду
	Подсчитывает число запросов на блокировку в секунду, которые привели к ожиданию

Вторая утилита, которой можно воспользоваться для измерения производительности запросов, - это SQL Server Profiler. Далее мы рассмотрим основные способы ее использования.

Использование SQL Server Profiler

Утилита SQL Server Profiler служит для измерения производительности отдельных запросов и запросов, входящих в состав хранимых процедур и пакетов команд на языке Transact-SQL. С ее помощью можно собирать информацию о производительности, включая время, затраченное на выполнение отдельной команды, время блокировки для выполнения команды, а также план выполнения (execution plan).

Для создания нового профиля необходимо выполнить ряд следующих шагов:

Запустить SQL Server Profiler (Microsoft SQL Server 2005 => Performance Tools => SQL Server Profiler ).
В меню File выбрать команду New Trace .
В диалоговой панели Connect to Server выбрать необходимый сервер и нажать кнопку Connect .
В диалоговой панели Trace Properties нужно задать имя профиля (Trace name) , а в списке Use the template выбрать один из доступных шаблонов или Blank , если шаблон не используется.
Для сохранения результатов профилирования необходимо выбрать опцию Save to file для записи данных в файл и указать максимальный размер файла (значение по умолчанию - 5 Мбайт), опционально можно включить опцию Enable file rollover для автоматического создания нового файла по достижении указанного размера профиля. Вторая возможность - сохранение данных в таблице базы данных (Save to table) - опция Set maximum rows позволяет задать максимальное число записей.
Для задания времени завершения трассировки можно использовать опцию Enable trace stop time (рис. 4).

Для задания событий и колонок данных для профиля требуется выполнить следующие шаги:

В диалоговой панели Trace Properties перейти на вкладку Events Selection .
Добавить или удалить события из профиля, используя таблицу классов событий.
Для получения списка всех доступных событий включите опцию Show all events (рис. 5).

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

Таблица 2. События SQL Server Profiler

Название события	Описание

	Данное событие происходит по завершении выполнения вызова удаленной процедуры
	Данное событие осуществляется по завершении выполнения хранимой процедуры
SP:StmtCompleted	Данное событие происходит по завершении выполнения одной из команд языка Transact-SQL внутри хранимой процедуры

SQL:StmtCompleted	Данное событие осуществляется по завершении выполнения команды на языке Transact-SQL
SQL:BatchCompleted	Данное событие происходит по завершении выполнения пакета команд на языке Transact-SQL

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

Как определить наличие блокировок

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

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

использовать утилиту SQL Server Management Studio Activity Monitor, которая показывает информацию о процессах, блокировках на уровне процесса и на уровне объекта. Для доступа к Activity Monitor в SQL Server Management Studio нужно выбрать элемент Management , а в нем - Activity Monitor и дважды щелкнуть по этому элементу. Activity Monitor позволяет просматривать:

Заблокированные объекты для каждого процесса - для определения запроса, приведшего к появлению блокировки, применяйте идентификатор процесса Server Process ID (SPID) на странице Process Info ,

Процессы, заставляющие другие процессы находиться в состоянии ожидания, - для выявления таких процессов воспользуйтесь колонкой Blocked By на странице Process Info (рис. 6);

применять SQL Server Profiler для получения отчета о заблокированных процессах - в этом списке отображается информация о процессах, которые оставались заблокированными дольше указанного временно

В этой статье собраны несколько материалов, опубликованных в рассылке "MS SQL Server - дело тонкое…" и посвящённых проблемам мониторинга эффективности MS SQL Server, методам выяснения причин возникновения аппаратных проблем и их разрешения. Кроме того, Вашему вниманию будут предложены несколько полезных, практических рекомендаций и предостережений.

Если Ваш сервер баз данных чересчур интенсивно использует I/O, можно изменить значение параметра операционной системы I/O Page Lock Limit, который может увеличить эффективную норму чтения/записи данных операционной системой на жесткий диски.
Сначала, выполните эталонный тест I/O для вашей обычной загрузки сервера. Затем, в regedit.exe откройте ключ:

HKLM\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement\IoPageLockLimit

Смысл Ваших действий состоит в пошаговом подборе значений этого ключа до наиболее оптимального, с точки зрения изменений результатов эталонного тестирования, значения.
В этом ключе операционная система считывает максимальное число байт, которые она можете использовать для операций I/O. По умолчанию установлено значение 0, которому соответствует 512КБ. Увеличивайте это значение по шагам, каждый раз прибавляя по 512КБ (например: "512", "1024", и т.д.), и выполняйте после каждого изменения эталонное тестирование вашей системы. Увеличивать этот параметр есть смысл только до тех пор, пока вы наблюдаете увеличение пропускной способности операций ввода – вывода, которое может проявляться в снижении временных затрат на стандартные дисковые операции. Когда Вы перестанете наблюдать существенное улучшение, возвратитесь в редактор реестра и уничтожьте последнее приращение.

Предостережение : Есть ограничение на максимальный размер значения этого ключа. Если Вы имеете 16 МБ ОЗУ, не устанавливайте IoPageLockLimit более 2048 байт; для 32МБ ОЗУ, не превышайте 4096 байт, и так далее.

Важное замечание:

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