SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Author
Message
junk0
junk0
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 72
From last week our server periodically becomes very slow, I'm getting multiple RESOURCE_SEMAPHORE waittypes.

The server box specs are: 4 Quad core CPU, 64 GB memory and DB is on a SAN drive - windows 2003 R2 Ent SP2 32 bit, SQL 2005 Ent SP1 32 bit + hotfixes.
SQL configured memory is 62 GB.

At the peak of the slowdown I checked sys.dm_exec_query_resource_semaphores and it shows high waiter_count and no available_memory, also total_memory_kb is only 44200. Is this normal? Can I somehow increase the available memory for semaphore?

Maybe someone knows what might be causing this and how to avoid this problem?
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14778 Visits: 9518
Make sure that Hyperthreading is off and MAXDOP is set to 1.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
junk0
junk0
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 72
Umm, as far as I know Quad cores do not support hyperthreading.
And if it matters, MAXDOP is currently set to 4. I don't think it's a maxdop issue, because parallel queries are executing fine and the slowdown affects all queries.
Glen Sidelnikov
Glen Sidelnikov
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 804
Can you check your error log? Do you have any error messages stating "insufficient memory"?
Also, just from curiosity: why are using SP1?
junk0
junk0
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 72
Glen Sidelnikov (9/4/2008)
Can you check your error log? Do you have any error messages stating "insufficient memory"?
Also, just from curiosity: why are using SP1?

Error log is clean, no messages about "insufficient memory".
As for SP1, we had problems with our erp software with SP2, so we reverted back to SP1 + hotfixes.
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9616 Visits: 2048
The fact that you can are not using SP2 *is* a big deal.
My only thought is that you should run a sqltrace at the times this happens and figure out what are the most expensive queries.
If these can be tweaked (add indexes, change the definition of an stored procedure, etc) you are fine.

good luck


* Noel
junk0
junk0
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 72
noeld (9/4/2008)
The fact that you can are not using SP2 *is* a big deal.
My only thought is that you should run a sqltrace at the times this happens and figure out what are the most expensive queries.
If these can be tweaked (add indexes, change the definition of an stored procedure, etc) you are fine.

good luck


I'm terribly sorry, but I mixed up the sql server version, it's actually 9.0.3175 which is post SP2, we had problems with service pack and erp on sql2000 before the upgrade.
Too tired and was not thinking straight Sad

Maybe you have any other suggestions.
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3382 Visits: 3650
It sounds like you have reached a memory limit in your current configuration. We have experienced some of what you are referring to and have worked through most of it with applying service packs and CU patches but we still get them periodically. There are certain aspects of SQL Server such as query plans, procedure cache, sorts, etc that can only operate in the physical memory area of the server (virtual address space - VAS) so, with that being said it is some number less than 2 Gig in the 32-Bit world, which I am guessing you are in. That is one area that will typically see pressure first in a high activity environment.

If you can add more memory to the box and give SQL more that might be a possibility. Additional processors might allow for things to get in and out of memory faster allowing for more throughput. There is a good white paper on diagnosing memory pressure and other performance issues that you can find here - http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx - which I would recommend reading through. A more generic article can be found here - http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx

I will say too that if you have not installed CU8 then you should consider that as well as we saw some noticeable improvements with that Cumulative Update.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3382 Visits: 3650
....oops, didn't read your post very well. My guess is you are maxed out with memory on the box. Smile Still sounds like there may be some VAS pressure and the document would be good to review and go through. There are some great queries in there that give some insight into what is going on under the hood.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9616 Visits: 2048
Totally agree with David. You will probably be better off upgrading to 64 bit.

Some suggestions:
* Install Performance Dashboard and try to see which are the queries that affect you the most.
* Once you identify them you should check for missing indexes or reduction of the workload required for those.

Without further info we can't go too far.


* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search