Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Server becomes very slow [RESOURCE_SEMAPHORE] waittype. Expand / Collapse
Author
Message
Posted Thursday, September 4, 2008 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 11:39 PM
Points: 5, 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?
Post #563869
Posted Thursday, September 4, 2008 9:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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."
Post #563949
Posted Thursday, September 4, 2008 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 11:39 PM
Points: 5, 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.
Post #563978
Posted Thursday, September 4, 2008 11:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
Can you check your error log? Do you have any error messages stating "insufficient memory"?
Also, just from curiosity: why are using SP1?
Post #564024
Posted Thursday, September 4, 2008 11:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 11:39 PM
Points: 5, 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.
Post #564039
Posted Thursday, September 4, 2008 12:15 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #564065
Posted Thursday, September 4, 2008 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 11:39 PM
Points: 5, 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

Maybe you have any other suggestions.
Post #564124
Posted Thursday, September 4, 2008 2:15 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 2,049, Visits: 3,596
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #564157
Posted Thursday, September 4, 2008 2:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:02 AM
Points: 2,049, Visits: 3,596
....oops, didn't read your post very well. My guess is you are maxed out with memory on the box. :) 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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #564160
Posted Thursday, September 4, 2008 2:44 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #564192
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse