Створення в SQL системи моніторинга блокувань

Крок 1 — створи окрему базу:

CREATE DATABASE MonitoringDB;
GO

Крок 2 — створи таблиці в новій базі:

USE MonitoringDB;
GO
CREATE TABLE dbo.BlockingLog (
Id INT IDENTITY(1,1) PRIMARY KEY,
CaptureTime DATETIME DEFAULT GETDATE(),
BlockedSession INT,
BlockingSession INT,
WaitType NVARCHAR(60),
WaitTimeSec DECIMAL(10,2),
BlockedDB NVARCHAR(128),
BlockedHost NVARCHAR(128),
BlockedLogin NVARCHAR(128),
BlockedProgram NVARCHAR(256),
BlockingHost NVARCHAR(128),
BlockingLogin NVARCHAR(128),
BlockingProgram NVARCHAR(256),
BlockedSQL NVARCHAR(MAX),
BlockingSQL NVARCHAR(MAX),
BlockedObject NVARCHAR(256),
TransactionOpenSec INT
);
CREATE TABLE dbo.LockLog (
Id INT IDENTITY(1,1) PRIMARY KEY,
CaptureTime DATETIME DEFAULT GETDATE(),
SessionId INT,
LoginName NVARCHAR(128),
HostName NVARCHAR(128),
ProgramName NVARCHAR(256),
DBName NVARCHAR(128),
ObjectName NVARCHAR(256),
IndexName NVARCHAR(256),
LockType NVARCHAR(60),
LockMode NVARCHAR(60),
LockStatus NVARCHAR(60),
WaitTimeSec DECIMAL(10,2),
TransactionOpenSec INT,
SQL NVARCHAR(MAX)
);
CREATE TABLE dbo.HeavySessionLog (
Id INT IDENTITY(1,1) PRIMARY KEY,
CaptureTime DATETIME DEFAULT GETDATE(),
SessionId INT,
LoginName NVARCHAR(128),
HostName NVARCHAR(128),
ProgramName NVARCHAR(256),
DBName NVARCHAR(128),
CpuTime INT,
Reads BIGINT,
Writes BIGINT,
WaitType NVARCHAR(60),
WaitTimeSec DECIMAL(10,2),
DurationSec INT,
SQL NVARCHAR(MAX)
);
CREATE TABLE dbo.SnapshotLog (
Id INT IDENTITY(1,1) PRIMARY KEY,
CaptureTime DATETIME DEFAULT GETDATE(),
TotalSessions INT,
RunningSessions INT,
SuspendedSessions INT,
BlockedSessions INT,
TotalWaitSec BIGINT,
SqlMemoryUsedMb INT,
PageFaultCount BIGINT,
MemoryUtilizationPct INT
);
-- Індекси для швидкого пошуку
CREATE INDEX IX_BlockingLog_CaptureTime ON dbo.BlockingLog(CaptureTime);
CREATE INDEX IX_LockLog_CaptureTime ON dbo.LockLog(CaptureTime);
CREATE INDEX IX_HeavySessionLog_CaptureTime ON dbo.HeavySessionLog(CaptureTime);
CREATE INDEX IX_SnapshotLog_CaptureTime ON dbo.SnapshotLog(CaptureTime);
GO
PRINT 'База MonitoringDB та таблиці створені успішно';

Крок 3 — процедура моніторингу (живе в MonitoringDB, читає з системних DMV):

