6

We have a Windows 2008 r2 server (on hyper v) and on this we are running a SQL server. This database is approx 300 gb big. The server has 32 GB ram. In the SQL Server properties I saw the following:

enter image description here

enter image description here

I just wondered is this OK or is this too much memory. According to www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/ I read that a server with 32 GB needs max 29000

Can someone advise. Thanks

Mat
  • 10,289
  • 4
  • 43
  • 40
bobby
  • 69
  • 2

2 Answers2

11

Generally in our setup guide, I recommend that you leave 4GB or 10% free for the operating system, whichever is larger. In this case, 4GB is larger, so you'd set max server memory to 28000.

The free memory is for:

  • Other services like SSIS, SSAS, SSRS (and if you use those extensively, you'll want to leave more memory aside)
  • Remote desktopping in (I wish people didn't, but so many folks RDP in and run SSMS or other tools)
  • Systems management apps (Dell OpenManage, HP SIM, etc that fire up and gobble memory)
  • Backup tools

If you monitor the Perfmon counter for Available Memory over time, and you consistently notice that most of the memory is going unused, then you can inch up max server memory to a higher number.

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
5

I created an Excel sheet with a chart based on the formula which was recommended by Jonathan Kehayias in his article:

How much memory does my SQL Server actually need?

Jonathan Kehayias:

...and in my book the recommendation that I make is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

This is a screenshot of the chart based on the formula:

Memory Sizing Chart

In my past years of being a SQL Server DBA this formula has been so far sufficient to leave enough free memory for SQL Server by-products that don't pull from the MIN and MAX memory settings and leave enough memory for the Operating System.

With 32 GB of RAM the recommended setting would be 25 GB or 25600 MB for the MAX memory setting for SQL Server.

John K. N.
  • 18,854
  • 14
  • 56
  • 117