3

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)

CPU

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 (RESOURCE_SEMAPHORE, LCK_M_IS)

Usually these complete within 10 ms or less, executed 1-4 times per second and not causing any issues, and now these started having duration of 4000-8000 ms per 1 execution, which was the issue

RESOURCE_SEMAPHORE is absolutely NOT typical for this server, but during the issue it was among top waits (RESOURCE_SEMAPHORE - Queries waiting for memory to be granted; total 135400234 ms in 2 hours; avg 4174 ms )

Granted Workspace Memory and Reserved System Memory in SQL Server rocketed from 0 to ~110 GB during the issue


Questions:

  1. What are your thoughts and experience on above ?

  2. Can constant RESOURCE_SEMAPHORE waits and Memory Grants Pending cause pressure on CPU just to allocate workspace memory for queries? Because when looked at top 10 queries by CPU during the issue, CPU numbers looked normal / usual

  3. How can it be that Granted Workspace Memory and Reserved Server Memory started consuming ~ 112 and 110 GB when the issue started, given that Max Server Memory is 180 GB and Database Cache Memory remained ~ 150 GB all the time? Did it over-commit memory or something like that ?

  4. 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 ?

  5. How can issue be solved in a more surgical way, without doing manual failover to secondary replica? How can I calm down the query and bring it back to 10 ms ? Plan needs to be dropped, or query recompiled, etc. ? What is the best way to do it and monitor it?

  6. Brent Ozar First Responder Kit or other diagnostic procedures - in which sequence should be executed during performance issues, to better understand what is going on ?

7
  • Was there something else going on on the server, like a system update? How big is your system page file pagefile.sys, it could be trying to use paged memory? – Charlieface Mar 31 at 10:57
  • @Charlieface no system updates, all CPU was consumed by SQL Server; pagefile was not used, machine had ~50 GB free of RAM – Aleksey Vitsko Mar 31 at 12:30
  • 3
    Are you able to reproduce the issue still on the (now) secondary replica if you run the procedure? I just wonder if you ran into a bad cached query plan due to parameter sniffing or a statistics problem on that server. Either one could've been potentially solved by a failover. – J.D. Mar 31 at 12:51
  • @J.D. not able to reproduce, because final goal of procedure is inserting or updating data into tables (p.s. there are many statements inside SPs, that are doing some calculations, using temp tables etc., and finally inserting result into permanent tables, which is not possible on secondary because of its read-only state – Aleksey Vitsko Mar 31 at 13:41
  • 1
    Well, it's quite complex topic. I would start here: brentozar.com/sql/parameter-sniffing Is it vulnerable? Basicaly (and not completely), procedure has to have input parameters and those parameters has to play in queries above not evenly distributed data. – jericzech Apr 1 at 19:53
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.