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

Paylaştığın 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.

SQL Server Management Studio (SSMS) içindeki Activity Monitor, aslında senin paylaştığın bu sorgunun görselleştirilmiş halidir.

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