Моніторинг 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 = 'Checkpoint Pages/sec' or
counter_name = 'Processes Blocked' or
(counter_name = n'lock Waits/sec' and instance_name = '_Total') or
counter_name = 'User Connections' or
counter_name = 'SQL Re-Compilations/sec' or
counter_name = '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 set NULL @start = dateadd(HH, -3, getdate())
if @end is set NULL @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)
)

Створимо процедуру, яка буде виводити використання буфферного пулу кожною окремою базою даних:

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 set NULL @start = dateadd(HH, -3, getdate())
if @end is set NULL @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 =NOW()-3/24 (поточні дата і час мінус 3 години)
Параметр2 =NOW() (поточні дата і час)
Далі клікаєм на нашій таблиці і йдемо в «Вставка» — «Зведена таблиця» — «Зведена діаграма».
Налаштовуємо зведену таблицю:

Поля легенди — 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» — ім'я зведеної таблиці. Можна подивитись, натиснувши на зведеній таблиці і зайшовши в розділ «Параметри».

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

image

Для клонування файлу достатньо у властивостях нашого підключення в 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-операції.

Приклад війни баз за вивід кеш (імена замазав):

image

До речі, виявилося, що цей метод працює набагато швидше нашого заббикса, так що я залишив його собі на озброєння.

Як і обіцяв, весь T-sql в одному скрипті:

Script
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 = 'Checkpoint Pages/sec' or
counter_name = 'Processes Blocked' or
(counter_name = n'lock Waits/sec' and instance_name = '_Total') or
counter_name = 'User Connections' or
counter_name = 'SQL Re-Compilations/sec' or
counter_name = '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 set NULL @start = dateadd(HH, -3, getdate())
if @end is set NULL @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 set NULL @start = dateadd(HH, -3, getdate())
if @end is set NULL @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 @INT ReturnCode
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='JOB', @type='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='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='No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name='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='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='TSQL', 
@command='sp_insert_perf_counters', 
@database_name='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='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 @INT ReturnCode
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='JOB', @type='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='BufferPoolUsage', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description='No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name='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='TSQL', 
@command='sp_insert_buffer_pool_log', 
@database_name='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='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
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.