SQL – Analizar uso y rendimiento CPU

Aplicable a SQL 2008 i SQL 2008 R2.

La siguiente consulta no sirve para hacer un seguimento historico del uso de la CPU. Podrem ver el uso de CPU que corresponde al SQL y de esta forma saber la influencia que las consultas de nuestros programes tienen sobre el rendimiento del total de la CPU.

La consulta nos devuelve la utilitzación de la CPU de los últimos 256 minutos, agrpuados por intervales de un minuto.

Consulta:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], 
               SystemIdle AS [System Idle Process], 
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
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 WITH (NOLOCK)
			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
			AND record LIKE N'%%') AS x 
	  ) AS y 
ORDER BY record_id DESC OPTION (RECOMPILE);

Vía http://sqlserverperformance.wordpress.com/ by Glenn Berry.

Leave a Reply

Your email address will not be published. Required fields are marked *