Сбор статистики производительности и вывод результатов в SSMS в виде пользовательских отчетов

Источник: habrahabr

tion

Итак, у нас появилась задача по сбору статистики производительности SQl-сервера и дальнейший анализ результатов. Для чего это надо? Например, вы хотите перенести с одного сервера на другой некую базу данных, и вам надо просчитать производительность до переноса и после.

Чуть подробнее. Есть некий сервер, назовем его server-sql-001, на котором крутятся базы данных CRM и ERP компании. И есть еще сервер, назовем его server-sql-1c, на котором крутятся БД 1С. Server-sql-001 - это новый брендовый сервер, с современными характеристиками и т.д. А server-sql-1c - это довольно средний по современным меркам сервер. И вот, вся бухгалтерия жаждет переехать на server-sql-001, дабы наступило им счастье, увеличилась производительность и скорость работы отличной желтой программы. Вот и встала задача проверить, а правда ли наступит всем счастье? Или же после переезда пострадает производительность и CRM и ERP и 1С? Вот один из примеров, по которым нам необходимо собрать и проанализировать статистику.

Да, можно средствами системного монитора собрать всю необходимую статистику в excel-файл, затем построить графики и т.д. Но что если сбор статистики продолжает неделю, месяц? А еще с интервалом 10 секунд? Удобно будет работать с таким файлом? Ну смотрите сами.

1. Создание DB.

Первое, что нам надо сделать - это создать базу данных. Запускаем SQL Server Management Studio, подключаемся к нужному нам серверу.

После подключения к инстансу нажимаем Файл - Создать - Создать запрос в текущем соединении (или просто Ctrl + N). В появившемся окне пишем такой код:

CREATE DATABASE [s_statistic] CONTAINMENT = NONE ON PRIMARY ( NAME = N's_statistic', FILENAME = N'D:\data\s_statistic.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N's_statistic_log', FILENAME = N'L:\log\s_statistic_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO

Если не нравится создавать базы через запросы, то делаем следующее. После подключения к инстансу, нажимаем правой кнопкой на Базы данных - Создать базу данных.

Выбираем месторасположение базы данных и нажимаем ОК. Для наших целей, все остальные параметры можно оставить по умолчанию.
Цель - создать базу данных. А каким способом - это дело вкуса, кому как нравится.

2. Создание источника ODBC.

Итак, базу создали, теперь пора создать источник, куда Системный монитор будет писать свою статистику. Выбираем Пуск - Администрирование - Источники данных ODBC (для Windows Server 2012 проще нажать Win + q, набрать odbc и нажать enter).

Нас интересует вкладка System DSN (Системный DSN). Нажимаем Add, выбираем SQL Server и нажимаем Finish. В появившемся Мастере создание нового источника пишем имя, например, statistic, по желание можем указать описание и указываем, к какому sql-серверу мы хотим подключиться, в данном случае server-sql-001. (Советую написать имя sql-сервера вручную, вместо того, чтобы выбирать из списка. Причина проста, если в среде много SQL инстансов, то вывод списка доступных SQL-сервером займет некоторое время). После ввода данных, нажимаем Далее. Здесь можно выбрать какой способ аутентификации выбрать. Всё остальное оставляем как есть. Нажимаем Далее и вот тут надо выбрать подключение в созданной нами. Ставить галочку Использовать по умолчанию базу данных и выбираем нашу s_statistic. Нажимаем Далее, в следующем окне вносить изменения по желанию. Нажимаем Готово, проверяем источник данных, нажимаем ОК и закрываем консоль управления источниками данных ODBC.

3. Создание и настройка группы сборщиков данных.

Третья часть нашего балета начинается с запуска Управления компьютером - раздел Performance (Производительность) - Data Collector Set (Группы сборщиков данных), правой кнопкой на User Defined (Особые) - New Data Collector Set (Создать Группа Сборщиков данных). Даём имя новой группе сборщиков данных, выбираем Создать вручную - Далее - Создать журналы данных (Счетчик производительности) - Далее - Добавить. И вот тут начинает микро ад! Количество всевозможных счетчиков просто зашкаливает. Я остановил свой выбор на нескольких:

• \Память\% использование выделенной памяти
• \Процесс(sqlserv)\% загруженности процессора
• \Процессор(_Total)\% загруженности процессора
• \Физический диск(_Total)\% активности диска при чтении
• \Физический диск(_Total)\% активности диска при записи

Вы можете выбрать всё что угодно или всё что необходимо в данный момент. Интервал у меня выставлен 10 секунд. Нажимаем Далее - при необходимости меняем корневую папку - Далее - Пользователь по умолчанию - Готово. Итак, группу создали. Видим, что счетчик DataCollector01 уже создан и заходим в его свойства для того, чтобы выбрать формат журнала SQL. Снизу появляется возможность выбора источника данных, где мы и выбираем созданный нами statistic. Нажимаем ОК, затем правой кнопкой по группу сборщиков данных statistic - Пуск.

Отлично, сбор статистики начался! Кстати, если вылетает ошибка ServelAllConnect%1 (или как-то так), то посмотрите в журнале событий. Там, как ни странно, очень подробно описана ошибка. Чаще всего она связана с правами пользователем, от которого собирается статистика.

4. Создание пользовательского отчета.

А вот теперь приступаем к самому интересному! Создание пользовательского отчета для SSMS.
Для начала проверим, идет ли запись отчета. Запустим SSMS и создадим такой запрос:

use [s_statistic] go select CounterName, CounterDateTime, CounterValue from dbo.CounterData CDT join dbo.CounterDetails CD on CD.CounterID=CDT.CounterID

В результате мы получим это:

Запускаем SQL Server Business Intelligence Development Studio (в SQL 2012 он называется SQL Server Data Tools). Выбираем Файл - Новые проект - Проект сервера отчетов. В правой части программы нажимаем правой кнопкой Общие источники данных и выбираем Добавить новый источник данных.

Называем как нам больше нравится, а в строку соединения пишем Data Source=server-sql-001;Initial Catalog=s_statistic. Либо нажимаем правка и выбираем в свойствах подключение имя сервера, вход на сервер и базу данных.

Проверяем подключение и нажимаем ОК. Переходимн а вкладку Учетные данные и указываем там необходимые. Нажимаем ОК.
Ниже нажимаем правой кнопкой Отчеты - Добавить новый отчет. В данном случаем новую страницу пропускаем, а на второй страницы пишем вот такой, нам уже знакомый, код:

select CounterName, CounterDateTime, CounterValue from dbo.CounterData CDT join dbo.CounterDetails CD on CD.CounterID=CDT.CounterID

Нажимаем Далее, выбираем тип отчета Матричный - Далее. В поле Столбцы ставит CounterName, в поле Строки CounterDateTime, в поле подробности CounterValue и можно нажимать Готово. Либо нажимаем далее и выбираем стиль матрицы. Отлично, отчет создан.
Теперь нам остается только привести его в тот внешний вид, который нам нравится. Т.к. таблица нам не особо нужна, я её удаляю. Вместо ней мы добавляем диаграмму (график). В данные диаграммы указываем:

• Группы рядом - CounterName
• Группы категория - CounterDateTime
• Значения - CounterValue

Сохраняем наш проект и готово. Теперь запускаем SSMS, нажимаем правой кнопкой на нашей базе s_statistic - Отчеты - Пользовательские отчеты - находим наш отчет - Открыть.

И вот наш результат:

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

Ссылки по теме