sqlservr.exe uses lots of CPU, while SQL queries does not seem to use much CPU at all

Question

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.

Answer 1

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:

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

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

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

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.

Answer 2

Open SQL Server Configuration Manager and check for other SQL Server instances.
It seems like what you check is not the right instance, so you might have to get access to the other instance.

Also it might be another SQL Server Configuration Manager version so check for other SQL versions also.

If you need to dig into those CPU consume metrics, I suggest you use DataCollector to recover interesting performance metrics.