September 4, 2008 at 8:42 am
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?
September 4, 2008 at 9:58 am
Make sure that Hyperthreading is off and MAXDOP is set to 1.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 4, 2008 at 10:23 am
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.
September 4, 2008 at 11:26 am
Can you check your error log? Do you have any error messages stating "insufficient memory"?
Also, just from curiosity: why are using SP1?
September 4, 2008 at 11:44 am
Glen (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.
September 4, 2008 at 12:15 pm
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
September 4, 2008 at 1:23 pm
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.
September 4, 2008 at 2:15 pm
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
September 4, 2008 at 2:17 pm
....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“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 4, 2008 at 2:45 pm
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
October 23, 2008 at 5:42 am
Just a follow up.
After working with MS support - the problem was a memory leak, which was fixed by CU9.
So if anyone else is having the same problem - apply CU9.
October 24, 2008 at 9:02 am
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 13, 2009 at 7:24 am
TheSQLGuru (10/24/2008)
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.
We had the same problem recently. I killed all sessions that were stalled on RESOURCE_SEMAPHORE (there were several of those), and the problem went away. We are on SQL Server 2005 SP2; I wonder if SP3 has a fix for this issue.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 13, 2009 at 1:37 pm
Marios Philippopoulos (5/13/2009)
TheSQLGuru (10/24/2008)
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.We had the same problem recently. I killed all sessions that were stalled on RESOURCE_SEMAPHORE (there were several of those), and the problem went away. We are on SQL Server 2005 SP2; I wonder if SP3 has a fix for this issue.
I have only found this issue on overloaded servers. but I could be wrong.
* Noel
May 13, 2009 at 1:42 pm
noeld (5/13/2009)
Marios Philippopoulos (5/13/2009)
TheSQLGuru (10/24/2008)
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.We had the same problem recently. I killed all sessions that were stalled on RESOURCE_SEMAPHORE (there were several of those), and the problem went away. We are on SQL Server 2005 SP2; I wonder if SP3 has a fix for this issue.
I have only found this issue on overloaded servers. but I could be wrong.
Our server is overloaded, mainly because of a large number of ad-hoc sessions (ie. sessions not connecting to the databases through the business application).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply