F5F Stay Refreshed Hardware Desktop Question: SQL Server Using Full CPU on Windows Server (Intel Sapphire Rapids) – CPU Fluctuates Between 20% and 100%

Question: SQL Server Using Full CPU on Windows Server (Intel Sapphire Rapids) – CPU Fluctuates Between 20% and 100%

Question: SQL Server Using Full CPU on Windows Server (Intel Sapphire Rapids) – CPU Fluctuates Between 20% and 100%

U
unormal2
Member
Find
125
07-03-2016, 07:49 PM
#1
Hello everyone,
I’m facing a significant performance problem on one of our servers and would value any advice or troubleshooting help.

System Specifications
OS: Windows Server
CPU: Intel Xeon (Sapphire Rapids), 4 sockets, 16 virtual processors
RAM: 180 GB total (≈ 86 GB in use / 93 GB free)
Primary Load: Microsoft SQL Server (SQL Server Windows NT – 64 Bit)

Issue Description
Over the past few days, CPU utilization has been highly erratic, changing rapidly between 20%, 60%, and 100%, then returning to normal even without heavy user activity. SQL Server (sqlservr.exe) consistently reports 97%–99% CPU usage in Task Manager. The CPU often jumps to 100%, drops abruptly, and then spikes again. Memory usage remains stable around 48%. There are no other processes consuming excessive resources. The server has been operational for about 3.5 days.

Attached screenshots (Task Manager → Processes / CPU / Memory graphs) illustrate the usage trends, with SQL Server occupying most of the CPU time. All pictures
Reply
Reply
U
unormal2
07-03-2016, 07:49 PM #1

Hello everyone,
I’m facing a significant performance problem on one of our servers and would value any advice or troubleshooting help.

System Specifications
OS: Windows Server
CPU: Intel Xeon (Sapphire Rapids), 4 sockets, 16 virtual processors
RAM: 180 GB total (≈ 86 GB in use / 93 GB free)
Primary Load: Microsoft SQL Server (SQL Server Windows NT – 64 Bit)

Issue Description
Over the past few days, CPU utilization has been highly erratic, changing rapidly between 20%, 60%, and 100%, then returning to normal even without heavy user activity. SQL Server (sqlservr.exe) consistently reports 97%–99% CPU usage in Task Manager. The CPU often jumps to 100%, drops abruptly, and then spikes again. Memory usage remains stable around 48%. There are no other processes consuming excessive resources. The server has been operational for about 3.5 days.

Attached screenshots (Task Manager → Processes / CPU / Memory graphs) illustrate the usage trends, with SQL Server occupying most of the CPU time. All pictures

Reply
Reply
P
P4y0D_LuckyLp
Junior Member
Find
32
07-08-2016, 11:38 PM
#2
check activity in smss?
Reply
Reply
P
P4y0D_LuckyLp
07-08-2016, 11:38 PM #2

check activity in smss?

Reply
Reply
X
xLikax
Member
Find
173
07-14-2016, 12:12 PM
#3
Sure, I can clarify that. Would you like me to elaborate further on what that involves?
Reply
Reply
X
xLikax
07-14-2016, 12:12 PM #3

Sure, I can clarify that. Would you like me to elaborate further on what that involves?

Reply
Reply
P
Pix3l_M4rc
Junior Member
Find
2
07-14-2016, 09:09 PM
#4
I used these two queries in SQL and learned about the CPU % Utilization.
First query:
SQL:
SELECT TOP 10
total_worker_time/1000 AS CPU_ms,
execution_count,
(total_worker_time/execution_count)/1000 AS AvgCPU_ms,
text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_worker_time DESC;
Second query:
SQL:
SELECT TOP 10
[total_user_cost] * [avg_user_impact] AS Impact,
last_user_seek,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig
ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
ORDER BY Impact DESC;
Thanks for the assistance ^^, if you want more ideas and suggestions I need to hear!
Reply
Reply
P
Pix3l_M4rc
07-14-2016, 09:09 PM #4

I used these two queries in SQL and learned about the CPU % Utilization.
First query:
SQL:
SELECT TOP 10
total_worker_time/1000 AS CPU_ms,
execution_count,
(total_worker_time/execution_count)/1000 AS AvgCPU_ms,
text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_worker_time DESC;
Second query:
SQL:
SELECT TOP 10
[total_user_cost] * [avg_user_impact] AS Impact,
last_user_seek,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig
ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
ORDER BY Impact DESC;
Thanks for the assistance ^^, if you want more ideas and suggestions I need to hear!

Reply
Reply