We have a SQL Server 2008 R2 instance that is running on a Windows Server 2008 R2 Enterprise server that has been allocated 128GB of memory. However, it maxes out at 70GB. Can anyone tell me how I can allow the SQL server to utilize the majority of its memory?? Thanks in advance.
If you use SQL Server 2008 R2 Standard Edition, you are limited to 64GB RAM per SQL-Server instance.
Execute the following:
select SERVERPROPERTY('Edition') SELECT name, cast(value_in_use as int)/1024 from sys.configurations where name = 'max server memory (MB)'
If the first query returns standard or web edition you are stuck and need to upgrade to the enterprise edition.
The second query returns the set maximum size of memory that the engine is allowed to allocate, if you are using the enterprise edition you need to set the limit higher by right clicking the server in the SSMS, select properties, memory and change that or execute this which will set the limits to 124 GB leaving some for the os
sp_configure 'show advanced options',1 reconfigure with override go sp_configure 'max server memory (MB)',126976 reconfigure with override go