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.
execution_count: Sorgunun kaç kez tetiklendiğini gösterir. Bir sorgu 1 ms sürebilir ama günde 1 milyon kez çalışıyorsa, toplamda sistemi kilitleyebilir.
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;

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