SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Allocated too much memory
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlteam_nas
Starting Member

2 Posts

Posted - 12/08/2012 :  23:51:42  Show Profile  Reply with Quote
I have 8 sql instances running on a virtual server, sharing 90GB among them (with Min/Max for each set).

I am pretty sure that they don't need all this memory and am trying to back this up with some evidence to release some memory from this server. I used perfmon to monitor the "Target Server Memory, Total Server Memory and Buffer cache hit ratio". I am seeing Target server memory close to the Total Memory and a hit ratio very close to 100%. However, since SQL Server will consume as much as it given with, these numbers may not mean much.

Is there any way to determine (any counters) that there is more than enough memory allocated to these instances?

Thanks

-SS

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/09/2012 :  08:08:36  Show Profile  Reply with Quote
Page Life Expectancy and Buffer Cache Hit Ratio under Buffer Manager in Perfmon are good one to look at. Page Life Expectancy below 300, and Buffer Cache Hit Ratio less than 98% are indications of memory pressure on the Server.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/09/2012 :  11:04:24  Show Profile  Reply with Quote
I would look
Page Life Expectancy
Buffer Manager/Page Reads/sec
Physical Disk/Disk reads/sec.

If the PLE is good enough and there is not high pressure on fetching data from disks(disk reads is less), you can set min or max based on it but needs to do detailed analysis before we change it.
Go to Top of Page

sqlteam_nas
Starting Member

2 Posts

Posted - 12/10/2012 :  12:26:08  Show Profile  Reply with Quote
Thanks for the help. I will go ahead and monitor these counters.

-SS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000