MSSQL Server’da CPU Yük Geçmişi Analizini Tabloya Kaydetme

Veritabanı yöneticileri (DBA) için sistem performansını izlemek, olası darboğazları (bottleneck) önceden tespit etmek adına kritiktir. SQL Server’ın kendi içinde tuttuğu Ring Buffer verilerini kullanarak, ek bir yazılıma ihtiyaç duymadan CPU kullanım verilerini nasıl anlamlı bir tabloya dönüştürebileceğimizi inceleyeceğiz. Sayfada bulunan ilgili makale ile CPU yük analizini detaylı bir şekilde görebilirsin.

SQL Server, sistem sağlığıyla ilgili bilgileri XML formatında sys.dm_os_ring_buffers görünümünde saklar. Bu makalede, bu verileri düzenli aralıklarla okuyup kalıcı bir tabloya yazacak olan mekanizmayı kuracağız.

İzleyeceğimiz üç temel metrik şunlardır:

SQL Process Utilization: SQL Server servisinin CPU kullanım oranı.
System Idle: Sistemin boşta kalma oranı (CPU’nun ne kadarı kullanılmıyor).
Other Process Utilization: SQL Server dışındaki diğer uygulamaların CPU kullanım oranı.

Öncelikle, toplanan verilerin tarihsel olarak saklanacağı bir tabloya ihtiyacımız var. DBAMON veritabanı altında CpuUsage tablosunu oluşturuyoruz:

USE [DBAMON]
GO

CREATE TABLE [dbo].[CpuUsage](
	[captureDate] [datetime] NULL,
	[sqlProcessUtilization] [int] NULL,
	[systemIdle] [int] NULL,
	[otherProcessUtlization] [int] NULL
) ON [PRIMARY]
GO

Aşağıdaki procedure ile CPU değerlerini oluşturduğumuz tabloya kaydetme işlemi yapılmaktadır.

USE [DBAMON]
GO

CREATE OR ALTER PROCEDURE [dbo].[CaptureCpuUsage]
AS
BEGIN
SET NOCOUNT ON;

-- Değişken Tanımlamaları
DECLARE @now BIGINT, @CPUCount INT;

-- CPU Bilgilerini Alıyoruz
SELECT
@now = cpu_ticks / (cpu_ticks / ms_ticks),
@CPUCount = socket_count
FROM sys.dm_os_sys_info WITH (NOLOCK);

-- Veriyi Tabloya Insert Ediyoruz
INSERT INTO [dbo].[CpuUsage] (
[captureDate],
[sqlProcessUtilization],
[systemIdle],
[otherProcessUtlization]
)
SELECT
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 (
SELECT
rec.value('(./Record/@id)[1]', 'int') AS id,
rec.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int') AS SystemIdle,
rec.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS SQLCPU,
[timestamp]
FROM (
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'%%'
) AS RingBufferData
) AS Data
-- Sadece henüz tabloda olmayan kayıtları eklemek isterseniz buraya bir WHERE NOT EXISTS eklenebilir.
ORDER BY id DESC
OPTION (RECOMPILE, MAXDOP 1);

END

Prosedür çalıştırıldığında elde edilen veriler, sistemin o anki yük durumunu net bir şekilde ortaya koyar.

Oluşturulacak job araclığı ile CPU değerleri tabloya belirli aralıklarla kaydedilir.

EXECUTE [DBAMON].[dbo].[CaptureCpuUsage] ;

Oluşturulan tabloya select çektiğimizde değerlerimizin başarılı bir şekilde döndüğü görülmektedir.

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.

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

“Şüphesiz güçlükle beraber bir kolaylık vardır. Gerçekten, güçlükle beraber bir kolaylık vardır.” İnşirah Suresi; 5-6. Ayet

Bir yanıt yazın