На фото сцена из старой доброй комедии «Аэроплан» — бумеры помнят ее.
Она здесь не случайно. Но пригодится чуть позже.
Вовсе не потому, что интерфейс PerfMon не менялся с Windows NT 3.1 (?) и отдает теплой ламповостью двухтысячных годов. Кстати, может быть кто нибудь знает объяснение этому странному факту. Control panel переписывают несколько раз в год. Даже калькулятор переписали. Но не perfMon.
DBA мыслит кверями. Когда к больному SQL server приходит добрый доктор DBA, он открывает свой чемоданчик, а в чемоданчике этом огромное количество кверей, которые служат ему верой и правдой. Самые короткие из них он набирает по памяти. Иногда DBA может заглянуть и в метрики PerfMon — Physical disk, или Disk Queue Length. Собственные же метрики SQL server настоящий DBA будет доставать не через PerfMon, а тоже кверью, например, так:
Когда представители IT, оторванные от DBA сферы, спрашивают его, «какие метрики вы хотите, чтобы мы собирали?» (ну там всякие splunk, squared-up итд), то DBA начинает думать о скриптах, а остальные — о perfmon metrics. При этом, как правило, стараются вывалить на DBA все метрики. Когда такое происходит, то получается бесполезная по своей перегруженности картина. И тут уже мне поможет короткий видео отрывок из фильма «Аэроплан».
В итоге DBA пишет квери, которые записывают статистику каждые N минут в какую-нибудь табличку в базе DBAtasks. Узнаете себя?
И тем не менее, perfmon counters являются стандартным средством. Их можно логгировать, устанавливать алерты, но самое главное, есть большое число систем, которые «сливают» эти метрики со многих машин в централизованное хранилище, позволяют проводить по ним анализ и строить красивые (а не как в perfmon) графики.
Оказывается, мы можем подружить SQL world и PerfMon world!
Ниже я покажу, как.
Для примера мы покажем, как экспортировать в PerfMon метрики, которые не публикует сам SQL. Например, запрос ниже:
выдает объем пространства tempdb, занятого version store (для snapshot), user objects (#tab и ##tab) и пространства сортировки/временного хранения. Создадим для них метрики (18+, команды недокументированы)
Это создаст три метрики в SQLServer -> UserSettable -> Query. Метрики целые — int (не bigint). Кроме того, их значение интерпретируется 'как есть'. То есть, если нужна дельта от предыдущего значения, то это ваша забота. Но в данном случае нам надо просто присвоить метрикам значения:
Собственно, все. Теперь этот код просто вызывайте регулярно и все будет работать.
Теперь давайте смастерим счетчик с дельтой. Это будет время ожидания блокировки в ms.
Тут вы мне можете возразить, что такая метрика уже есть:
Вот только эта метрика только лишь eventually consistent. Как вы помните, если протоны неустойчивы, то любая база данных во вселенной is eventually consistent. То есть, если вы создадите блокировку и будете смотреть на значение этой метрики, то расти она не будет! И лишь по окончании блокировки ее значение резко прыгнет на полное время ожидания. На графике вместо ожидаемого 'плато' вы получите резкий пик, который еще и испортит вам масштаб по оси Y.
Та же проблема есть у квери:
Поэтому мы должны к времени 'завершенных' блокировок добавить время текущих:
Вот эта кверь уже дает на графике правильное плато.
Теперь нам надо считать дельту от предыдущего значения. Тут есть два способа:
Во втором случае вполне можно делать цикл раз в пять секунд или даже чаще. Итак, получаем:
Обратите внимание, что при первом выполнении цикла DBCC будет пропущено (что правильно), так как @OLDlock (и lock) будут null.
Она здесь не случайно. Но пригодится чуть позже.
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.