0

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.

  • Where are you measuring 70GB? How big is your active data? What is the edition of SQL Server? What is max server memory set at? Is this a VM? Using what hypervisor? If you're using dynamic memory and Hyper-V see this post. – Aaron Bertrand May 20 '15 at 18:44
  • Maybe it just doesn't need more up to now – Tom V May 20 '15 at 18:45
  • If it is not using means it does not require. Well if you really like to see, create maintenance plan which would rebuild all index and at same time when this job is running run some select * on big tables you would see memory increasing. THIS IS NOT ADVISED THOUGH. And yes hope you are not referring task manager for SQL Server memory utilization – Shanky May 20 '15 at 19:02
  • This is actually for one of our client sites. They have continuously complained about slowness coming from the application that uses the database. It was suggested to me that we allocate more memory, but if what you all are saying, I don't need to allocate more memory because it's not using all of it due to not needing it. So now I have to figure out all the other possible reasons why there would be slowness. Actually, I didn't verify until now, but they ARE referring to task manager memory utilization! – Ryan Neff May 20 '15 at 19:26
  • Ask them to use select * from sys.dm_os_process_memory to see memory utilization – Shanky May 21 '15 at 4:06
0

If you use SQL Server 2008 R2 Standard Edition, you are limited to 64GB RAM per SQL-Server instance.

Memory Supported by the Editions of SQL Server

  • Actually, although the SQL Server service is limited to 64GB, plus some non-managed memory added up to 70GB for @RyanNeff, adding additional memory can improve overall performance. This would provide memory for other tools, such as SSIS, and potential other processes that also will need memory. – RLF May 21 '15 at 0:47
0

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
  • Standard it is! However, the second query yielded a max server memory (MB) of 119.......not 119000.....just 119. So even though the max server memory is set to 122 GB in server properties , it really doesn't mean anything since standard edition is limited to 64/70 GB? – Ryan Neff May 22 '15 at 15:25
  • yes, correct, my mistake, forgot how stingy 2008R2 was. You max out at 64GB – Spörri May 25 '15 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.