Suggested Max Memory Settings for SQL Server 2005/2008 (for x64)

It is pretty important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.

[@more@]

This is for x64, on a dedicated DB server.

Physical RAM MaxMem Setting

2GB 1500

4GB 3200

6GB 4800

8GB 6700

12GB 10600

16GB 14500

24GB 22400

32GB 30000

48GB 45000

64GB 59000

This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003)

Physical RAM Target Avail RAM in Task Manager

< 4GB 512MB – 1GB

4-32GB 1GB – 2GB

32-128GB 2GB – 4GB

> 128GB > 4GB

You can set this value with Transact-SQL like this:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure

-- Set max server memory = 2300MB for the server
EXEC sp_configure 'max server memory (MB)', 2300
GO
RECONFIGURE
GO

Or you can set it with SQL Server Management Studio (SSMS)