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?
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.
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.
Allocated too much memory
Posted - 12/08/2012 : 23:51:42
