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