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