Мониторинг Microsoft SQL Server используя dm_os_performance_counters

Административные скрипты MS SQL > Мониторинг Microsoft SQL Server используя dm_os_performance_counters
01.11.2017 10:48:17


Наиболее часто встречающиеся слова в статье:

[ReturnCode] [QuitWithRollback] [counter_name] [которая] [getdate] [collection_time] [datetime] [TRANSACTION] [Uncategorized] [collect_time]


Статья:

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

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

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

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

sp_select_perf_counters процедура, которая будет выбирать данные из нашей логовой таблицы.

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

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

 

Скрипт  (запуск просмотра данных) exec sp_select_perf_counters

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

Касательно метрик — page life (cple) однозначно нужен, по нему определяем, сколько страницы живут в буфферном пуле. Checkpoint pages/sec — позволит определить, нагружены ли диски из-за маленького плуа/больших объемов считываемой информации или же из-за того, что на диск постоянно сливаются «грязные страницы» (много изменяемых данных). Batch requests — позволит определить, в момент просадки cple не было ли повышенного количества запросов. С ним же смотрим User Connections. Может просто куча пользователей «ломанулась» одновременно. Granted Workspace Memory — покажет, не отъедается ли часть буфферного пула под память для запросов (hash-операции, сортировка). Такие запросы нужно отлавивать и, возможно, тюнить. Ну а касательно локов — конкретно для этой задачи этот счетчик не очень нужен, но вообщедействительно необходим. Попрой повзоляет сразу понять, почему пользователи не могут/могли попасть в систему или проделать ту или иную операцию, почему ночной реиндекс не завершился вовремя итд. 
Естественно, это не все счетчики, что я собирал. Тут для примера привел.
Так бы я добавил еще Memory Pages/sec — использование файла подкачки, Memory Available Bytes — доступная память, если её мало ОС может делать HardTrim памяти сиквела или прочие неприятности, Full scans/sec — фулл сканы таблиц, если значения большие и коррелируют с нагрузкой на диски — искать планы в кэше, где встречается table или index scan.

Источник : https://habrahabr.ru/post/317426/