Заставляем дружить 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.