MSSQL Server’da sp_WhoIsActive ile Anlık Sorgu Takibi ve Loglama Otomasyonu

Bu makalede sql server üzerinde dönen query’lerin belirli bir aralıkla bir tabloya kaydedilmesini sağlayacağız. Bu ifade Anlık gelişen sorguların bir tabloya kaydedilmesi sonucu geçmişe dönük olarak sorgularımız üzerinde analiz yapılmasını sağlayacaktır.

Bu T-SQL kod parçası, SQL Server’da performans takibi ve sistem aktivitesini loglamak amacıyla sp_WhoIsActive adlı yaygın bir stored procedure script’ini kullanır. Ana hatlarıyla görevleri şunlardır:

SET NOCOUNT ON;
 
DECLARE @retention INT = 30,--30 gün saklama süresi belirlenir.
        @destination_table VARCHAR(500) = 'WhoIsActive',--Logların yazılacağı hedef tablo 
        @destination_database sysname = 'WhoIsDB',--Logların yazılacağı hedef Veritabanı 
        @schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
        @parameters NVARCHAR(500),
        @exists BIT;
 
SET @destination_table = @destination_database + '.dbo.' + @destination_table;
 
--Eğer tablo yoksa dinamik olarak tablo oluşturulur. Yukarıdaki isimle oluşur.
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;
 
--collection_time alanı üzerinde sorgu performansını artırmak için clustered index oluşturulup oluşturulmadığı kontrol edilir. Yoksa oluşturulur.
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;
 
--sp_WhoIsActive, o anki sistem aktivitelerini (islemeler, planlar, komutlar vs.) toplar ve log tablosuna yazar.
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                        @get_outer_command = 1,
                        @get_plans = 1,
                        @destination_table = @destination_table;
 
--Belirtilen saklama süresi kadar eski olan loglar silinir (örn. 30 günden eski olanlar).
SET @SQL
    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE());';
EXEC ( @SQL );

Yapılması gerekenler:

• Bu script çalışmadan önce sp_WhoIsActive prosedürü veritabanınızda kurulu olmalı.
• @destination_database değişkeniyle belirtilen veritabanı (örnekte WhoIsDB) ö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.
• Bu script genelde bir SQL Server Agent job’ı olarak periyodik çalıştırılır (örn. her 1-5 dakikada bir).

SQL Injection (SQL Enjeksiyonu), kötü niyetli bir kullanıcının, uygulamanın veri tabanına gönderdiği SQL sorgularını manipüle ederek sisteme zarar vermesi ya da yetkisiz verilere erişmesi anlamına gelir.

Yukarıdaki kodun güvenli olduğunu aşağıdaki ifadeden anlayabiliriz.

• @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:
• EXEC sys.sp_executesql @SQL, @parameters, …

Bu yöntem SQL Injection’a karşı güvenli, çünkü değerleri dışarıdan gelen metin gibi değil, parametre olarak işler.

Aşağıdaki gibi bir şey olsaydı SQL Injection riski olurdu:

SET @SQL = 'SELECT * FROM ' + @user_input + ' WHERE id = ' + @id;
EXEC (@SQL);

Çünkü burada @user_input kontrolsüz şekilde doğrudan SQL içine giriyor.

Kaldığımız yerden devam edecek olursak Yukarıdaki sorgu kendi tablosunu kendi oluşturduğunu söylemiştik. Eğer tabloyu manuel olarak oluşturulması gerekirse aşağıda tablonun create scripti kullanılabilir.

USE [WhoIsDB]
GO

/****** Object:  Table [dbo].[WhoIsActive]    Script Date: 20.04.2025 17:48:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WhoIsActive](
	[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 NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

İlk sorgumuzun çalıştırılması kullanmış olduğumuz sp_whoIsactive yapısına göre farklılık gösterebilir. Bunun için tablonun manuel oluşturulmasına gerek yoktur.

Yukarıdaki komut bir job aracılığıyla 1 yada 5 dakikada bir çalıştırılabilir. Bu şekilde herhangi bir sorun var geçmişe dönük inceleme yapılabilir.

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