SQL Server Memory Configuration Guidelines

Note: These guidelines are general in nature and are designed for a single instance of SQL Server.

The minimum SQL Server memory should be changed from the default setting of zero.The reason for this is that when the operating system comes under memory pressure it will tell the applications running on the server to return memory to the operating system. SQL Server's response is to release memory based on the minimum memory setting. The recommendation is to set the minimum memory to 50% of the server's physical memory so that SQL Server can co-operate with the operating system but not sacrifice all of it's memory.

The maximum SQL Server memory should be changed from the incredibly high number provided to a smaller number. A good value for the maximum memory setting is to either leave 2GB or 10% of the server memory free for the operating system and other applications, whichever is greater.

It is also recommended to not use the "Lock Pages in Memory" feature as this "may" have a negative impact on server performance. The rationale behind this is that the by locking pages in memory we are really just locking in buffer pages (data cache). This can cause memory pressure if other types of SQL cache or processes that run outside of SQL Server need to allocate more memory yet can't grab it because it's reserved for SQL Server.