SQL Server’da sp_WhoIsActive ile Session İzleme, Loglama ve Otomatik Mail Alarmı

Bu makalede sql server üzerinde dönen query’lerin belirli bir aralıkla bir tabloya kaydedilmesini ve aynı zamanda belirttiğimiz session sayısı üzerinde olan query’leri ayrı bir tabloya kaydedip ilgili instance’da query sayısının belirli bir değer üzerinde olması sonucu mail gönderme işlemini yapacağız. Bir önceki sp_whoIsactive makalesinde sadece sp_whoIsactive ile dönen değerleri tabloya kaydetme işlemini yapmıştık. İlgili makalede okunabilir.

Bu T-SQL kod parçası bir önceki sp_whoIsactive makalesinden alınıp belirli eklemeler yapılmıştır. Eğer sisteminizde database mail konfigurasyonu varsa sp_whoIsactive takip log mekanizmasında kullanılması yeterlidir.

use DBAMON

DECLARE @retention INT = 30,
        @destination_table VARCHAR(500) = 'WhoIsActive',
        @destination_table2 VARCHAR(500) = 'WhoIsActive2',
        @destination_database sysname = 'DBAMON',
        @schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
	@SQL2 NVARCHAR(4000),
        @parameters NVARCHAR(500),
        @exists BIT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
    BEGIN;
        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                                @get_outer_command = 1,
                                @get_plans = 1,
                                @return_schema = 1,
                                @schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
        EXEC ( @schema );
    END;

--create index on collection_time
SET @SQL
    = 'USE ' + QUOTENAME(@destination_database)
      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;

--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                        @get_outer_command = 1,
                        @get_plans = 1,
                        @destination_table = @destination_table;
/* =========================================================
   Session sayısı 1000 üzerindeyse WhoIsActive2 + Mail
   ========================================================= */

DECLARE @slowTable NVARCHAR(500) = 'DBAMON.dbo.WhoIsActive2';
DECLARE @sessionCount INT;
DECLARE @mailBody NVARCHAR(MAX);
DECLARE @ServerName VARCHAR(50)=@@SERVERNAME;

/* Son collection_time için session sayısını al */
SET @SQL = '
SELECT @cnt = COUNT(*)
FROM ' + @destination_table + '
WHERE collection_time = (SELECT MAX(collection_time) FROM ' + @destination_table + ');
';

EXEC sp_executesql
    @SQL,
    N'@cnt INT OUTPUT',
    @cnt = @sessionCount OUTPUT;

/* Eğer session sayısı 1000 üzerindeyse WhoIsActive2''ye yaz */
IF @sessionCount > 1000
BEGIN
    SET @SQL = '
    INSERT INTO ' + @slowTable + '
    SELECT *
    FROM ' + @destination_table + '
    WHERE collection_time = (SELECT MAX(collection_time) FROM ' + @destination_table + ');
    ';

    EXEC(@SQL);
	
    /* HTML mail içeriği */
    SET @mailBody =
        '<html><body>' +
        '<h1 style="color:red;">' +@ServerName + '- SESSION Uyarı</h1>' +
        '<h2><b>Aktif session sayısı: ' + CAST(@sessionCount AS NVARCHAR(20)) + '</b></h2>' +
        '<p>Session sayısı 1000 eşik değerini aşmıştır.</p>' +
        '</body></html>';

    /* Mail gönder */
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SQLProfile',
        @recipients   = 'veritabaniyonetimi@jandarma.gov.tr',
        @subject      = 'SQL Server Uyarı - Session Sayısı 1000 Üzeri',
        @body         = @mailBody,
        @body_format  = 'HTML';
END

-- Mevcutta bulunan iki tablodaki 30 gün önceki değerler silinir.
SET @SQL
    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE());';
EXEC ( @SQL );

SET @SQL2
    = 'DELETE FROM ' + @destination_table2 + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE());';
EXEC ( @SQL2 );

Yapılması gerekenler:

Bu script çalışmadan önce sp_WhoIsActive prosedürü instance’a kurulu olmalı.
@destination_database değişkeniyle belirtilen veritabanı (örnekte DBAMON) önceden oluşturulmuş olmalı.
OBJECT_ID, sys.indexes, sp_executesql, ve dinamik SQL çalıştırmak için uygun yetkiler gerekir.
Dinamik SQL kullanıldığından SQL injection risklerine dikkat edilmeli. Bu script kontrollü olduğu için genelde güvenlidir. @destination_database ve @destination_table gibi değişkenler kod içinde sabit belirlenmiş. Yani kullanıcıdan alınmıyor. Dinamik SQL var ama içine konan değerler kontrollü. Parametreli sp_executesql kullanılıyor.
Bu script genelde bir SQL Server Agent job’ı olarak periyodik çalıştırılır (örn. her 1-5 dakikada bir).

doğrudan SQL içine giriyor.

Yukarıda belirtilen kodun dönen sonuçları kaydedeceği tabloların önceden oluşturulması gerekmektedir. WhoIsActive ve WhoIsActive2 tablolarının aynı anda oluşturulması gerekmektedir.

USE [DBAMON]
GO

CREATE TABLE [dbo].[WhoIsActive2](
	[dd hh:mm:ss.mss] [varchar](8000) NULL,
	[session_id] [smallint] NOT NULL,
	[sql_text] [xml] NULL,
	[sql_command] [xml] NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[wait_info] [nvarchar](4000) NULL,
	[tran_log_writes] [nvarchar](4000) NULL,
	[CPU] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[blocking_session_id] [smallint] NULL,
	[reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[query_plan] [xml] NULL,
	[used_memory] [varchar](30) NULL,
	[status] [varchar](30) NOT NULL,
	[tran_start_time] [datetime] NULL,
	[open_tran_count] [varchar](30) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL2) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Tablomuzu manuel olarak oluşturmuş olduk. Kullanılan sp_whoIsactive sonucunda gelen koluna göre her zaman tablomuzun oluşturulması gerekmektedir.

Makalenin başında vermiş olduğumuz kodu bir job aracılığıyla belirli zamanlarda çalıştırıyoruz.

Jobımızı test işlemi için çalıştırdığımızda sonucumuzun başarılı bir şekilde geldiği görülmektedir.

Başka makalede görüşmek dileğiyle..

Göklerde ve yerde ne varsa hepsi Allah’ındır. Allah’ın ilmi ve kudreti her şeyi kuşatmıştır. Nisa-126

Bir yanıt yazın