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]
Merhaba ben forum sitemde bu sorunu yaşadım sql içinde onarı yaptım düzeldi tabloları onarmak gerekli sonrada optimize etmek çok faydalı .