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

CREATE DATABASE MonitoringDB;
GO

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 та таблиці створені успішно';

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 'Процедура оновлена успішно';

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 створено успішно';