Мониторинг и настройка производительности

Мониторинг баз данных выполняется с целью оценки производительности сервера. Эффективный мониторинг подразумевает регулярное создание моментальных снимков текущей производительности для изолирования процессов, вызывающих неполадки, и постоянный сбор данных для отслеживания тенденций роста или снижения производительности. В состав Microsoft SQL Server и операционной системы Microsoft Windows входят программы, позволяющие следить за текущим состоянием базы данных и измерять производительность, если это состояние меняется.

Мониторинг SQL Server позволяет делать следующее.

• Определять возможности увеличения производительности. Например, контролируя время ответа часто используемых запросов, можно определить, требуется ли изменить текст запроса или индексы таблицы.
• Оценивать активность пользователей. Например, контролируя пользователей, которые подключаются к экземпляру SQL Server, можно определить, правильно ли настроены параметры безопасности, и проверить работу приложений и систем разработки. Контролируя выполнение SQL-запросов, можно определить, правильно ли они написаны, и проверить результаты, которые они возвращают.
• Устранять любые проблемы или отлаживать компоненты приложений, например хранимые процедуры.

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

Для эффективного контроля компонентов SQL Server выполните следующие действия.

1. Определите цели мониторинга.
2. Выберите подходящее средство.
3. Определите компоненты, которые нужно контролировать.
4. Выберите метрики для этих компонентов.
5. Осуществите мониторинг работы сервера.
6. Проанализируйте данные.

Ниже подробно описан каждый шаг.

Определение целей мониторинга

Чтобы обеспечить эффективность мониторинга SQL Server, необходимо четко определить его причины. Причинами контроля могут быть следующие.

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

Выбор подходящего средства

Определив цели мониторинга, следует выбрать соответствующие средства. В состав SQL Server и операционной системы Windows входит полный набор средств мониторинга серверов в средах с большим количеством транзакций. Эти средства раскрывают работу экземпляра компонента SQL Server Database Engine или экземпляра служб SQL Server Analysis Services.

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

• Системный монитор, который позволяет собирать и просматривать данные об использовании памяти, диска и процессора в реальном времени.
• Журналы и предупреждения производительности.
• Диспетчер задач.
• Трассировка событий Windows (ETW), которая позволяет отслеживать и регистрировать в журнале события, создаваемые приложениями в пользовательском режиме и драйверами в режиме ядра. Функциональность ETW реализована в операционной системе Windows. С ее помощью можно экспортировать большинство событий SQL Server.

Дополнительные сведения о средствах Windows Server и Windows см. в документации по операционной системе Windows.

SQL Server предоставляет следующие средства мониторинга компонентов SQL Server.

• Трассировка SQL.
• SQL Server Profiler
• Монитор активности SQL Server Management Studio.
• Графическое отображение результатов инструкции Showplan в среде SQL Server Management Studio.
• Хранимые процедуры.
• Консольные команды базы данных (DBCC).
• Встроенные функции.
• Флаги трассировки.

Дополнительные сведения о средствах мониторинга SQL Server см. в разделе Средства контроля и настройки производительности.

Определение компонентов, которые нужно контролировать

На этом шаге требуется определить компоненты SQL Server, которые нужно контролировать. Например, при трассировке сервера при помощи приложения SQL Server Profiler можно собирать данные об определенных событиях. Кроме того, можно исключить ненужные события.

Выбор метрик для наблюдаемых компонентов

Определив компоненты, которые нужно контролировать, укажите метрики. Например, выбрав события трассировки, можно указать, какие именно данные требуются для анализа. Благодаря этому, можно сэкономить ресурсы системы, которые требуются для выполнения трассировки.

Мониторинг работы сервера

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

Анализ данных

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

Ниже описан процесс сбора данных о событиях и работа с ними.

