January 7, 2025 at 3:36 am
Hello,
At my work place I recently migrated our test environment to SQL Server 2022 on Windows 2022 Data Center, in the hope of migrating our production environment in the near future. The system is work well speed wise but on random occasion the CPU utilization would go to 100% causing the system to become inaccessible to the point that I cannot even RDP into the box anymore. I eventually have to reboot it from the AWS portal. We hired DB consultants and they said that there were no issues with how SQL Server is setup and blamed it on the OS.
Has anyone experienced similar issues and what are some low hanging fruits that I am missing ?
January 7, 2025 at 1:42 pm
Do you have monitoring in place to understand why the CPU is maxing out? Without more data, I'd just be guessing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2025 at 8:27 pm
With Grant, you need a trace (xEvents) or something to look at the workload. Or is this 100% without a workload?
January 17, 2025 at 6:50 am
Yes, as others stated, with no tracing data, we are guessing. However, you may consider checking the following:
-----------------------------------------------------------------------------------------------------------------------
"tempdb" contention could cause issues. (one tempdb file for each logical core.)
https://www.sqlshack.com/sql-server-tempdb-database-and-latch-contention/
Please check for CPU pressure issues. MIN/MAX memory should be setup properly. On VMs, you would need something like 4 GB or more for Minimum memory. We need to see what kind of database waits you are experiencing:
If SQL Server consistently consumes a high percentage of CPU (above 80–90%), it could indicate CPU pressure. CPU-Related Wait Types: Check for wait types like SOS_SCHEDULER_YIELD , THREADPOOL , and CXPACKET , which are common signs of CPU contention or bottlenecks.
-----------------------------------------------------------------------------------------------------------------------------
To identify the queries that are responsible for high-CPU activity currently, run the following statement:
SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC
DBASupport
January 17, 2025 at 6:51 am
Yes, as others stated, with no tracing data, we are guessing. However, you may consider checking the following:
-----------------------------------------------------------------------------------------------------------------------
"tempdb" contention could cause issues. (one tempdb file for each logical core.)
https://www.sqlshack.com/sql-server-tempdb-database-and-latch-contention/
Please check for CPU pressure issues. MIN/MAX memory should be setup properly. On VMs, you would need something like 4 GB or more for Minimum memory. We need to see what kind of database waits you are experiencing:
If SQL Server consistently consumes a high percentage of CPU (above 80–90%), it could indicate CPU pressure. CPU-Related Wait Types: Check for wait types like SOS_SCHEDULER_YIELD , THREADPOOL , and CXPACKET , which are common signs of CPU contention or bottlenecks.
-----------------------------------------------------------------------------------------------------------------------------
To identify the queries that are responsible for high-CPU activity currently, run the following statement:
SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC
DBASupport
January 17, 2025 at 6:52 am
Apologies, the web site duplicated my response twice.
DBASupport
January 17, 2025 at 10:33 am
a little more info on the AWS config would be nice, such as type and cpu resource allocation.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply