Search This Blog

Wednesday, 7 January 2015

Check CPU usage by Sql query

Tip #1 Check CPU usage by Sql query:

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.Following query will help in determine CPU consumption in percentage by sql processes. Run the following query in your sql server query window:

SELECT TOP 1 100 - x.System_Idle AS CPU_USAGE
 SELECT y.record.value('(./Record/@id)[1]', 'int') AS record_id, y.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS System_Idle
  SELECT 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 y
 ) AS x
ORDER BY x.record_id DESC