Veri tabanı yönetim tarafındaki en büyük kabusumuz disk, işlemci ve bellek kaynaklarının yüksek ölçüde kullanılıyor olmasıdır. Bu yazıda SQL Server tarafından CPU kullanımını nasıl takip edeceğimizi ve yöneteceğimizi örneklendiriyor olacağız. Ayrıca CPU ile ilişki konusunda SQL Server yönetimi konusunda kullandığım farklı kaynaklardan edindiğim birkaç script paylaşıyor olacağım. SQL Server üzerindeki CPU kullanımını izlemek için sp_who2 komutu Activity Monitor veya DMV’ler ((Dynamic Management Views)) kullanılabilir. Sunucuda yüksek CPU kullanımına denk geldiğimizde bakacağımız ilk yer Task Manager aracıdır. Burada SQL Server’in yüksek CPU kullandığından emin olmamız gerekiyor. Task Manager içerisinde sqlservr.exe programının karşısında CPU kullanımı gösterilmektedir. Ardından SQL Server tarafına geçip tam olarak SQL Server’in hangi prosesin bu kaynağı kullandığını takip ederiz. Bunun için kullanacağımız Performance Monitor (perfmon) aracını kullanacağız. SQL Server, her bağlantı için tekil bir numara verir. SPID (SQL Server Process ID) olarak bilinen bu numara tam olarak hangi bağlantıya odaklanmamız konusunda yönlendirici olacak. 1 ve 50 aralığındaki SPID’leri SQL Server, kendisi kullanır. Kullanıcı bağlantılarını 50’den sonra olacak şekilde numaralandırır. SQL’deki her bağlantının işletim tarafındaki thread ile ilişkisini KPID (Kernel Process ID) numarası üzerinden sağlar. ID Thread olarak ta bilinen bu numara SQL tarafından bir thread açıldığı zaman Windows tarafından atanır. Windows tarafındaki thread’lari ayırt etmek için kullanılır. spid ve kpid ilişkisi için master.dbo.sysprocesses DMV’ye bakılabilir.

Performance Monitor aracılığıyla ilgili Thread ID’lere erişeceğiz. Control Panel’den veya CMD Prompt üzerinden perfmon komutuyla Performance Monitor aracını açıp “Add Counters” penceresini açtıralım. “Performans object” listesinden veya yeni işletim sistemlerinde “Available counters” listesinden aşağıdaki sayaçları ekleyelim.

% Processor Time
ID Thread

Sağ veya alt taraftaki “Instances of selected object” listesinden de “sqlservr” ile başlayan tüm SQL Server listesini sayaç listesine dahil edelim.

Şimdi SQL Server tarafında CPU’yu artıracak bir sorgu yazalım. Pinal Dave’in sitesinden aldığım aşağıdaki küçük sorgu SQL Server tarafında CPU kullanımına neden olacak.

DECLARE    @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,60,@T)>GETDATE()
SET    @F=POWER(2,30);

Bu sorguyu çalıştırdığımızda Task Manager tarafında SQL servisinin CPU’ya yüklendiğini görebiliriz.

Performance Monitor’u açtığımız zaman % Processor Time’ı en yüksek instance’a ait “ID Thread” sayacındaki değeri alıp aşağıdaki gibi sorguladığımız ilgili prosesin SPID’sini yakalamış oluruz.

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=1380

Bu işlemde tek thread kullanıldığı için SPID’yi kolayca bulabildik. Bazı durumlarda multithread olduğu zaman SQL Server tarafındaki SPID sabit olsa da Windows tarafındaki thread numarası her an değişebilir. Örneğin aşağıdaki gibi bir INSERT işlemi muhtemelen birden fazla thread üzerinde çalışıyor olacaktır.

CREATE TABLE CPUSTRESS
(
 SatirId UNIQUEIDENTIFIER DEFAULT NEWID(),
 AdSoyad VARCHAR(50) DEFAULT CAST(NEWID() AS VARCHAR(50))
);

SET NOCOUNT ON;
INSERT INTO CPUSTRESS DEFAULT VALUES;
GO  1000000

Bu tür durumlarda en pratik yöntem sp_who2 veya aşağıdaki sp_who2’nin beslendiği sysprocesses view’i sorgulamaktır.

SELECt * FROM master..sysprocesses
WHERE status = 'runnable' --şu anda çalışanları getirmek için
ORDER BY cpu DESC

Son olarak SQL Server’da yüksek kaynak kullanan sorguları yakalamak için aşağıdaki script kullanılabilir.

SELECT TOP 10 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),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
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_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Aynı DMV’ler kullanılarak nesne seviyesinde CPU kullanımı da raporlanabilir.

SELECT 
	DB_NAME(st.dbid) AS DatabaseName
	,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS SchemaName
	,cp.objtype AS ObjectType
	,OBJECT_NAME(st.objectid,dbid) AS Objects
	,MAX(cp.usecounts)AS Total_Execution_count
	,SUM(qs.total_worker_time) AS Total_CPU_Time
	,SUM(qs.total_worker_time) / (max(cp.usecounts) * 1.0) AS Avg_CPU_Time 
FROM sys.dm_exec_cached_plans cp 
INNER JOIN sys.dm_exec_query_stats qs 
	ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),cp.objtype,OBJECT_NAME(objectid,st.dbid) 
ORDER BY sum(qs.total_worker_time) desc

SQL Server CPU kullanım bilgilerini anlık gösterdiği gibi son 256 dk için dakika dakika tarihçeyi de göstermektedir. Bu bilgilere sys.dm_os_ring_buffers isimli DMV’den erişilebilir.

Performance Dashboard’dan alınmış aşağıdaki script, CPU kullanımı bilgilerini listelemektedir.

DECLARE @ms_ticks_now BIGINT
 
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
 
SELECT --TOP 60 
record_id
	,dateadd(ms, - 1 * (@ms_ticks_now - [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

SQLProcessUtilization alanı, SQL Server servisinin kullandığı CPU miktarını, SystemIdle alanı o anda boşta olan CPU miktarını ve
OtherProcessUtilization alanı da diğer proseslerin kullandığı CPU miktarını belirtir

Konuyla bağlantılı olarak SQL Server’in fiziksel olarak kaç tane CPU gördüğü bilgisi aşağıdaki script ile elde edilebilir.

DECLARE @xp_msver TABLE (
	[idx] [int] NULL
	,[c_name] [varchar](100) NULL
	,[int_val] [float] NULL
	,[c_val] [varchar](128) NULL
	)

INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;

WITH [ProcessorInfo]
AS (
	SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
		,CASE 
			WHEN hyperthread_ratio = cpu_count
				THEN cpu_count
			ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
			END AS [number_of_cores_per_cpu]
		,CASE 
			WHEN hyperthread_ratio = cpu_count
				THEN cpu_count
			ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
			END AS [total_number_of_cores]
		,[cpu_count] AS [number_of_virtual_cpus]
		,(
			SELECT [c_val]
			FROM @xp_msver
			WHERE [c_name] = 'Platform'
			) AS [cpu_category]
	FROM [sys].[dm_os_sys_info]
	)
SELECT [number_of_physical_cpus]
	,[number_of_cores_per_cpu]
	,[total_number_of_cores]
	,[number_of_virtual_cpus]
	,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]