All spids showing resource semaphore waits

Question

Post reply

All spids showing resource semaphore waits

askcoffman

Mr or Mrs. 500

Points: 580
More actions
August 28, 2022 at 3:25 am

#4081281

I have a data warehouse server (sql 2017 Enterprise, 8 CPU, 64GB RAM) that has been randomly plagued with resource semaphore waits during the nightly ETL processing. When the issue is noticed, I run sp_whoisactive and EVERY session is showing resource semaphore waits. Some scheduled jobs that run in 5 seconds will take 15 minutes and others that normally run in minutes will not finish the same day. I have spotlight running to monitor when this issue occurs, but the culprit has not surfaced. I went a step further and scheduled sp_whoisactive results to be stored every 5 minutes. This was helpful, but only told me when the waits started and not any queries that were consuming large amounts of the buffer cache. I am considering starting another job to record what tables/indexes are being used in the buffer cache. It seems to me that whatever could be causing this to happen should be obvious on the monitoring services I have in place.
Am I on the right track with digging into the cache? Could hardware be at fault? Bad... RAM?
I will happily provide more details if needed.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 2

Isn't a hardware fault.

Resource_semaphore:

SQL Server decided on an execution plan that requires more memory.

http://blog.sqlgrease.com/how-to-fix-resource_semaphore-waits/#:~:text=Typically%20RESOURCE_SEMAPHORE%20waits%20occur%20as%20a%20result%20of,need%20tuning%20%28i.e.%20adhoc%20queries%2C%20new%20code%20releases%29.

https://www.sqlskills.com/help/waits/resource_semaphore/

Add more server ram or since you're on sql server 2017, you can use query store to review those executionsplans to see which ones are using the most memory at that time.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

(1) What is the 'cost threshold for parallelism' on that instance? If it's too low, SQL could be trying use parallelism far too often.

(2) Check the SQL log for error-type messages. Are there indications/messages from SQL that it needs more RAM?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

To add to Jo's and Scott's good suggestions, check on MAXDOP while you're at it If it's set to "0" (something can use ALL the CPU's at the same time), consider changing it. My general rule of thumb is to set it to Processor Count/4 with a max of 4 in most cases. In other words, no one thing gets to use more than a quarter of the horsepower by default for smaller systems and if you need more than 4 on larger systems, you might want to have a look at your code. Yep... there are some processes that might rightly benefit from the max that a Numa Node can offer but you can set MAXDOP in the code for those.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

askcoffman Mr or Mrs. 500 Points: 580 More actions · Answer 5

Following up on this, we gave up trying to pinpoint what query/queries were causing the issues. Threw 64 GB of RAM at it which resolved the issue. We spotted lots of heavy hitting queries through whoIsActive and query store, but not enough DBAs to tune them fast enough.

Thanks for all of your suggestions!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

askcoffman wrote:

Following up on this, we gave up trying to pinpoint what query/queries were causing the issues. Threw 64 GB of RAM at it which resolved the issue. We spotted lots of heavy hitting queries through whoIsActive and query store, but not enough DBAs to tune them fast enough.
Thanks for all of your suggestions!

Awesome. I'm glad that "fixed" it for you. Adding memory is a good, inexpensive way to "fix" such things. There's a reason why I have "fix" in quotes... it's not a "fix"... it's a "stop the flames" patch. You haven't put the fire out and it's going to restart when your data grows. The unfortunate part of adding memory to do such a "fix" is that it makes people complacent and they never go back and do an actual fix in the code until, once again, it's a crisis.

The best way to keep it from becoming a crisis, again, is to use tools like WhoIsActive, Query Store, or some of Brent Ozar's sp_Blitz goodies to re-identify those "heavy" procs and assign some to fix, say, the top 3 in a "Continuous Improvement Program". Sometimes the real fix will be as easy as correctly identifying some good indexes to add. Other times, it's going to entail code changes such as removing things like non-SARGable criteria, removing accidental many-to-many joins, and maybe even splitting queries up using "Divide'n'Conquer" methods where the "core" of the query is identified and maybe even stuff into a Temp Table and then join to that.

Of course, "Continuous Improvement" doesn't mean that you stop at the first 3. 😉 Nor is it about fixing just the code. If you teach the Developers what to look for as they write new code, you'll eventually stop producing such problems and the "Continuous Improvement" will see an end.

One of the keys that I remind people of is that "Set-Based <> All-in-One-Query".

And thank you kindly for the feedback about how adding memory helped.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

askcoffman wrote:

Following up on this, we gave up trying to pinpoint what query/queries were causing the issues. Threw 64 GB of RAM at it which resolved the issue. We spotted lots of heavy hitting queries through whoIsActive and query store, but not enough DBAs to tune them fast enough.
Thanks for all of your suggestions!

I like the quote "not enough DBAs to tune them fast enough".

I sure hope there are more people writing queries than just your DBAs.

The errors clearly show RAM issues. What procedure has been followed to determine this sql server instance't topology.

Enterprise edition + 8CPU =license cost

64 GB of RAM will be a bottleneck on most DWH systems. Processing masses of data just needs a vast amount of RAM to have it work smoothly.

This RAM is not involved in MS licensing costs, so it's just the hardware costs involved.

Maybe a next task for your DBA is to refresh your devs SQL (query) knowledge and have a chance to have better written code hit your server.

This is the best investment your company can make with a very quick ROI.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me