<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Server becomes very slow [RESOURCE_SEMAPHORE]...
35 posts, Page 1 of 4
1
2
3
4
»
»»
Server becomes very slow [RESOURCE_SEMAPHORE] waittype.
Rate Topic
Display Mode
Topic Options
Author
Message
junk0
junk0
Posted Thursday, September 04, 2008 8:42 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 5,
Visits: 70
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
RBarryYoung
RBarryYoung
Posted Thursday, September 04, 2008 9:58 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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
junk0
junk0
Posted Thursday, September 04, 2008 10:23 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 5,
Visits: 70
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
Glen Sidelnikov
Glen Sidelnikov
Posted Thursday, September 04, 2008 11:26 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, January 22, 2013 12:08 PM
Points: 371,
Visits: 794
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
junk0
junk0
Posted Thursday, September 04, 2008 11:44 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 5,
Visits: 70
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
noeld
noeld
Posted Thursday, September 04, 2008 12:15 PM
SSCertifiable
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260,
Visits: 1,977
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
junk0
junk0
Posted Thursday, September 04, 2008 1:23 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 5,
Visits: 70
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
David Benoit
David Benoit
Posted Thursday, September 04, 2008 2:15 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,063,
Visits: 3,441
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
David Benoit
David Benoit
Posted Thursday, September 04, 2008 2:17 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,063,
Visits: 3,441
....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
noeld
noeld
Posted Thursday, September 04, 2008 2:44 PM
SSCertifiable
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260,
Visits: 1,977
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 »
35 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
