May 24, 2016 at 1:42 pm
I have a stored proc with no input params that does a select that sometimes takes 2 minutes and sometimes doesn’t end. On one of the long runs I checks to see what was holding it up and notices it had a RESOURCE_SEMAPHORE wait, Based on what I read this is related to memory pressure. It's currently doing 208 million logical reads. My question is the RESOURCE_SEMAPHORE more prevalent if the given query is deemed to need more memory? I think I know the answer however want to make sure. I rewrote the query and it now does 600k logical reads. Should I be concerned that this RESOURCE_SEMAPHORE wait will rear its ugly head? Was I getting that wait because other resources were hogging memory and this particular optimized proc was slated to take a large chunk of memory? Or is the amount of memory projected by this proc irrelevant and the RESOURCE_SEMAPHORE wait is strictly because of other processes trying to hog memory? I’m wondering if because I optimized the proc I won’t have to worry about this wait. Does anyone have experience w/ the RESOURCE_SEMAPHORE wait type?
May 25, 2016 at 4:55 am
It's an indication of memory pressure. It might be because of that one query, or it might be a combination of your queries. I wouldn't be able to tell you for sure without a lot more information. It's also possible that it's related, in part, to your statistics. If the statistics are out of date or incorrect, then the calculated memory requirements for the execution plan might be too low. That will also lead to waits on memory as it allocates enough to satisfy the actual needs. I would start with the easiest part of a possible solution, ensuring the statistics are up to date. After that, it's all about tuning your queries and adjusting your structure and code to ensure that you're not starving the system of memory. It might also be possible that you simply don't have enough memory for the processing you're doing.
"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
May 25, 2016 at 6:50 am
Perfect, that's what I was thinking. With that in mind I suspect my issue will come back even though I optimized the procedure. My issue is our prod sql server houses revenue based data as well as reporting and our reporting guys are good sql developers however either don’t care to take the time to write optimized sql or don’t know how. Therefore, I'm left w/ non-revenue sql procs affecting performance on the sever and as such affecting performance on our revenue based back end databases resources. My plans are to move the revenue generating databases onto their own servers however that will take some time. The current production server has 174 gigs of mem allocated for sql. I optimizes a reporting proc the other day that was doing 11 billion logical reads;). They're killing me!!! What about buffer pool extensions? Has anyone had any good or bad experiences with it? Would buffer pool extensions help increase the memory pool available in this instance?
May 25, 2016 at 6:57 am
Buffer pool extensions are for the data cache, specifically clean pages, not for memory grants for query execution (which is what resource semaphore waits are for)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply