Заставляем дружить SQL server и PerfMon

На фото сцена из старой доброй комедии «Аэроплан» — бумеры помнят ее.

Она здесь не случайно. Но пригодится чуть позже.

DBA не любят perfmon

Вовсе не потому, что интерфейс PerfMon не менялся с Windows NT 3.1 (?) и отдает теплой ламповостью двухтысячных годов. Кстати, может быть кто нибудь знает объяснение этому странному факту. Control panel переписывают несколько раз в год. Даже калькулятор переписали. Но не perfMon.

DBA мыслит кверями. Когда к больному SQL server приходит добрый доктор DBA, он открывает свой чемоданчик, а в чемоданчике этом огромное количество кверей, которые служат ему верой и правдой. Самые короткие из них он набирает по памяти. Иногда DBA может заглянуть и в метрики PerfMon — Physical disk, или Disk Queue Length. Собственные же метрики SQL server настоящий DBA будет доставать не через PerfMon, а тоже кверью, например, так:

select * from sys.dm_os_performance_counters    where object_name='SQLServer:Buffer Manager'      and counter_name like 'Page life expectancy%'  	and instance_name='' 

Когда представители IT, оторванные от DBA сферы, спрашивают его, «какие метрики вы хотите, чтобы мы собирали?» (ну там всякие splunk, squared-up итд), то DBA начинает думать о скриптах, а остальные — о perfmon metrics. При этом, как правило, стараются вывалить на DBA все метрики. Когда такое происходит, то получается бесполезная по своей перегруженности картина. И тут уже мне поможет короткий видео отрывок из фильма «Аэроплан».

В итоге DBA пишет квери, которые записывают статистику каждые N минут в какую-нибудь табличку в базе DBAtasks. Узнаете себя?

Выступаем адвокатом дьявола (perfmon)

И тем не менее, perfmon counters являются стандартным средством. Их можно логгировать, устанавливать алерты, но самое главное, есть большое число систем, которые «сливают» эти метрики со многих машин в централизованное хранилище, позволяют проводить по ним анализ и строить красивые (а не как в perfmon) графики.

Оказывается, мы можем подружить SQL world и PerfMon world!

Ниже я покажу, как.

Теперь пойдет конкретика

Для примера мы покажем, как экспортировать в PerfMon метрики, которые не публикует сам SQL. Например, запрос ниже:

select   convert(numeric(10,2),round(((sum(version_store_reserved_page_count)*1.0)/128.00),2)),   convert(numeric(10,2),round(((sum(user_object_reserved_page_count)*1.0)/128.00),2)),   convert(numeric(10,2),round(((sum(internal_object_reserved_page_count)*1.0)/128.00),2)) from   tempdb.sys.dm_db_file_space_usage;

выдает объем пространства tempdb, занятого version store (для snapshot), user objects (#tab и ##tab) и пространства сортировки/временного хранения. Создадим для них метрики (18+, команды недокументированы)

dbcc addinstance ('SQLServer:User Settable', 'TempDB version store KB') dbcc addinstance ('SQLServer:User Settable', 'TempDB user store KB') dbcc addinstance ('SQLServer:User Settable', 'TempDB sort store KB') 

Это создаст три метрики в SQLServer -> UserSettable -> Query. Метрики целые — int (не bigint). Кроме того, их значение интерпретируется ‘как есть’. То есть, если нужна дельта от предыдущего значения, то это ваша забота. Но в данном случае нам надо просто присвоить метрикам значения:

declare @TEMPDBver int, @TEMPDBuser int, @TEMPDBsort int select  @TEMPDBver = convert(numeric(10,2),round(((sum(version_store_reserved_page_count) *1.0)/128.00),2)), @TEMPDBuser = convert(numeric(10,2),round(((sum(user_object_reserved_page_count) *1.0)/128.00),2)), @TEMPDBsort = convert(numeric(10,2),round(((sum(internal_object_reserved_page_count) *1.0)/128.00),2)) from tempdb.sys.dm_db_file_space_usage;  if @TEMPDBver is not null       dbcc setinstance ('SQLServer:User Settable', 'Query',      'TempDB version store KB', @TEMPDBver) if @TEMPDBuser is not null      dbcc setinstance ('SQLServer:User Settable', 'Query',     'TempDB user store KB', @TEMPDBuser) if @TEMPDBsort is not null      dbcc setinstance ('SQLServer:User Settable', 'Query',      'TempDB sort store KB', @TEMPDBsort)

Собственно, все. Теперь этот код просто вызывайте регулярно и все будет работать.

Счетчики с дельтой

Теперь давайте смастерим счетчик с дельтой. Это будет время ожидания блокировки в ms.

dbcc addinstance ('SQLServer:User Settable', 'LOCK ms per s') 

Тут вы мне можете возразить, что такая метрика уже есть:

select * from sys.dm_os_performance_counters    where counter_name like 'Lock Wait Time (ms)%'      and instance_name='_Total'     and object_name='SQLServer:Locks' 

Вот только эта метрика только лишь eventually consistent. Как вы помните, если протоны неустойчивы, то любая база данных во вселенной is eventually consistent. То есть, если вы создадите блокировку и будете смотреть на значение этой метрики, то расти она не будет! И лишь по окончании блокировки ее значение резко прыгнет на полное время ожидания. На графике вместо ожидаемого ‘плато’ вы получите резкий пик, который еще и испортит вам масштаб по оси Y.

Та же проблема есть у квери:

select sum(wait_time_ms)    from sys.dm_os_wait_stats    where wait_type like 'LCK_%'

Поэтому мы должны к времени ‘завершенных’ блокировок добавить время текущих:

declare @NEWlock bigint select @NEWlock=sum(wait_time_ms)    from sys.dm_os_wait_stats    where wait_type like 'LCK_%' -- finished waits select @NEWlock=@NEWlock+isnull(sum(waittime),0)    from master.dbo.sysprocesses    where blocked>0 and lastwaittype like 'LCK_%' -- waits in progress 

Вот эта кверь уже дает на графике правильное плато.

Теперь нам надо считать дельту от предыдущего значения. Тут есть два способа:

• Оформить процесс как job, который вызывается часто (скажем, раз в минуту). Создать табличку для хранения предыдущих значиений. На самом деле, часто вызывать job плохо (накладные расходы + конкуренция за историю выполнения Jobs ), да и без дополнительной таблички можно обойтись:
• Создать процесс который работает в бесконечном цикле c waitfor. Предыдущее значение процесс помнит в переменной. Процесс оформим как job с двумя schedule: при старте и раз в час (на тот случай, если job упала — она тогда перезапустится)

Во втором случае вполне можно делать цикл раз в пять секунд или даже чаще. Итак, получаем:

declare @OLDlock bigint, @NEWlock bigint declare @lock int, @seconds int = 5 -- must match WAITFOR loop:   set @OLDlock=@NEWlock -- shift new to old   select @NEWlock=sum(wait_time_ms)      from sys.dm_os_wait_stats  	where wait_type like 'LCK_%' -- finished waits   select @NEWlock=@NEWlock+isnull(sum(waittime),0)      from master.dbo.sysprocesses  	where blocked>0 and lastwaittype like 'LCK_%' -- waits in progress   set @lock=(@NEWlock-@OLDlock)/@seconds -- this is delta   if @lock is not null         dbcc setinstance ('SQLServer:User Settable', 'Query',  	  'LOCK ms per s', @lock)   waitfor delay '00:00:05'  goto loop

Обратите внимание, что при первом выполнении цикла DBCC будет пропущено (что правильно), так как @OLDlock (и lock) будут null.