SQL Server High CPU usage and RESOURCE_SEMAPHORE waits

Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %)

This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has resolved the issue)

Issue start: 12:15

Issue end: 14:15 (after manual AG failover)

Server Info:

SQL Server 2017  
32 logical processors (max DOP = 8)  
256 GB RAM (Max Server Memory = 180 GB, used 179 GB)

Below metrics DIDN'T change noticeably before the issue vs. after the issue started

• User Connections / sec ( avg. 200-300 )

• Batch Requests / sec ( avg. 200 and lower )

• Database Cache Memory ( ~ 150 GB )

Below metrics PEAKED considerably, which is NOT typical for this server (usually these are low):

• CPU ( over 80 % )

• Memory Grants Pending

• Lock Waits/sec, Avg. Lock Wait Time, Deadlocks

• Latch Wait Time

• Granted Workspace Memory and Reserved Server Memory

Queries:

I did not notice change in workloads for this server when issue had started

Developers also confirmed that applications did their usual job and were running usual queries, no peaks in application load

During this "high CPU usage" issue, top 10 queries by CPU didn't look unusual

All the same top 10 queries that we usually see even when CPU is normal (10-30 %)

Problem:

The problem seemed to be in a couple of related stored procedures, that application usually runs 1-4 times / second, and those usually complete within 50 ms, but during the issue, anytime I've checked the sys.dm_exec_requests (also used exec ViewSessionsConnections 'running' https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql), there were sitting like 50-70 sessions from 1 application, all trying to complete above mentioned procedures, and it was slow

When looking at top 10 queries by Duration in a Monitoring tool, top 1 and 2 these were two statements from above procedures - they did not consume a lot of CPU, BUT had excessive waits (RESOU

What are your thoughts and experience on above ?

Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ?

CPU pressure caused by bad plans. You should track and manage plan stability with Query Store, as well as investigating bad plans and remediating with additional index and statistics, and perhaps changes to the queries.

Can constant RESOURCE_SEMAPHORE waits and Memory Grants Pending cause pressure on CPU just to allocate workspace memory for queries?

No, it's the other way around. Bad plans are resource intensive, driving both large memory allocations and CPU use.

StackExchange Database Administrators Q#289016, answer score: 5