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

Когда я устроился на новую работу, передо мной была поставлена первая задача — разобраться, почему один из экземпляров SQL очень сильно нагружает диски. И предпринять необходимые действия для устранения этой ужасной проблемы. Я еще не сказал, что дисковый пул был всего один, и что при нагрузке на диски страдали все экземпляры сиквела? Так вот это было так. Что самое главное, как оказалось, мониторинг в лице Zabbix не собирал необходимые метрики, а на добавление оных нужно было заводить заявку и ждать. Ждать и смотреть, как «горит» дисковый массив. Или…

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

Для начала создадим БД и объекты, необходимые для сбора метрик производительности 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 задают временной интервал, за который мы хотим увидеть значения. Если параметры не заданы, выводить информацию за 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(
	[collection_time] [datetime],
	[db_name] [nvarchar](128),
	[Size] [numeric](18, 6),
	[dirty_pages_size] [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 предлагает нам выбрать, из каких ячеек ему брать эти параметры. Указываем ему эти ячейки, ставим галки «использовать по умолчанию» и «автоматически обновлять при изменении ячейки». Лично я эти ячейки заполнил формулами:

Параметр1 =ТДАТА()-3/24 (текущие дата и время минус 3 часа)
Параметр2 =ТДАТА() (текущие дата и время)

Далее кликаем на нашей таблице и идем в «Вставка» — «Сводная таблица» — «Сводная диаграмма».
Настраиваем сводную таблицу:

Поля легенды — counter_name,
Поля осей — collect_time,
Значения — value.

Вуаля! Получаем графики метрик производительности. Рекомендую изменить тип диаграммы на «График». Осталась еще пара штришков. Переходим на страницу с нашими данными, опять заходим в свойства подключения и выставляем в «Обновлять каждые X мин» значение по желанию. Думаю, логично выставить частоту равную частоте выполнения задания на SQL сервере.

Теперь данные в таблице обновляются автоматически. Осталось заставить обновляться график. Переходим во вкладку «разработчик» — «Visual Basic».

Кликаем слева на лист с исходными данными и вписываем следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
  Worksheets("Своднаятаблица").PivotTables("СводнаяТаблица1").PivotCache.Refresh
End Sub

Где,

«Своднаятаблица» — имя листа со сводной таблицей. То имя, что указано в скобках в VB редакторе.
«СводнаяТаблица1» — имя сводной таблицы. Можно посмотреть, кликнув на сводной таблице и зайдя в раздел «Параметры».

Теперь наш график будет обновляться каждый раз, когда обновляется исходная таблица. Пример такого графика:

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

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

DECLARE	@ram INT,
		@avg_perc DECIMAL,
		@recommended_ram decimal
		
--Узнаем, сколько сейчас выделено серверу
SELECT @ram = CONVERT(INT,value_in_use )
FROM sys.configurations
WHERE name = 'max server memory (MB)'
ORDER BY name OPTION (RECOMPILE);

--Узнаем какой процент от всей памяти составляет Buffer Pool
SELECT @avg_perc = avg(t.perc) FROM
(
SELECT sum(Size)/@ram*100 AS perc FROM Monitor.dbo.BufferPoolLog
GROUP BY collection_time
) t

--Вычисляем рекоммендуемый объем оперативной памяти
SELECT @recommended_ram = sum(t.maxsize)*100/@avg_perc  FROM
(
SELECT db_name, MAX(Size) AS maxsize FROM Monitor.dbo.BufferPoolLog
GROUP BY db_name
) t

select @ram as current_RAM_MB, @recommended_ram as Recommended_RAM_MB

Стоит заметить, что данные вычисления имеют смысл только если вы уверены, что запросы, работающие на сервере оптимизированы и не делают full table scan при каждом удобном (и не очень) случае. Также следует убедиться, мониторя метрику Maximum Granted Workspace, что у вас на сервере нет запросов, отъедающих часть буфферного пула под сортировку и hash-операции.

Пример войны баз за буфферный кэш (имена замазал):

Кстати, оказалось, что этот метод работает намного быстрее нашего заббикса, так что я оставил его себе на вооружение.

Как и обещал, весь T-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
CREATE TABLE BufferPoolLog
(
	collection_time datetime NOT NULL,
	db_name nvarchar(128) NULL,
	Size numeric(18, 6) NULL,
	dirty_pages_size numeric(18, 6)
)
GO
CREATE CLUSTERED INDEX cidx_collection_time ON BufferPoolLog
(
	collection_time
)
GO
create procedure sp_insert_perf_counters -- Процедура, которая собирает счетчики
AS
	insert into perf_counters
	select	getdate() as Collect_time, 
			rtrim(counter_name) as Counter, 
			Value =	CASE	WHEN counter_name like '%/sec%' --Счетчики, которые "что-то в секунду" - инкрементальные, т.е. чтобы получить текущее "среднее" значение нужно поделить на кол-во секунд аптайма. Естественно, с течением времени очень слабо колеблются
								then cntr_value/DATEDIFF(SS, (select create_date from sys.databases where name = 'tempdb'), getdate())
							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%')
GO
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
CREATE procedure sp_insert_buffer_pool_log --процедура, которая собирает данные по использованию буфферного пула базами данных
AS
	insert into 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
GO
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,
			db_name,
			Size
	FROM BufferPoolLog 
	WHERE (collection_time>= @start And collection_time<= @end)
	ORDER BY collection_time, db_name
GO
-- Далее создаем джобу, которая ежеминутно дергает процедуру по счетчикам
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'collect_perf_counters', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sp_insert_perf_counters', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'sp_insert_perf_counters', 
		@database_name=N'monitor', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 1 minute', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20161202, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
-- Создаем джобу, которая собирает данные по использованию буфферного пула. Раз в три минуты
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'BufferPoolUsage', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'sp_insert_buffer_pool_log', 
		@database_name=N'Monitor', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 3 minutes', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=3, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20161117, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Использованные статьи:

» SQL Server Memory Buffer Pools: Understand the Basics
» How to execute stored procedure in excel with parameters

Автор: IndiraGandhi

Источник