Mssql Server CPU'yu En Çok Kullanan Sorgular

SQL Server performans sorunlarında CPU, en yaygın darboğaz kaynaklarından biridir. Ani yavaşlamalar, zaman aşımı hataları ve yetersiz kaynak uyarıları genellikle aşırı CPU tüketen sorgulardan kaynaklanır. Bu makalede, CPU kaynaklı bir sorunu nasıl teşhis edeceğinizi, hangi sorguların suçlu olduğunu nasıl bulacağınızı ve sisteminizin CPU durumunu nasıl anlık olarak izleyeceğinizi adım adım ele alacağız.

CPU baskısı yaşadığınızı gösteren iki kritik performans sayacı (Performance Counter) vardır:

% Processor Time: Sürekli %80-90’ın üzerinde seyretmesi, CPU’nun tükenmek üzere olduğunun en net göstergesidir.
SQL Statistics: Batch Requests/Sec: Saniyede çok fazla sorgu çalıştırılıyorsa, bu durum CPU’ya aşırı yük bindiriyor olabilir.

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;

Ana sorgumuzu, sadece eksik indeks önerisi olan (yani performansı bir indeksle ciddi şekilde artırılabilecek) CPU yoğun sorguları bulacak şekilde filtreleyebiliriz.

SELECT
    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
    q.[text] AS query_text,
    p.query_plan,
    qs_cpu.execution_count,
    DB_NAME(q.dbid) AS database_name
FROM
    (SELECT TOP 500 qs.plan_handle, qs.total_worker_time, qs.execution_count
     FROM sys.dm_exec_query_stats qs
     ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(qs_cpu.plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(qs_cpu.plan_handle) p
WHERE p.query_plan.exist('declare namespace qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        //qplan:MissingIndexes')=1 -- Bu satır, sadece eksik indeks önerisi olan planları filtreler
ORDER BY total_cpu_time_ms DESC;

Aşağıdaki sorgu anlık olarak cpu üzerinde ne olup bittiğini görmek için aşağıdaki sorguları kullanabilirsiniz. Aktif Tread sayısını görmek için aşağıdaki sorgu kullanılmaktadır.

SELECT 
    scheduler_id,
    cpu_id,
    current_tasks_count,
    runnable_tasks_count, 
    active_workers_count 
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY cpu_id;

-----------
SELECT 
    scheduler_id as CPU_NO, 
	current_tasks_count as Mevcut_Worker,
    sum(active_workers_count) as Aktif_Worker
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
group by scheduler_id,current_tasks_count,active_workers_count
ORDER BY scheduler_id;
-------------

SELECT 
    sum(active_workers_count) as Aktif_Worker
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'

runnable_tasks_count: CPU için kuyrukta bekleyen iş sayısıdır. Bu değerin sürekli olarak 5-10’un üzerinde olması, CPU’nun taleplere yetişemediğinin ve bir darboğaz oluştuğunun kesin kanıtıdır. cpu_id: 0’dan başlayarak sistemdeki çekirdek sayısına kadar gider. schedule_id: Scheduler’ın benzersiz tanımlayıcısı (ID’si) SQL Server’ın iç yönetiminde kullanılan kimlik numarası current_tasks_count: Bu scheduler’da şu anda çalışan veya bekleyen toplam görev sayısıdır. 0 olması scheduler’ın boşta olduğunu gösterir. active_workers_count: Aktif olarak çalışan worker (işçi) sayısıdır. Her worker bir task’ı işler.

Bu sorgu, SQL Server’ın 5 saniyelik bir süredeki CPU kullanım yüzdesini hesaplamak için tasarlanmıştır. İşlevselliğini adım adım açıklayayım:

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
--calculate the CPU usage by queries OVER a 5 sec interval
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity]
FROM sys.dm_exec_requests

Sonucun Yorumlanması:

%100’e yakın değer: SQL Server’ın CPU’yu tam kapasite kullandığını gösterir
%50 değeri: CPU’nun yarı kapasiteyle kullanıldığını gösterir
%10’dan az: CPU’nun büyük ölçüde boşta olduğunu gösterir

CPU tüketen sorguları bulmak için başka bir komut:

SELECT TOP 10 
    total_worker_time / execution_count AS AvgCPU,
    execution_count,
    total_worker_time,
    statement_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)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgCPU DESC;

Yukarıdaki adımları izleyerek, CPU kaynaklı performans sorunlarını kolay bir şekilde tespit ederek ve sisteminizin sağlıklı kalmasını sağlayabilirsiniz.

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

“İnsanların en hayırlısı insanlara faydalı olandır.” (Hadis)

Mssql Server CPU’yu En Çok Kullanan Sorgular

Bir yanıt yazın