Как администратору баз данных SQL Server важно проактивно отслеживать и решать проблемы, связанные с производительностью, до того, как они станут заметными и повлияют на бизнес. Для достижения этой цели существуют различные инструменты и методы сбора метрик производительности SQL Server. В этом блог-посте мы рассмотрим процесс создания централизованной, интегрированной и гибкой системы сбора и анализа данных с использованием компонентов SSIS.
Обзор решения
Решение включает создание пакета ETL (извлечение, преобразование, загрузка), который считывает необработанные данные о производительности из центральной базы данных репозитория, классифицирует данные на основе заранее определенных пороговых значений метрик производительности и загружает их в хранилище данных. Загрузка данных происходит инкрементально и может быть запущена из заданий SQL Server Agent.
Пакет разработан с использованием SSIS 2008, но его можно легко обновить до более новых версий. Модель данных состоит из нескольких измерений и фактов в схеме PerfDWH Db.
Модель данных
Схема PerfDWH Db включает следующие таблицы:
- DimSQLInstances – Измерение для экземпляров SQL
- DimPerfCounters – Измерение для счетчиков производительности и экземпляров счетчиков
- DimServers – Измерение для имен серверов
- DimCollectionTimes – Измерение для наборов времени сбора
- FactPerfCounters – Факт-таблица, содержащая данные о производительности
- LatestSnapshotID – Таблица для хранения ID последних загруженных данных из таблицы PerfCounterData
Развертывание пакета SSIS
Пакет SSIS может быть развернут на экземпляре SQL или в файловой системе. В качестве альтернативы, его можно преобразовать в модель проекта SSIS 2012 и развернуть в каталоге SSIS, который предоставляет гибкие возможности конфигурации и ведения журнала.
Шаги развертывания следующие:
- Скачайте пакет развертывания и распакуйте его содержимое
- Выполните скрипт CreatePerfDWHDb.sql для создания базы данных PerfDWH и ее объектов
- Импортируйте пакет на центральный сервер с помощью SQL Server Management Studio (SSMS)
- Выполните скрипт CreateSecurityObjectsDWH.sql для создания учетной записи и прокси-аккаунтов для SQL Agent
- Выполните скрипт CreatePerfDWH_ETLJobs.sql для создания задания обработки данных
Настройка пакета SSIS
Пакет использует настройки конфигурации на основе переменных среды. Необходимо создать следующие переменные среды:
- PerfCollectionSqlInstance – имя экземпляра SQL, на котором размещены исходная (PerfCollection) и целевая (PerfDWH) базы данных
- PerfCollectionLogFolder – папка для журналов пакета
Примечание: возможно потребуется перезапуск сервера, чтобы переменные среды вступили в силу.
Структура пакета
Пакет состоит из нескольких компонентов, выполняющих определенные задачи:
- Get Last Snapshot ID – Считывает последний загруженный ID из исходной базы данных
- Populate DimServers – Инкрементально заполняет таблицу DimServers
- Populate DimSQLInstances – Инкрементально заполняет таблицу DimSQLInstances
- Populate DimCounters – Инкрементально заполняет таблицу DimCounters
- Populate DimCollectionTimes – Инкрементально заполняет таблицу DimCollectionTimes
- Get latest source data ID – Считывает последний доступный ID из исходной базы данных
- Populate Fact tables – Инкрементально заполняет таблицу FactPerfCounters
- Save last source ID – Сохраняет последний загруженный ID в таблице LatestSnapshotID
Пакет использует фиксированные (тип 1) медленно изменяющиеся измерения для таблиц измерений, так как маловероятно, что значения измерений изменятся после вставки. Факт-таблицы заполняются на основе последнего загруженного ID и классификации метрик производительности.
Заключение
Построение централизованной системы сбора и анализа данных о производительности SQL Server является важным для поддержания оптимальной производительности и проактивного решения проблем, связанных с производительностью. Путем использования компонентов SSIS и следуя шагам развертывания и настройки, описанным в этом блог-посте, администраторы баз данных могут создать надежную систему, которая собирает, классифицирует и анализирует метрики производительности с нескольких серверов.