MSSQL Server'da En Çok CPU Tüketen Sorgular

Veritabanı yönetiminde bazen sorun o an çalışan bir kullanıcı değildir; asıl sorun, her çalıştığında sistemi azar azar yoran ama toplamda devasa bir yük oluşturan verimsiz sorgulardır. Bu SQL komutu, SQL Server’ın Plan Cache (Plan Belleği) istatistiklerini kullanarak “ortalama bazda en ağır” 10 sorguyu gün yüzüne çıkarır. Anlık olarak en çok CPU tüketen sorguları görmek için ilgili makale kullanılabilir.

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
   SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
   (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
   (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
   qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
   (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
   (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Bu sorgu, sys.dm_exec_requests (anlık istekler) yerine sys.dm_exec_query_stats görünümünü kullanır. Aralarındaki temel fark şudur: Bu görünüm, bir sorgu bittikten sonra bile onun ne kadar CPU harcadığını, kaç kez çalıştığını ve ne kadar sürdüğünü hafızasında tutar.

avg_cpu_time_ms: Sorgunun her bir çalışmasında işlemciyi (CPU) kaç milisaniye meşgul ettiğini hesaplar. Bu, performans iyileştirmesi için en dürüst metriklerden biridir.
avg_logical_reads: Bellekten okunan sayfa sayısıdır. Eğer bu değer yüksekse, sorgu muhtemelen indeks kullanmıyor ve koca tabloları belleğe çekmeye çalışıyordur.

Sorgunun sonundaki ORDER BY (qs.total_worker_time / qs.execution_count) DESC ifadesi çok kritiktir. Toplam CPU yerine ortalama CPU süresine göre sıralama yaparak; nadiren çalışan ama çalıştığında sistemi felç eden “ağır siklet” sorguları listenin en başına getirir.

Bu sorgu, bir DBA için “Düşük Asılı Meyveleri” toplama aracıdır. Eğer listenin ilk 3 sırasındaki sorguların avg_logical_reads değerleri binlerle ifade ediliyorsa, orada bir Index (İndeks) eksikliği veya kötü yazılmış bir WHERE koşulu var demektir.

Bu sorgu sonuçları, SQL Server servisi her yeniden başladığında veya DBCC FREEPROCCACHE komutu çalıştırıldığında sıfırlanır. Bu yüzden, sonuçları düzenli aralıklarla kontrol etmek en sağlıklısıdır.

Aşağıdaki sorgu, plan önbelleğindeki (plan cache) istatistiklere dayanarak en çok CPU kaynağı tüketen ilk 500 sorguyu bulmanızı sağlar. TOP 500 ifadesini ihtiyaca göre artırabilir veya azaltabilirsiniz.

SELECT TOP 50 -- İlk 50 kaydı getir
    qs.total_worker_time / 1000 AS total_cpu_time_ms, 
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_time_ms, 
    qs.execution_count, 
    SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, 
        ((CASE qs.statement_end_offset 
          WHEN -1 THEN DATALENGTH(qt.text) 
          ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS individual_query, 
    qt.text AS parent_query, 
    DB_NAME(qt.dbid) AS database_name, 
    qp.query_plan 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
ORDER BY qs.total_worker_time DESC;

Aşağıdaki komut sayesinde anlık çalışan sorgularda maliyetli cpu değerlerini göstermektedir.

SELECT
r.session_id,
r.status,
r.cpu_time,
r.start_time,
r.total_elapsed_time,
r.logical_reads,
r.reads,
r.writes,
s.text AS QueryText
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS s
ORDER BY r.cpu_time DESC;

Aşağıdaki sorgu ile En Çok CPU tüketen sorguların Execution Planı- Plan Handle değerini ve sorgu ile ilgili genel bilgilere ulaşabilirsiniz.

select
b.query_plan as QueryPlan,
es.session_id as SPID,
er.database_id,
db_name(er.database_id) AS DBName,
a.text,
wait_time,
wait_type,
blocking_session_id,
er.cpu_time as CPU,
er.logical_reads,
*
from
sys.dm_exec_sessions es
inner join sys.dm_exec_requests er on es.session_id = er.session_id
CROSS APPLY sys.dm_exec_query_plan (Plan_Handle) as b
CROSS APPLY fn_get_sql(SQL_HANDLE) AS a
where er.status in ('running', 'suspended')
order by CPU DESC

Aşağıdaki sorgu ile gelen sorguların ne kadar MAXDOP kullanıdığını spill durumu sql handle değeri ve sorgunun ne kadar kaynak tükettiği ve sorgu ile ilgili genel bilgileri görebiliriz.

select total_logical_reads as tot_log_reads, total_worker_time as tot_wrk_time, execution_count ,
total_logical_reads/execution_count 'avg reads' , total_worker_time/execution_count 'avg cpu' , A.text, *
from sys.dm_exec_query_stats
CROSS APPLY fn_get_sql(SQL_HANDLE) AS A
order by total_worker_time desc

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

Gıybet etmeyin. Hucurat-12

MSSQL Server’da En Çok CPU Tüketen Sorgular

Bir yanıt yazın