SQL Server

Мониторинг производительности SQL Server 2008

20.05.2020

1733

Начиная с версии 2008 в сборку SQL Server был добавлен монитор производительности системы — Performance Data Collector (PDC). Новый компонент Management Studio, призванный облегчить мониторинг и настройку производительности экземпляров SQL Server’а конечным пользователям.

Компоненты, устанавливаемые по умолчанию, называют набором системы сбора данных, а именно:

Использование дискового пространства. Сбор данных об использовании дискового пространства в базе данных.
Статистика запросов. Отчеты о статистики запросов, индивидуальный текст запроса, планы запросов, и конкретных запросов.
Мониторинг активности сервера. Собирает статистику использования ресурсов и производительности данных с сервера, операционной системы и SQL сервера.

Преимущества:

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

Ограничения:

Совместимо только с версией SQL Server 2008.
Система сбора данных не отображает информацию о дисковом пространстве в режиме онлайн

Этапы подготовки:

На первом этапе подготовки на сервере должна быть создана папка с правами на чтение/запись для службы SQLSERVERAGENT. Вся техническая информация будет собираться в данной папке, а затем загружаться в базу данных системы мониторинга.
База данных системы мониторинга должна быть создана до того, как вы запустите мониторинг. Данная база данных по сути является обычной базой данных SQL и содержит все данные, полученные с помощью системы мониторинга.
Позаботьтесь заранее о размере дискового пространства. Ожидаемый рост базы данных около 250 — 350 мб в день.
По умолчанию данные очищаются каждые 14 дней. Глубину очистки можно менять в зависимости от заданных требований.
Набор сбора «Занято место на диске» отслеживает рост базы данных и файлов журнала и предоставляет статистику по файлам, такую как средний рост (в мегабайтах) в день. Опрос состояния диска происходит каждые 5 секунд, каждый час данные записываются в базу данных и хранятся в течение 90 дней. Данные интервалы могут быть скорректированы.
Набора сбора «Статистика запросов» собирает данные по статистике запросов, а также тексты отдельных запросов, планы запросов и конкретные запросы. Эти данные в сочетании с системой статистикой и действиями позволяют проводить детализацию углублением ниже уровня сеанса к отдельным запросам. Частота передачи по расписанию — каждые 15 минут, хранение данных в течение 14 дней. Данные интервалы могут быть скорректированы.
Набор сбора «Активность сервера» предоставляет общие сведения об активности SQL Server, использовании ресурсов SQL Server и конфликта между ресурсами SQL Server. Этот набор сбора также дает инкапсулированное представление использования всех системных ресурсов, которые позволяет определить связь проблем производительности с действиями за пределами области SQL Server. Запись статистики активности сервера происходит каждые 60 секунд, для активных сессий и запросов данный интервал составляет 10 секунд
База данных MSDB используется для хранения информации о конфигурации, о времени выполнения, аудита и ведении журнала сбора информации. SSIS пакетов хранятся в MSDB.
Необходимо обязательно установить SQL Server agent.
Служба интеграции SQL Server должна быть запущена, т.к. SSIS пакеты используются для сбора данных. SSIS пакеты также генерируют события во время сбора данных, которые используются для мониторинга и устранения неполадок в процессе сбора.
Data Collector Security. В окне мастера «Configure Data Warehouse Wizard» необходимо сопоставить роли для сборщика данных и учетные записи пользователей. К ним относятся: mdw_admin, mdw_reader и mdw_writer.

mdw_reader — используется для входа пользователям, которым необходимо архивные отчеты;
mdw_writer — роль может загружать и записывать данные в хранилище данных. Поэтому каждая служба SQLServerAgent, используемая на удаленных сборщиках данных, хранит данные в центральном базе данных.
mdw_admin — чтение, запись, обновление и удаление доступа к базе данных. Любая учетная запись пользователя назначенная на роль mdw_admin может изменить схему на mdw-файла и запускать задания по обслуживанию.

Настройка производительности Data Collector

Во-первых, создадим хранилище данных управления

Теперь, давайте настроим хранилище данных управления.

Нажимаем «Next» на экране приветствия

Указываем имя базы данных и место расположения

Присваиваем пользователю роль mdw_admin.

Проверяем конфигурацию. Если все указано правильно, нажимаем «Finish» и переходим к процессу конфигурирования.

Идет процесс конфигурации…

Конфигурация выполнена.

Теперь, после выполнения на сервере запросов к базе данных можно просмотреть отчеты этих выполнений. Для просмотра отчетов разверните вкладку «Management» -> «Data Collection».