Различия

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

--- stvorennja_v_sql_sistemi_monitoringa_blokuvan [2026/05/01 14:40]
tro
+++ stvorennja_v_sql_sistemi_monitoringa_blokuvan [2026/05/01 14:52] (текущий)
tro
@@ Строка 87: / Строка 87: @@
 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>