MSSQL Server’da Anlık Olarak CPU En Çok Kullanan Sorgular

Bir SQL Server yavaşladığında, genellikle suçlu aşırı kaynak tüketimidir. Sistemde o an neyin “fırtınalar kopardığını” anlamak için iki temel yöntemimiz var: Dinamik Yönetim Görünümleri (DMVs) kullanarak manuel sorgulama yapmak veya Activity Monitor gibi grafiksel arayüzleri kullanmak.

Aşağıdaki sorgu anlık olarak CPU’yu en çok kullanan sorguları göstermektedir.

SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Yukarıda bulunan SQL sorgusu, sistemin o anki “röntgenini” çeker. Özellikle sys.dm_exec_sessions ve sys.dm_exec_requests tablolarını birleştirerek, sadece aktif çalışan işlemleri değil, bu işlemlerin kim tarafından ve hangi komutla yürütüldüğünü de gösterir.

r.cpu_time & r.logical_reads: Sorgunun işlemciyi ne kadar yorduğunu ve bellekten (buffer cache) ne kadar veri okuduğunu gösterir. Çoğu zaman CPU yüksekliğinin sebebi, eksik indeksler yüzünden yapılan aşırı logical okumalardır.
statement_text: CROSS APPLY sys.dm_exec_sql_text kullanımı sayesinde, çalışan koca bir prosedürün içindeki tam olarak hangi satırın o an icra edildiğini cımbızla çekip almanızı sağlar.
r.open_transaction_count: Kapatılmamış işlemlerin sayısını vererek olası “blocking” (kilitleme) sorunlarına dair ipucu sunar.

Activity Monitor harika bir “genel bakış” aracı olsa da, bu SQL komutu derinlemesine analiz için daha güçlüdür. Örneğin:

Çok yüksek CPU kullanımında SSMS arayüzü yanıt vermeyebilir, ancak bir query window üzerinden bu sorguyu çalıştırmak genellikle mümkündür.
Bu sorgunun çıktısını bir yere kaydederek, sistemin o anki durumunu raporlayabilir veya geçmişle kıyaslayabilirsiniz.
Nokta atışı bir şekilde r.total_elapsed_time / (1000 * 60) hesaplamasıyla, bir sorgunun kaç dakikadır sistemi meşgul ettiğini anında görebilirsiniz.

Anlık CPU’yu en çok yoran sorgularla ilgili active monitor ekranın yansıması olan ikinci komut:

WITH profiled_sessions as (
	SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT TOP 10 SUBSTRING(qt.TEXT, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2)+1) as [Query],
er.session_id as [Session Id],
er.cpu_time as [CPU (ms/sec)],
db.name as [Database Name],
er.total_elapsed_time as [Elapsed Time],
er.reads as [Reads],
er.writes as [Writes],
er.logical_reads as [Logical Reads],
er.row_count as [Row Count],
mg.granted_memory_kb as [Allocated Memory],
mg.used_memory_kb as [Used Memory],
mg.required_memory_kb as [Required Memory],
/* We must convert these to a hex string representation because they will be stored in a DataGridView, which can't handle binary cell values (assumes anything binary is an image) */
master.dbo.fn_varbintohexstr(er.plan_handle) AS [sample_plan_handle], 
er.statement_start_offset as [sample_statement_start_offset],
er.statement_end_offset as [sample_statement_end_offset],
profiled_session_id as [Profiled Session Id]
FROM 
sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg 
	ON er.session_id = mg.session_id
LEFT OUTER JOIN profiled_sessions
	ON profiled_session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt,
sys.databases db
WHERE db.database_id = er.database_id
AND er.session_id  <> @@spid

Eğer sisteminizde “anlık bir kasılma” hissediyorsanız, Activity Monitor ile genel gidişatı kontrol etmek iyi bir başlangıçtır. Ancak “Hangi kullanıcı, hangi bilgisayardan bağlanıp, hangi SQL cümlesiyle işlemciyi %100 yapıyor?” sorusuna en net cevabı yukarıdaki sorgular vermektedir.

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

Boş konuşmalardan kaçının Müminin-3

Bir yanıt yazın