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:
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ü instance’a 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. @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ıdaki sorgu kendi tablosunu kendi oluşturduğunu söylemiştik. Eğer tabloyu manuel olarak oluşturulması gerekirse aşağıda tablonun create script’i kullanılabilir.
USE [WhoIsDB]
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]
GOTablomuzu manuel olarak oluşturmuş olduk. Kullanılan sp_whoIsactive sonucunda gelen koluna göre her zaman tablomuzun oluşturulması gerekmektedir.
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.
İlgili tablodan sorun tespiti için aşağıdaki yöntemler denenebilir:
Aşağıdaki komutlar kaydedilen sp_whoIsactive sorgularında aynı zaman diliminde ne kadar geldiğini bulabiliriz.
SELECT collection_time,count(*) as session_count FROM [WhoIsDB].[dbo].[WhoIsActive] where collection_time >= '20260121 16:50:17'
and collection_time < '20260121 17:30:00' group by collection_time order by collection_time descYukarıdaki sorguda tespit edilen tüm kayıtları incelemek için getiririz.
select * from DBAMON.dbo.WhoIsActive
where collection_time = '2026-01-21 16:50:26.080'Aşağıdaki komutla içinde ‘insert bulk’ geçen kayıtları listeler.
select collection_time, * from DBAMON.dbo.WhoIsActive where collection_time >= '20260121 00:00:00' and collection_time < '20260121 17:15:00' and cast(sql_text as nvarchar(max)) like N'%insert bulk%'Aşağıdaki komutla içinde ‘insert bulk’ sorgularını session bazlı sayar ve sıralamaktadır.
select session_id, cast(sql_text as nvarchar(max)), count(*) as sayi from DBAMON.dbo.WhoIsActive where collection_time >= '20260121 00:00:00' and collection_time < '20260121 17:15:00' and cast(sql_text as nvarchar(max)) like N'%insert bulk%' group by session_id, cast(sql_text as nvarchar(max))
order by sayi descYukarıdaki komutlarla sp_whoIsactive tablosunda filtreleme işlemi yapılmaktadır.
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
