Наблюдение за компонентами SQL Server

Область применения: SQL Server

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

Чтобы эффективно отслеживать любой компонент SQL Server, выполните следующие действия.

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

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

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

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

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

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

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

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

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

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

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

• Обзор расширенных событий
• Трассировка SQL
• SQL Server Profiler;
• Распределенное воспроизведение SQL Server
• Монитор активности
• Графический план showplan SQL Server Management Studio
• Системные хранимые процедуры
• Консольные команды базы данных (DBCC)
• Системные динамические административные представления
• Что такое функции базы данных SQL?
• Флаги трассировки

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

Определение компонентов, подлежащих мониторингу

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

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

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

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

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

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

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

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

1. Примените фильтры, чтобы ограничить объем собираемых данных о событиях.

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

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

   Дополнительные сведения о фильтрации трассировки SQL см. в разделе "Настройка фильтра трассировки (Transact-SQL) и фильтрация событий в трассировке (SQL Server Profiler)".

2. Соберите данные о событиях.

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

3. Сохраните собранные данные о событиях.

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

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

   Данные событий трассировки SQL можно даже воспроизвести с помощью программы распределенное воспроизведение или профилировщика SQL Server. SQL Server Profiler позволяет сохранять данные событий в файле или таблице SQL Server. Дополнительные сведения см. в статье Шаблоны и разрешения приложения SQL Server Profiler.

4. Создайте шаблоны трассировки, содержащие параметры сбора событий.

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

   Определения сеанса расширенных событий — это шаблон, который можно включить в скрипт и использовать повторно. Сведения о создании сеансов и управлении ими см. в статье Управление сеансами событий в обозревателе объектов. Профилировщик XEvent Studio уже предоставляет шаблоны, готовые к использованию. Дополнительные сведения см. в статье Использование профилировщика XEvent для SSMS.

   Sql Server Profiler использует шаблоны трассировки для этой цели. Дополнительные сведения см. в разделе Set Trace Definition Defaults (SQL Server Profiler) and Create a Trace Template (SQL Server Profiler).

   Совет

   Определение трассировки SQL можно преобразовать в сеанс расширенных событий. Дополнительные сведения см. в статье Преобразование существующего скрипта трассировки SQL в сеанс расширенных событий.

5. Проанализируйте собранные данные о событиях.

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

   Например, записанную трассировку расширенных событий можно перезагрузить в SQL Server Management Studio для просмотра и анализа. Дополнительные сведения см. в разделе "Просмотр данных о событиях" в SQL Server Management Studio.

   Данные трассировки SQL можно перезагрузить в SQL Server Profiler для просмотра и анализа. Дополнительные сведения см. в статье Просмотр и анализ трассировок с помощью приложения SQL Server Profiler.

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

6. Воспроизведите собранные данные о событиях (необязательно).

   Возможность воспроизведения позволяет создать копию базы данных, в которой были собраны данные, и повторить события, как будто они произошли в реальной системе. Эта возможность доступна только с помощью программы распределенное воспроизведение или SQL Server Profiler. События можно воспроизводить с первоначальной скоростью, с максимально возможной скоростью (чтобы загрузить систему) или пошагово (чтобы проанализировать состояние системы после каждого события). Анализ событий в тестовой среде позволяет избежать повреждений в производственной системе. Дополнительные сведения см. в статье Воспроизведение трассировок.