MSSQL Server’da CPU Yük Geçmişi Analizi

SQL Server, sistem olaylarını ve performans metriklerini sürekli olarak bellek üzerinde tutulan küçük veri depolarında (Ring Buffers) saklar. Aşağıdaki sorgu, özellikle RING_BUFFER_SCHEDULER_MONITOR tipindeki verileri hedef alarak, işlemcinin (CPU) o anki durumundan ziyade yakın geçmişteki eğilimini görmemizi sağlar. Ring Buffer verileri SQL Server servisi yeniden başlatıldığında temizlenir.

Sistemde anlık bir donma yaşandı ve geçtiyse, o anki CPU kullanımını göremezsiniz. Bu sorgu, son 256 kaydı (genellikle son 1-4 saatlik dilim) getirerek, dar boğazın yaşandığı tam dakikayı ve o esnada yükün SQL Server’dan mı yoksa başka bir yazılımdan mı kaynaklandığını gösterir

--Getting CPU load history
DECLARE
  @now BIGINT,
  @CPUCount int ;

SELECT @now = cpu_ticks / (cpu_ticks / ms_ticks) ,@CPUCount = socket_count
FROM sys.dm_os_sys_info WITH (NOLOCK);

;WITH RingBufferData([timestamp], rec)
AS
(
  SELECT [timestamp], CONVERT(XML, record) AS rec 
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE 
    ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND
    record LIKE N'%<SystemHealth>%'
)
,Data(id, SystemIdle, SQLCPU, [timestamp])
AS
(
  SELECT
    rec.value('(./Record/@id)[1]', 'int') 
    ,rec.value
     ('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')
    ,rec.value
     ('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') 
    ,[timestamp] 
  FROM RingBufferData
)
SELECT TOP 256 
  dateadd(MS, -1 * (@now - [timestamp]), getdate()) AS [Event Time] 
  ,SQLCPU/@CPUCount AS [SQL Server CPU Utilization] 
  ,SystemIdle/@CPUCount  AS [System Idle]
  ,(400 - SystemIdle - SQLCPU)/@CPUCount  AS [Other Processes CPU Utilization]
FROM Data
ORDER BY id desc
OPTION (RECOMPILE, MAXDOP 1);

Yukarıdaki resimde çıktının ne anlama geldiği:

• SQL Server CPU Utilization: SQL Server’ın ne kadar güç harcadığı.
• System Idle: İşlemcinin boşta kalma oranı.
• Other Processes: SQL Server dışındaki uygulamaların (antivirüs, yedekleme araçları vb.) CPU üzerindeki yükü.

Eğer Other Processes CPU Utilization değeri yüksekse, sorun SQL sorgularınızda değil, sunucuda çalışan başka bir servistedir. Bu, veritabanı yöneticisinin (DBA) suçu üzerinden atmasını ve sistem ekibini doğru yönlendirmesini sağlar.

Sunucunun gün içindeki CPU dalgalanmalarını izleyerek, mevcut donanımın yeterli olup olmadığını veya belirli saatlerde (örneğin yedekleme saatleri) işlemcinin darboğaza girip girmediğini analiz etmek için kullanılır.

Sql server ve diğer proceslerin anlık olarak ne kadar CPU kullandığına bakmak istersek aşağıdaki komur kullanılır. Çalıştırıldığı andaki değerleri vermektedir.

set nocount on declare @ts_now2 bigint

 
select @ts_now2 = cpu_ticks /( cpu_ticks / ms_ticks ) /*cpu_ticks / convert(float, cpu_ticks_in_ms)*/ from sys.dm_os_sys_info

select top 1 record_id,dateadd(ms, -1 * (@ts_now2 - [timestamp]), GetDate()) as EventTime,

SQLProcessUtilization,SystemIdle,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

from (select record.value('(./Record/@id)[1]', 'int') as record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

timestamp from (select timestamp, convert(xml, record) as record

from sys.dm_os_ring_buffers

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%<SystemHealth>%') as x

) as y order by record_id desc

Belirli bir saat dilimine göre analiz yapmak istersek aşağıdaki komut kullanılmaktadır. İlk sorguya göre daha iyi değer vermektedir.

DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 100 record_id
,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
,[SQLProcess (%)]
,SystemIdle
,100 - SystemIdle - [SQLProcess (%)] AS [OtherProcess (%)]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcess (%)]
,[timestamp]
FROM (
SELECT [timestamp]
,convert(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC

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

Anne babaya kaba davranmayın İsra-23