proc cache hit % drops to 30 with low memory allocation

Question

Post reply

proc cache hit % drops to 30 with low memory allocation

Andrew Barton

Mr or Mrs. 500

Points: 520
More actions
December 31, 2008 at 6:14 am

#126232

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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

What is the min and max server memory settings? I think this is not a dedicated box for sql server.

MJ

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

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.

Shamless self promotion - read my blog http://sirsql.net

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Oh, and have you identified the root cause of the blocking? Where it all begins?

Shamless self promotion - read my blog http://sirsql.net

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 4

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.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

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

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 6

Hi Jason,

No other apps are running on this node just SQL.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 8

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.

Shamless self promotion - read my blog http://sirsql.net

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 9

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 🙂

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 10

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 😉

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 11

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 😉

Shamless self promotion - read my blog http://sirsql.net

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 12

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.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 13

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.

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 14

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