USE MonitoringDB;
GO
CREATE OR ALTER PROCEDURE dbo.usp_CaptureMonitoring
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CaptureTime DATETIME = GETDATE();
-- 1. Загальний знімок стану системи
INSERT INTO dbo.SnapshotLog (
CaptureTime, TotalSessions, RunningSessions,
SuspendedSessions, BlockedSessions, TotalWaitSec,
SqlMemoryUsedMb, PageFaultCount, MemoryUtilizationPct
)
SELECT
@CaptureTime,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1),
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE status = 'running'),
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE status = 'suspended'),
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE blocking_session_id > 0),
(SELECT ISNULL(SUM(wait_time),0)/1000 FROM sys.dm_exec_requests
WHERE wait_type NOT IN ('SLEEP_TASK','WAITFOR')),
p.physical_memory_in_use_kb / 1024,
p.page_fault_count,
p.memory_utilization_percentage
FROM sys.dm_os_process_memory p;
-- 2. Блокування: хто кого блокує
INSERT INTO dbo.BlockingLog (
CaptureTime, BlockedSession, BlockingSession, WaitType, WaitTimeSec,
BlockedDB, BlockedHost, BlockedLogin, BlockedProgram,
BlockingHost, BlockingLogin, BlockingProgram,
BlockedSQL, BlockingSQL, BlockedObject, TransactionOpenSec
)
SELECT
@CaptureTime,
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time / 1000.0,
DB_NAME(r.database_id),
s_blocked.host_name,
s_blocked.login_name,
s_blocked.program_name,
s_blocking.host_name,
s_blocking.login_name,
s_blocking.program_name,
SUBSTRING(t_blocked.text, 1, 4000),
SUBSTRING(t_blocking.text, 1, 4000),
OBJECT_NAME(t_blocked.objectid, r.database_id),
DATEDIFF(SECOND, at.transaction_begin_time, @CaptureTime)
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s_blocked ON r.session_id = s_blocked.session_id
JOIN sys.dm_exec_sessions s_blocking ON r.blocking_session_id = s_blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t_blocked
OUTER APPLY (
SELECT SUBSTRING(t.text, 1, 4000) AS text
FROM sys.dm_exec_requests r2
CROSS APPLY sys.dm_exec_sql_text(r2.sql_handle) t
WHERE r2.session_id = r.blocking_session_id
) t_blocking
LEFT JOIN sys.dm_tran_session_transactions tst ON tst.session_id = r.blocking_session_id
LEFT JOIN sys.dm_tran_active_transactions at ON at.transaction_id = tst.transaction_id
WHERE r.blocking_session_id > 0;
-- 3. Очікування на об'єктах БД
INSERT INTO dbo.LockLog (
CaptureTime, SessionId, LoginName, HostName, ProgramName,
DBName, ObjectName, IndexName, LockType, LockMode, LockStatus,
WaitTimeSec, TransactionOpenSec, SQL
)
SELECT
@CaptureTime,
l.request_session_id,
s.login_name,
s.host_name,
s.program_name,
DB_NAME(l.resource_database_id),
OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id),
NULL,
l.resource_type,
l.request_mode,
l.request_status,
r.wait_time / 1000.0,
DATEDIFF(SECOND, at.transaction_begin_time, @CaptureTime),
SUBSTRING(t.text, 1, 4000)
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = l.request_session_id
LEFT JOIN sys.dm_tran_session_transactions tst ON tst.session_id = l.request_session_id
LEFT JOIN sys.dm_tran_active_transactions at ON at.transaction_id = tst.transaction_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE l.resource_type IN ('OBJECT','PAGE','KEY','ROW','EXTENT')
AND l.request_status = 'WAIT'
AND l.resource_database_id > 4;
-- 4. Важкі сесії
INSERT INTO dbo.HeavySessionLog (
CaptureTime, SessionId, LoginName, HostName, ProgramName,
DBName, CpuTime, Reads, Writes, WaitType, WaitTimeSec, DurationSec, SQL
)
SELECT
@CaptureTime,
r.session_id,
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id),
r.cpu_time,
r.reads,
r.writes,
r.wait_type,
r.wait_time / 1000.0,
DATEDIFF(SECOND, r.start_time, @CaptureTime),
SUBSTRING(t.text, 1, 4000)
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
AND (
r.cpu_time > 5000
OR r.reads > 100000
OR r.wait_time > 30000
);
-- 5. Очищення даних старше 14 днів
DELETE FROM dbo.BlockingLog WHERE CaptureTime < DATEADD(DAY, -14, GETDATE());
DELETE FROM dbo.LockLog WHERE CaptureTime < DATEADD(DAY, -14, GETDATE());
DELETE FROM dbo.HeavySessionLog WHERE CaptureTime < DATEADD(DAY, -14, GETDATE());
DELETE FROM dbo.SnapshotLog WHERE CaptureTime < DATEADD(DAY, -14, GETDATE());
END;
GO
PRINT 'Процедура оновлена успішно';

Крок 4 — Створюємо SQL Agent Job:

USE msdb;
GO
DECLARE @jobId BINARY(16);
EXEC sp_add_job
@job_name = N'1C - Monitoring Capture',
@enabled = 1,
@job_id = @jobId OUTPUT;
EXEC sp_add_jobstep
@job_id = @jobId,
@step_name = N'Run monitoring capture',
@subsystem = N'TSQL',
@command = N'EXEC MonitoringDB.dbo.usp_CaptureMonitoring;',
@database_name = N'MonitoringDB';
EXEC sp_add_schedule
@schedule_name = N'1C Monitor Every 2 min',
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 2;
EXEC sp_attach_schedule
@job_id = @jobId,
@schedule_name = N'1C Monitor Every 2 min';
EXEC sp_add_jobserver
@job_id = @jobId,
@server_name = N'(LOCAL)';
GO
SELECT name, enabled, date_created
FROM msdb.dbo.sysjobs
WHERE name = N'1C - Monitoring Capture';
PRINT 'Job створено успішно';

Крок 4.1 - запуск завдання вручну (для перевірки)

-- Запускаємо вручну
EXEC msdb.dbo.sp_start_job N'1C - Monitoring Capture';

Крок 4.2 Перевырка успышносты запису

-- Перевіряємо що з'явився новий запис
SELECT * FROM MonitoringDB.dbo.SnapshotLog ORDER BY Id DESC;
-- Перевіряємо статус останнього запуску job-а
SELECT
j.name,
h.run_date,
h.run_time,
h.run_duration,
CASE h.run_status
WHEN 0 THEN 'Помилка'
WHEN 1 THEN 'Успішно'
WHEN 2 THEN 'Повтор'
WHEN 3 THEN 'Скасовано'
WHEN 4 THEN 'Виконується'
END AS run_status,
h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name = N'1C - Monitoring Capture'
ORDER BY h.run_date DESC, h.run_time DESC;

Крок 6 - отримання результатів моніторинга

SELECT * FROM MonitoringDB.dbo.BlockingLog
WHERE CaptureTime >= DATEADD(MINUTE, -30, GETDATE())
ORDER BY CaptureTime DESC;
SELECT * FROM MonitoringDB.dbo.HeavySessionLog
WHERE CaptureTime >= DATEADD(MINUTE, -30, GETDATE())
ORDER BY CaptureTime DESC;