1. Примените фильтры, чтобы ограничить объем собираемых данных о событиях.
   Фильтры позволяют системе собирать данные только о тех событиях, которые имеют отношение к поставленной задаче мониторинга. Например, если требуется проконтролировать медленные запросы, с помощью фильтра можно собрать данные только о тех запросах к конкретной базе данных, которые выполняются более 30 секунд. Дополнительные сведения см. в разделах Как создать фильтр трассировки (Transact-SQL) и Как фильтровать события в трассировке (SQL Server Profiler).
2. Соберите данные о событиях.
   После включения мониторинга собираются данные указанного приложения, экземпляра SQL Server или операционной системы. Например, при мониторинге активности диска с помощью системного монитора собираются и отображаются на экране данные об операциях чтения и записи. Дополнительные сведения см. в разделе Мониторинг использования ресурсов (системный монитор).
3. Сохраните собранные данные о событиях.
   Благодаря сохранению данные можно позже проанализировать и даже воспроизвести в приложении SQL Server Profiler. Данные сохраняются в файл, который впоследствии можно загрузить обратно. Приложение SQL Server Profiler позволяет сохранять данные о событиях в таблицу SQL Server. Возможность сохранения играет важную роль при определении базового уровня производительности. Эти критерии используются для сравнения новых данных о событиях со старыми и позволяют определить, является ли производительность оптимальной. Дополнительные сведения см. в разделе Работа с приложением SQL Server Profiler.
4. Создайте шаблоны трассировки, содержащие параметры сбора событий.
   Шаблоны трассировки включают определения самих событий, данных о событиях и фильтрах, которые применяются для сбора данных. Эти шаблоны применяются для мониторинга определенного набора событий в будущем и не требуют переопределения событий, данных и фильтров. Например, если часто приходится следить за количеством взаимоблокировок и пользователями, которые в них участвуют, можно создать шаблон, определив нужные события, данные о событиях и фильтры, и сохранить его в отдельном файле; впоследствии этот фильтр можно использовать для мониторинга взаимоблокировок. Приложение SQL Server Profiler поддерживает шаблоны трассировки. Дополнительные сведения см. в разделах Как установить определения трассировки по умолчанию (приложение SQL Server Profiler) и Как создать шаблон трассировки (приложение SQL Server Profiler).
5. Проанализируйте собранные данные о событиях.
   Для анализа сохраненные данные загружаются в приложение, с помощью которого они были собраны. Например, трассировку, собранную приложением SQL Server Profiler, можно повторно загрузить в SQL Server Profiler для просмотра и анализа. Дополнительные сведения см. в разделе Просмотр и анализ трассировок с помощью приложения SQL Server Profiler.
   Анализ данных подразумевает определение того, что происходит и почему. В зависимости от типа анализа эти сведения позволяют совершить действия, которые могут увеличить производительность, например добавить память, изменить индексы, исправить проблемы в коде инструкций Transact-SQL и хранимых процедур и т. п. Например, используя помощник по настройке компонента Database Engine, можно проанализировать трассировку, записанную приложением SQL Server Profiler, и получить рекомендации по построению индексов. Дополнительные сведения см. в разделе Настройка физической структуры базы данных.
6. Воспроизведите собранные данные о событиях.
   Возможность воспроизведения позволяет создать копию базы данных, где были собраны данные, и повторить события так, как будто они произошли в реальной системе. Это возможно только в приложении SQL Server Profiler. События можно воспроизводить с первоначальной скоростью, с максимально возможной скоростью (чтобы загрузить систему) или пошагово (чтобы проанализировать состояние системы после каждого события). Анализ событий в тестовой среде позволяет избежать повреждений в производственной системе. Дополнительные сведения см. в разделе Воспроизведение трассировки.

В этом разделе

См. также

Основные понятия

Автоматизация администрирования в масштабах предприятия

Другие ресурсы

Справочник по приложению SQL Server Profiler
Общие сведения о помощнике по настройке ядра СУБД

Справка и поддержка

Получение помощи по SQL Server 2005