SQL Server – Setting Maximum Server Memory Higher Than Available to OS

memorysql serversql-server-2008-r2

I have a server that is being repurposed into a SQL Server. It came with more physical memory than the 32GB that can be seen by my OS (Windows Server 2008 R2).

Management would like me to configure the Maximum server memory used by SQL (SQL Server 2008 R2 Service Pack 3) to be higher than what is seen by the OS. I can change it in the properties of SQL Server via SQL Server Management Studio. Will SQL Server be able to utilize the additional RAM? Or will it exhaust all memory made available to the OS?

Best Answer

Presumably you have the "Standard" edition of Windows Server 2008 R2.

You'll need to move to the Enterprise Edition (or DataCenter) of Window Server 2008 R2 to utilize more than 32GB, regardless of the "max server memory" setting in SQL Server.

Windows memory limitations can be seen on MSDN here.

Keep in mind, SQL Server also has memory limitations by edition. SQL Server 2008 R2 Standard Edition limits memory to a maximum of 64GB.