SQL Server - How to determine ideal memory for instance? 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
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