Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I'm running into a problem when using the following SQL to identify CPU usage in the production SQL Server 2005 env.

DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info 

SELECT TOP(10) 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
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

Which shows the other CPU utilization is high in the dedicated SQL Server 2005 env.

SQL_Server_Process_CPU_Utilization  System_Idle_Process Other_Process_CPU_Utilization   Event_Time
1   79  20  11/18/2011 5:07
0   80  20  11/18/2011 5:06
1   77  22  11/18/2011 5:05
1   79  20  11/18/2011 5:04
1   78  21  11/18/2011 5:03
0   79  21  11/18/2011 5:02
0   79  21  11/18/2011 5:01
0   80  20  11/18/2011 5:00
0   80  20  11/18/2011 4:59
0   79  21  11/18/2011 4:58
1   78  21  11/18/2011 4:57
0   79  21  11/18/2011 4:56
0   80  20  11/18/2011 4:55
0   82  18  11/18/2011 4:54
3   77  20  11/18/2011 4:53
2   79  19  11/18/2011 4:52
0   79  21  11/18/2011 4:51
0   79  21  11/18/2011 4:50

I find the Other_Process_CPU_Utilization eat our db resource sometime and how can I get the detail process name for these kind of lists above through DMV? We can't login the SQL Server OS env directly so only DMV could tell us these information.

share|improve this question

1 Answer

up vote 1 down vote accepted

You can look at sys.dm_exec_query_stats to find queries which are causing the highest total_worker_time.

SELECT      TOP 20
            ST.text,
            QS.*
FROM        sys.dm_exec_query_stats          QS
CROSS APPLY sys.dm_exec_sql_text(sql_handle) ST
ORDER BY    total_worker_time
share|improve this answer
Thanks. Will run this query in our env. Does it need to order by the desc on "total_worker_time"? Does it stands for the CPU time of the Other_Process_CPU_Utilization consumes? – Vance Dec 16 '11 at 17:44
I can't find what is defined as non-SQL time ie does it exclude the time spent in the kernel. Check sys.dm_os_schedulers for non-zero runnable tasks. This would mean that task are waiting for a slot on the cpu's. The fact that the non-sql+sql is suggesting to me that part of the problem is how much cpu sql server is using under load. Fixing the badly performing queries might be the way to reduce the amount of cpu usage on the box. – MartinC Dec 16 '11 at 18:04
Almost forgot are the SQL Server data/log files excluded from any AntiVirus scanning? – MartinC Dec 16 '11 at 18:05
Thanks for the answers. We found that some backup process running so that might the root cause for the high "unknow" cpu cost. – Vance Dec 17 '11 at 1:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.