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.

• Aaron Bertrand: If there is more than one sqlservr.exe present, this means you are running multiple instances of SQL Server. Are you sure you're checking the right instance for queries with high CPU usage?
• Cenderze: @AaronBertrand Thanks for replying! Interesting suggestion. I'll keep that in mind. I tried to use the same Query on Another instance, but that gave even lower CPU usage on queries for the last 30 mins. Will definitely add monitoring on that instance as well though.

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:

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.

• Cenderze: Thanks for your thorough answer. It appears as I indeed did look at the wrong instance. One instance frequently used 80 % CPU whereas the one I looked at seldom exceeded 5 %. Thanks!

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.