MSSQL Server'da Belirli Hostname ve Query'in Kill Edilip Kaydedilmesi

Bu makalede son kullanıcının veritabanı üzerinde index oluşturmak istediği zaman kullanıcının ilgili sorgusunu kill eden yapıyı görmüş olacağız. Bazen veritabanı üzerinde rastgele oluşturulan bir index sunucu üzerinde memory ve cpu anlamında yük bindirebilir. Bu yapı sayesinde index oluşturan değiştiren son kullanıcının bu yapısı engellenerek daha planlı bir şekilde dba kontrolünde ve yoğunlu az olduğu zaman dilimlerinde oluşturulması gerekir.

Öncelikle silinen sorguların geçmişini tutacak bir tablo oluşturalım:

use DBAMON
go

CREATE TABLE IndexKillLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    SessionID INT,
    LoginName NVARCHAR(256),
    HostName NVARCHAR(256),
    ExecutedSQL NVARCHAR(MAX),
    KillDate DATETIME DEFAULT GETDATE()
);

Aşağıdaki script yardımıyla kill edilecek ifadelerin kaydedilip daha sonra kill edilme aşamasını görmüş olacağız.

use DBAMON
go
DECLARE @SessionID INT;
DECLARE @LoginName NVARCHAR(256);
DECLARE @HostName NVARCHAR(256);
DECLARE @SQLText NVARCHAR(MAX);
DECLARE @KillCmd NVARCHAR(MAX);

-- Cursor ile kriterlere uyan tüm aktif session'ları geziyoruz
DECLARE KillCursor CURSOR FOR 
SELECT 
    s.session_id, 
    s.login_name, 
    s.host_name, 
    t.text
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE 
    s.host_name LIKE 'HostName' 
   -- AND s.login_name LIKE 'kln%'
    AND (t.text LIKE '%ALTER INDEX%' OR t.text LIKE '%CREATE CLUSTERED INDEX%' OR t.text LIKE '%CREATE NONCLUSTERED INDEX%')
    AND s.session_id <> @@SPID;

OPEN KillCursor;
FETCH NEXT FROM KillCursor INTO @SessionID, @LoginName, @HostName, @SQLText;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 1. Önce Log Tablosuna Kaydet
    INSERT INTO IndexKillLog (SessionID, LoginName, HostName, ExecutedSQL)
    VALUES (@SessionID, @LoginName, @HostName, @SQLText);

    -- 2. Sonra Kill Komutunu Hazırla ve Çalıştır
    SET @KillCmd = 'KILL ' + CAST(@SessionID AS VARCHAR(10));
    EXEC sp_executesql @KillCmd;

    PRINT 'Session ' + CAST(@SessionID AS VARCHAR(10)) + ' loglandı ve kill edildi.';

    FETCH NEXT FROM KillCursor INTO @SessionID, @LoginName, @HostName, @SQLText;
END

CLOSE KillCursor;
DEALLOCATE KillCursor;

Büyük bir tablomuz üzerinde yukarıdaki sorgumuzda belirtilen host’un gelmesini engelleyecek bir index oluşturuyoruz. Aşağıdaki resimde görülebilir.

İlgili script çalıştırıldığında sorgumuzun kill edildiği görülmektedir. Bu işlemi kill yapılan sorgunun kaydettiği tablodan görebiliriz.

Son kullanıcı ilgili işlemin sonlandırıldığına dair hata mesajı almaktadır.

Kill edilen query’in tablomuza kaydedildiği görülmektedir.

Artık belirlenen hostlardan çalıştırmaması gereken sorguları kill edilmiş olacaktır.

Neden Bu Yöntem?

Kullanıcı “Sorgum neden yarıda kaldı?” dediğinde, log tablosundaki ExecutedSQL sütununu göstererek durumu açıklayabilirsiniz.
Tek seferde tüm session’ları öldürmek yerine döngü (Cursor) kullandığımız için her bir işlemi ayrı ayrı loglayıp garantili şekilde sonlandırırız.
IndexKillLog tablosuna bakarak hangi kullanıcının en çok kural ihlali yaptığını GROUP BY LoginName ile görebilirsiniz.

Bu scripti her 5 dakikada bir çalışacak bir SQL Agent Job olarak kurarsanız, sisteminiz adeta bir “otomatik polis” gibi çalışacaktır.

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

“Şüphesiz güçlükle beraber bir kolaylık vardır. Gerçekten, güçlükle beraber bir kolaylık vardır.” İnşirah Suresi; 5-6. Ayet

MSSQL Server’da Belirli Hostname ve Query’in Kill Edilip Kaydedilmesi

Bir yanıt yazın