Sql-server – How to find the detail cpu high process name which occupying most resource in the SQL Server database

monitoringperformancesql-server-2005windows

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.

Best Answer

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