Сбор данных о производительности SQL Server является важным для обеспечения правильной работы системы. Существует несколько способов и инструментов для сбора и анализа этих данных. В этом блоге мы рассмотрим, как построить OLAP-куб для анализа производительности SQL Server.
Обзор решения
В этой статье мы покажем, как построить OLAP-куб на основе базы данных PerfDWH. Мы создадим задания для обработки куба и будем использовать отчеты PivotTable и функции PivotChart в Excel для анализа данных о производительности.
Развертывание куба анализа производительности SQL Server
Для развертывания куба мы будем использовать скрипты XMLA для его развертывания в экземпляре Analysis Services. В качестве альтернативы его можно развернуть с помощью проекта SSAS, который включен в пакет развертывания. Шаги развертывания следующие:
- Запустите SQL Server Management Studio (SSMS) и подключитесь к экземпляру Analysis Services, который будет размещать куб.
- Щелкните на имени экземпляра Analysis Services и выполните скрипт “PerformanceCountersAnalysis_Script.xmla”, чтобы создать куб PerformanceCountersAnalysis.
- В Обозревателе объектов разверните узлы ‘Roles’ внутри ‘PerformanceCountersAnalysis’ и дважды щелкните на ‘Users’.
- Откройте вкладку ‘Membership’, удалите любые существующие сиротские идентификаторы и добавьте пользователей или группы, которым требуется полный доступ к этому кубу. Обратите внимание, что учетная запись пользователя, используемая для обработки этого куба, также должна быть добавлена в эту роль.
- Разверните узел ‘Data Sources’ в Обозревателе объектов и дважды щелкните ‘PerfAnalysisDS’, чтобы настроить источник данных.
- Выберите провайдер, имя сервера и имя базы данных, указывающие на вашу базу данных PerfDWH.
- Проверьте подключение и нажмите ‘OK’, чтобы подтвердить выбор.
- Чтобы протестировать обработку куба, щелкните правой кнопкой мыши на имени базы данных SSAS (PerformanceCountersAnalysis) и выберите ‘Process’.
- Убедитесь, что обработка куба успешно завершена и закройте диалоговое окно.
- Откройте скрипт “CreateSecurityObjectsSSAS.sql”, укажите имя домена, имя пользователя и пароль для создания учетных данных, которые будут использоваться заданиями обработки куба, и выполните его для создания учетных данных и учетных записей прокси для SQL Server Agent.
- Откройте скрипт “CreatePerfCubeProcessJobs.sql”, укажите значение частоты обработки для локальной переменной @ProcessFrequency_Min и выполните его для создания ежедневных инкрементных заданий обработки куба. Скрипт также создаст еженедельное задание полной обработки куба.
Внутренности куба
Куб PerformanceCountersAnalysis содержит одну факт-таблицу, связанную с 4 измерениями в виде звездной схемы. Измерения включают SQL-инстансы, счетчики производительности, серверы и время сбора. Куб также включает иерархии времени и иерархии на измерении счетчиков производительности для удобного анализа.
Пользовательские расчеты куба
Помимо основных мер куба, были созданы пользовательские расчеты MDX для анализа данных о производительности за продолжительные периоды времени. Эти расчеты включают среднее (по временному периоду), среднее за предыдущий временной период, средний прирост, скользящее среднее, прирост за последние 3 месяца, длительность превышения порога и длительность превышения критического значения.
Настройка отчетов
Пакет развертывания включает файл шаблона Excel для анализа. Чтобы настроить отчеты:
- Скопируйте электронную таблицу на компьютер с прямым доступом к кубу PerformanceCountersAnalysis и откройте ее.
- Перейдите на вкладку “Данные” и нажмите “Соединения”.
- В окне “Соединения книги” вы увидите два объекта соединения: куб PerformanceCountersAnalysis и PerfCollectionErrors.
- Для каждого выбранного соединения нажмите “Свойства”, затем откройте вкладку “Определение” и введите правильные имена экземпляра SSAS или SQL соответственно.
Будущее развитие
В будущем вы можете рассмотреть возможность добавления функциональности оповещения в систему отчетности. Оповещения могут быть отправлены во время сбора данных о производительности или этапа ETL, когда каждый счетчик проверяется на соответствие предварительно настроенным порогам. Кроме того, вы можете исследовать расширенные возможности бизнес-аналитики, публикуя эти отчеты на SharePoint для практически реального отчета о производительности.
Важно отметить, что производительность этой системы отчетности может варьироваться в зависимости от объема данных и количества серверов. Для улучшения производительности вы можете создать новые разделы на кубе на основе месячных или годовых диапазонов и создать пользовательские агрегации.
Следуя этим шагам, вы можете построить OLAP-куб для анализа производительности SQL Server и получить ценные сведения о производительности ваших систем.