stvorennja_v_sql_sistemi_monitoringa_blokuvan

Различия

Показаны различия между двумя версиями страницы.

Ссылка на это сравнение

Следующая версия 		Предыдущая версия
stvorennja_v_sql_sistemi_monitoringa_blokuvan [2026/05/01 14:38]
tro создано 		stvorennja_v_sql_sistemi_monitoringa_blokuvan [2026/05/01 14:52] (текущий)
tro
Строка 1: Строка 1:
 ====== Створення в SQL системи моніторинга блокувань ====== ====== Створення в SQL системи моніторинга блокувань ======
 +===== Крок 1 — створи окрему базу: =====
 +<code>
 +CREATE DATABASE MonitoringDB;
 +GO
 +</code>
 +===== Крок 2 — створи таблиці в новій базі: =====
 +<code>
 +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 та таблиці створені успішно';
 +</code>
 +===== Крок 3 — процедура моніторингу (живе в MonitoringDB, читає з системних DMV): =====
 +<code>
 +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 'Процедура оновлена успішно';
 +</code>
 +===== Крок 4 — Створюємо SQL Agent Job: =====
 +<code>
 +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 створено успішно';
 +</code>
 +==== Крок 4.1 - запуск завдання вручну (для перевірки) ====
 +<code>
 +-- Запускаємо вручну
 +EXEC msdb.dbo.sp_start_job N'1C - Monitoring Capture';
 +</code>
 +==== Крок 4.2 Перевырка успышносты запису ====
 +<code>
 +-- Перевіряємо що з'явився новий запис
 +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;
 +</code>
 +===== Крок 6 - отримання результатів моніторинга =====
 +<code>
 +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;
 +</code>
  
  • /sites/data/attic/stvorennja_v_sql_sistemi_monitoringa_blokuvan.1777646327.txt.gz
  • Последнее изменение: 2026/05/01 14:38
  • — tro