December 31, 2008 at 6:14 am
We have been having problems with our cluster. The procedure cache hit % will be consistent around the 82-85% with around 500-600mb of memory allocated. Then we start to get a number of blocking alerts and this corresponds with the proc cache hit % dropping to around 35% with only 1-2mb of memory allocated. The only way I have been able to get sql to release memory to the proc cache again is to failover the cluster - not the best solution! Once we failover and back the memory allocation grows back to the 500-600mb mark and the cache hit back to the normal range. This is happening every few weeks.
Has anyone else seen of or had this problem. We are running sql2005 sp2 with 16GB memory AWE enabled. Haven't been able to find a solution to this after may hours on google.
Thanks
December 31, 2008 at 6:37 am
What is the min and max server memory settings? I think this is not a dedicated box for sql server.
MJ
December 31, 2008 at 6:39 am
Dang, that's pretty brutal.
Do you have any particular process that executes at the time that you see the cache drop off?
Have you considered running a perfmon for the SQL instance using "SQL Server:Buffer Manager-->Buffer cache hit ratio and a server side trace, wait until the issue happens and then correlate the data on the two to find the culprit? Also capture the standard perfmon data.
There might well be DMV's that will provide a lot of that data, but I'm old school and haven't gotten that far yet 😉
At the least I could say use sys.dm_os_memory_cache_entries to see what's in the cache.
December 31, 2008 at 6:40 am
Oh, and have you identified the root cause of the blocking? Where it all begins?
December 31, 2008 at 6:45 am
Hi, thanks for the swift response, the min server memory is 0 and the max is 2147483647 so should be using dynamic memory management. This box is a dedicated sql server and has no other applications running on it. I was looking at the stats for the box as we have diagnostic software on the server and it drops from 627mb allocated to the proc cache to 1 mb in under 3 minutes so with 29mb free to 0 mb free.
December 31, 2008 at 6:46 am
We had a simlar thing happening several months ago on several non-clustered SQL servers after upgrading to SQL 2005.
We ended up finally tracking it down to a 3rd party application. Do you have 3rd party apps here?
The Redneck DBA
December 31, 2008 at 6:52 am
Hi Jason,
No other apps are running on this node just SQL.
December 31, 2008 at 6:52 am
Restrict the maximum memory usage by sql server to something around 14GB by setting max server memory to 14GB. SQL will eat all available memory when required so you need to leave breathing space for OS.
MJ
December 31, 2008 at 6:53 am
Andrew Barton (12/31/2008)
Hi, thanks for the swift response, the min server memory is 0 and the max is 2147483647 so should be using dynamic memory management.
SQL could be choking the OS, not sure if that might be a part of your problem, however trim back the max memory to 15GB so that the OS has 1GB to play with.
Check that you don't have the /3GB switch enabled in the boot.ini, the /PAE one only.
December 31, 2008 at 6:57 am
Nicholas Cain (12/31/2008)
Andrew Barton (12/31/2008)
Hi, thanks for the swift response, the min server memory is 0 and the max is 2147483647 so should be using dynamic memory management.SQL could be choking the OS, not sure if that might be a part of your problem, however trim back the max memory to 15GB so that the OS has 1GB to play with.
Check that you don't have the /3GB switch enabled in the boot.ini, the /PAE one only.
We only have the /PAE. I will try your setting the max memory to 15GB and see if this helps 🙂
December 31, 2008 at 7:01 am
Nicholas Cain (12/31/2008)
Oh, and have you identified the root cause of the blocking? Where it all begins?
The blocking only occures after the procedure cache drops. Fixing the blocking issue is on my todo list along with about 50 other things 😉
December 31, 2008 at 7:08 am
Andrew Barton (12/31/2008)
Nicholas Cain (12/31/2008)
Oh, and have you identified the root cause of the blocking? Where it all begins?The blocking only occures after the procedure cache drops. Fixing the blocking issue is on my todo list along with about 50 other things 😉
New years is gonna be busy for you then 😉
December 31, 2008 at 7:14 am
I have been looking at the memory usage at the time it goes wrong and sql is using 13600mb with 13600mb allocated. This is standard and the memory usage doesn't change. The changes are that the database memory goes up from 12715mb to 12731mb but the free memory goes up to 826mb. The paging is also low at 1.97 pages/sec. what else can i look at.
December 31, 2008 at 7:35 am
Do you have the lock memory in pages set on in windows.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Also change the min memory to 8196.
December 31, 2008 at 7:40 am
TRACEY (12/31/2008)
Do you have the lock memory in pages set on in windows.http://msdn.microsoft.com/en-us/library/ms190730.aspx
Also change the min memory to 8196.
Yes for the service account it has the lock pages rights. I will set the min-max memory as per peoples responses and monitior.
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply