8

We have some virtual machines that have X memory allocated to them. This amount of memory is somewhat random. It was allocated to the machine because that was the amount of memory the physical machine had, because of a vendor recommendation, or because we threw out a best guess as to how much memory we thought the instance would require.

I understand the more memory the better, but I would also like to avoid over allocating memory to the VM when it isn't necessary. The memory could be better utilized by another machine.

What would be the best way to determine an ideal amount of memory per instance that is actually active and being used? Are there other counters we should be looking at in addition to page life expectancy? We have instance that have PLE's of 10k+ and others that have 100k+.

Any insight is much appreciated. Thanks, Sam

6

A good starting point is best outlined by Glenn in his article : Suggested Max Memory Settings for SQL Server 2005/2008

Best is to baseline your database server usage during your full business cycle as that will give you the best number based on your workload using below PERFMON counters :

  • SQL Server:Buffer Manager\Page Life Expectancy
  • SQL Server:Buffer Manager\Page reads/sec
  • Physical Disk\Disk Reads/sec
  • Memory\Available Mbytes
  • SQL Server: Memory Manager - Total Server Memory
  • SQL Server: Memory Manager - Target Server Memory

Note: If you are going to use any formula or online calculator to calculate SQL Server memory configuration then best is to read Beaware of Wow… An online calculator to misconfigure your SQL Server memory ! - by Jonathan Kehayias first.

For Page Life Expectancy refer to Page Life Expectancy isn’t what you think… by Paul Randall.

An excellent reference :

How much memory does my SQL Server actually need? - by Jonathan Kehayias

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.