SQL Server Performance – High CPU Usage by sqlservr.exe with Low Query CPU Usage

sql server

I've found a Query which supposedely should determine how much CPU is being used by SQL queries, system idle and other processes. This is the Query:

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

SELECT TOP(30) 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
where SystemIdle >= 80
ORDER BY record_id DESC;

This shows that the CPU under the column SQL Server Process CPU Utilization is rather stable at around 0-10%.

When I'm checking the process task at the server, however, I can see that the CPU usage is very high at 90-100% from time to time. When I check what processes are using the most CPU in the task manager, I can conclude that sqlservr.exe uses much more CPU than the other processes (75+%).

In fact, there are more than one sqlservr.exe present.

My question is, really, how can the SQL queries use Little CPU, while sqlservr.exe uses very much?

I have some queries running in the background, by for instance using SQL jobs, but these should be captured in the CPU Query.

Best Answer

You have multiple instances of SQL Server running, and I suspect you are checking the wrong one for CPU activity.

In this system, I have two instances of SQL Server. In the plain Task Manager view I just see multiple SQL Server processes:

enter image description here

But if I expand each item, I can see the actual instance name involved with each:

enter image description here

In older versions of Task Manager you should be able to add process id (or inspect the user associated with the process):

enter image description here

If you can't identify the instance by name there, then you could look up the process id in Configuration Manager:

enter image description here

This makes it easy - in my case I know that the S1TARGET instance is the one that's using the CPU, so that's where I'm going to run queries like the one in the question.

Related Question