Pull to refresh

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

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

image

Чуть подробнее. Есть некий сервер, назовем его 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, подключаемся к нужному нам серверу.

image

После подключения к инстансу нажимаем Файл – Создать – Создать запрос в текущем соединении (или просто 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


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

image

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

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). Выбираем Файл – Новые проект – Проект сервера отчетов. В правой части программы нажимаем правой кнопкой Общие источники данных и выбираем Добавить новый источник данных.

image

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

image

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

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 — Отчеты – Пользовательские отчеты – находим наш отчет – Открыть.

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



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

Articles