Microsoft SQL Server 2005 — методы оптимизации запросов

Алексей Федоров

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

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

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

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

В предыдущих выпусках (см. КомпьютерПресс № 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, как блокировки и транзакции.

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

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

Рис. 1

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

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

Рис. 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).

Рис. 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

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

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

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

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

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

Рис. 4

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

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

Рис. 5

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

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

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

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

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

• использовать утилиту 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);

Рис. 6

• применять SQL Server Profiler для получения отчета о заблокированных процессах — в этом списке отображается информация о процессах, которые оставались заблокированными дольше указанного временного периода. Системная хранимая процедура sp_configure может использоваться для настройки данных, отображаемых в отчете;
• отслеживать ситуации, когда одна и та же процедура выполняется за разное время, — возможно, причиной этого являются лишние блокировки и ожидание их снятия;
• идентифицировать уровень изоляции транзакций для процедуры или пакета, внутри которого выполняется процедура. Чем более «агрессивный» уровень изоляции установлен, тем выше вероятность появления блокировок. 

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

КомпьютерПресс 3'2007