MSSQL Server'da CPU ve Memory Aşan Sorguları Kill Etmek

Bu makalede mssql server üzerinde uzun süren sorgularımızın cpu, memory ve toplam süresini gözlemleyerek belirlemiş olduğumuz eşik değerini aşan sorguları kill etmiş olacağız. Kill edilmeden önce kill edilen query’lerin bir tabloya kaydedildiğini görmüş olacağız.

Öncelikle bu verileri saklayacağımız bir tabloya ihtiyacımız var. Bu tabloyu DBAMON veya benzeri bir yönetim veritabanında oluşturabilirsiniz.

Use DBAMON
GO
CREATE TABLE dbo.HighResourceQueryLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    LogDate DATETIME DEFAULT GETDATE(),
    SessionID INT,
    LoginName NVARCHAR(256),
    HostName NVARCHAR(256),
    CPUTime_MS INT,
    Memory_MB DECIMAL(18,2),
    TotalElapsedTime_MS INT,
    SQLText NVARCHAR(MAX),
    ExecutionPlan XML
);

Bu procedure yapısı anlık olarak kaynak tüketen sorguları tespit eder ve tespit işleminden sonra oluşturulan tabloya kayıt işlemi yapar ve ardından kill işlemi gerçekleşmektedir. WHERE kısmındaki eşik değerlerini (örneğin 10 saniye CPU veya 200MB RAM) kendi ortamınıza göre güncelleyebilirsiniz.

USE master;
GO
Create procedure UzunSorguKill
AS
BEGIN
-- 1. ADIM: Bilgileri Tabloya Kaydet
INSERT INTO dbo.HighResourceQueryLog (SessionID, LoginName, HostName, CPUTime_MS, Memory_MB, TotalElapsedTime_MS, SQLText, ExecutionPlan)
SELECT 
    r.session_id,
    s.login_name,
    s.host_name,
    r.cpu_time,
    (r.granted_query_memory * 8 / 1024.0) AS Memory_MB,
    r.total_elapsed_time,
    st.text AS SQLText,
    qp.query_plan
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) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE s.is_user_process = 1 
  AND (
      r.cpu_time > 10000 -- 10 sn CPU
      OR (r.granted_query_memory * 8 / 1024.0) > 200 -- 200 MB RAM
      OR r.total_elapsed_time > 30000 -- 30 sn Süre
  )
  AND r.session_id <> @@SPID;

-- 2. ADIM: Kriterlere Uyan Sorguları Kill Et
DECLARE @kill_command NVARCHAR(MAX) = '';

SELECT @kill_command = @kill_command + 'KILL ' + CAST(r.session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1 
  AND (
      r.cpu_time > 10000 -- CPU üzerinde 10 saniye aşan değerler
      OR (r.granted_query_memory * 8 / 1024.0) > 200 
      OR r.total_elapsed_time > 30000 
  )
  AND r.session_id <> @@SPID;

-- Eğer öldürülecek sorgu varsa komutu çalıştır
IF @kill_command <> ''
BEGIN
    EXEC sp_executesql @kill_command;
    PRINT 'Ağır sorgular sonlandırıldı: ' + @kill_command;
END
END

Yukarıdaki sorguda:

CPU: cpu_time (milisaniye cinsinden) baz alınır. Bu, sorgunun işlemci üzerinde geçirdiği toplam süredir.
Memory: logical_reads (okuma sayısı) veya grant_query_memory (ayrılan sayfa sayısı) üzerinden gidilir. Genelde sayfa sayısı * 8 / 1024 yapılarak MB cinsine çevrilir.

Yukarıda oluşturulan procedure yapısı çalıştırıldıktan sonra kill işleminin gerçekleştiği görülmektedir.

Kill edilen 96 session id’li sorgumuzun kill edildiği ve tablomuza kaydedildiği görülmektedir.

Sorguları otomatik öldürmek tehlikelidir. “Kill” komutu tetiklendiğinde, sorgu o ana kadar yaptığı işlemleri Rollback (Geri Alma) sürecine sokar. Eğer çok büyük bir UPDATE veya DELETE işlemini öldürürseniz, geri alma işlemi veritabanını uzun süre kilitleyebilir. Ayrıca index rebuild ve backup işlemlerinde de sıkıntı yaşamamıza sebebiyet verecektir. Bu gibi risklerden dolayı sadece okuma yapan sorgular baz alınabilir.

Bu tabloyu bir hafta boyunca izledikten sonra şu soruların yanıtını alabileceksiniz:

Belirli saatlerde (örneğin raporlama saatleri) mi yoğunluk oluyor?
Hep aynı kullanıcı mı sistem kaynaklarını zorluyor?
SQLText kolonuna bakarak, CROSS JOIN gibi hatalı kodlar mı yazılmış, yoksa eksik bir index mi var?
ExecutionPlan kolonuna tıklayarak SQL Server Management Studio üzerinden sorgumuzun query plan yapısı görülebilir.

Bu makalede sistem üzerinde CPU ve Memory en çok yoran sorgularımızı detaylı bir şekilde görmüş olduk.

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

“Yeryüzündeki bütün ağaçlar kalem olsaydı, deniz de -ardından ona yedisi daha eklenmek üzere- mürekkep olsaydı yine de Allah’ın sözleri tükenmezdi; Allah azîzdir, hakîmdir.”Lokman-27

MSSQL Server’da CPU ve Memory Aşan Sorguları Kill Etmek

Bir yanıt yazın