Мониторинг Microsoft SQL Server «на коленке»

Было решено отправить заявку в путешествие сквозь шестерни бюрократического механизма и делать свой, временный мониторинг.

Для начала создадим БД и объекты, необходимые для сбора метрик производительности SQL-сервера.

Для простоты, я не стал в скрипте указывать опции создания БД:

create database monitor -- Создаем БД
GO
use monitor
GO
create table perf_counters -- Создаем таблицу, куда будем записывать данные по счетчикам
(
	collect_time datetime,
	counter_name nvarchar(128),
	value bigint
)
GO
CREATE CLUSTERED INDEX cidx_collect_time -- Индекс, чтобы потом было быстрее делать select 
ON perf_counters 
(
	collect_time
)
GO

Значения счетчиков производительности будем забирать из системного представления sys.dm_os_performance_counters. В скрипте описаны самые популярные и жизненно необходимые счетчики, естественно, список можно расширить. Хотелось бы пояснить по поводу CASE’ов. Счетчики, которые измеряются в «что-то»/секунду — инкрементальные. Т.е. SQL сервер каждую секунду прибавляет текущее значение счетчика к уже имеющемуся. Чтобы получить среднее текущее значение нужно значение в представлении делить на аптайм сервера в секундах. Узнать аптайм можно запросом:

select DATEDIFF(SS, (select create_date from sys.databases where name = 'tempdb'), getdate())

Т.е. найти разницу между текущим моментом и временем создания tempdb, которая, как известно, создается в момент старта сервера.

Метрику Granted Workspace Memory (KB) сразу перевожу в мегабайты.

Процесс сбора оформим в виде процедуры:

CREATE procedure sp_insert_perf_counters
AS
	insert into perf_counters
	select	getdate() as Collect_time, 
			Counter = CASE	WHEN counter_name = 'Granted Workspace Memory (KB)' then 'Granted Workspace Memory (MB)'
							ELSE rtrim(counter_name) END, 
			Value =	CASE	WHEN counter_name like '%/sec%'
										then cntr_value/DATEDIFF(SS, (select create_date from sys.databases where name = 'tempdb'), getdate())
							WHEN counter_name like 'Granted Workspace Memory (KB)%' then cntr_value/1024
							ELSE cntr_value
							END
	from sys.dm_os_performance_counters where 
	counter_name = N'Checkpoint Pages/sec' or
	counter_name = N'Processes Blocked' or
	(counter_name = N'Lock Waits/sec' and instance_name = '_Total') or
	counter_name = N'User Connections' or
	counter_name = N'SQL Re-Compilations/sec' or
	counter_name = N'SQL Compilations/sec' or
	counter_name = 'Batch Requests/sec' or
	(counter_name = 'Page life expectancy' and object_name like '%Buffer Manager%') or
	counter_name = 'Granted Workspace Memory (KB)'
GO

Далее создадим процедуру, которая будет выбирать данные из нашей логовой таблицы. Параметры end и start задают временной интервал, мониторинг ms sql за который мы хотим увидеть значения. Если параметры не заданы, выводить информацию за 3 последних часа.

create procedure sp_select_perf_counters
	@start datetime = NULL,
	@end datetime = NULL
as
	if @start is NULL set @start = dateadd(HH, -3, getdate())
	if @end is NULL set @end = getdate()
	select
		collect_time,
		counter_name,
		value
	from monitor..perf_counters
	where collect_time >= @start
	and collect_time <= @end
go

Завернем sp_insert_perf_counters в задание SQL-агента. С частотой запуска — раз в минуту.
Скрипт создания джоба я пропущу, чтобы не захламлять текст. В конце выложу все в виде одного скрипта.

Забегая вперед, скажу что дело было в том числе и из-за банальной нехватки оперативной памяти, поэтому сразу приведу скрипт, позволяющий посмотреть «борьбу» БД за буфферный пул. Создадим табличку, куда будем складывать данные:

CREATE TABLE BufferPoolLog(
	

[datetime],

[nvarchar](128), [Size] [numeric](18, 6),

[numeric](18, 6) )
Cоздадим процедуру, которая будет выводить использование буфферного пула каждой отдельной базой данных:

CREATE procedure sp_insert_buffer_pool_log
AS
	insert into Monitor.dbo.BufferPoolLog
	SELECT 
		getdate() as collection_time,
		CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END as [db_name],
		(COUNT(*) * 8.0) / 1024 as Size,
		Sum(CASE WHEN (is_modified = 1) THEN 1 
			ELSE 0 END) * 8 / 1024 AS dirty_pages_size
	FROM
	  sys.dm_os_buffer_descriptors
	GROUP BY
	  database_id

Грязные страницы = измененные страницы. Эту процедуру заворачиваем в джоб. Я поставил выполняться раз в три минуты. И создадим процедуру для селекта:

CREATE procedure sp_select_buffer_pool_log
	@start datetime = NULL,
	@end datetime = NULL
AS
	if @start is NULL set @start = dateadd(HH, -3, getdate())
	if @end is NULL set @end = getdate()
	SELECT	collection_time AS 'collection_time',
			db_name,
			Size AS 'size'
	FROM BufferPoolLog 
	WHERE (collection_time>= @start And collection_time<= @end)
	ORDER BY collection_time, db_name

Отлично, данные собираются, историческая база копится, осталось придумать удобный способ просмотра. И тут нам на помощь приходит старый добрый Excel.

Я приведу пример для счетчиков производительности, а для использования буфферного пула можно будет настроить по аналогии.

Открываем Excel, заходим в «Данные» — «Из других источников» — «Из Microsoft Query».

Создаем новый источник данных: драйвер — SQL Server или ODBC для SQL Server или SQL Server native Client, нажимаем «связь» и прописываем свой сервер, выбираем нашу БД в параметрах, в пункте 4 выбираем любую таблицу (она нам не понадобится).

Кликаем на наш созданный источник данных, нажимаем «Отмена» и на вопрос «Продолжить изменение запроса в Microsoft Query?» нажимаем «Да».

Закрываем диалог «Добавление таблицы». Далее идем в «Файл» → «Выполнить запрос к SQL». Пишем exec sp_select_perf_counters. Нажимаем ОК, идем в «Файл» — «вернуть данные в Microsoft Excel».

Выбираем, куда поместить результаты. Рекомендую оставить две строки сверху для параметров.
Идем в «Данные» — «Подключения», заходим в свойства нашего подключения. Переходим на вкладку «Определение» и там, где текст команды пишем exec sp_select_perf_counters?,?..
Нажимаем ОК и Excel предлагает нам выбрать, из каких ячеек ему брать эти параметры. Указываем ему эти ячейки, ставим галки «использовать по умолчанию» и «автоматически обновлять при изменении ячейки».