November 18, 2021 at 9:39 am
My OS is windows server 2008 R2 Enterprise, SQL Serer is 2008 R2 Standard edition, the installed is 128GB, and RAM identified by windows is 128GB, there are about 100 users using system at the same time every day, many functions of system are running slowly,
I set SQL Server maximum RAM 120GB, I observed more than 1 one month, the biggest RAM used by SQL Server (plus OS used) can't be over 74GB. could you please help why SQL Server can't use the RAM over 74GB? many thanks!
November 18, 2021 at 10:46 am
SQL 2008R2 Standard Edition can only use 64Gb of memory, Enterprise Edition can use all of the memory available on the server. If you can upgrade to 2016 or higher, Standard Edition can use 128Gb. The max is for the buffer pool but SQL can use additional memory which is why you see 74Gb. Most of SQL's memory is the buffer pool.
November 18, 2021 at 2:07 pm
Also, don't set the max of SQL Server to max of the system. You have to leave room for the operating system to have memory as well. While you can set SQL Server to max, it's just going to fight with the OS, because the OS gets to decide how much memory it needs before it lets SQL Server have any.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2021 at 2:24 pm
SQL 2008R2 Standard Edition can only use 64Gb of memory, Enterprise Edition can use all of the memory available on the server. If you can upgrade to 2016 or higher, Standard Edition can use 128Gb. The max is for the buffer pool but SQL can use additional memory which is why you see 74Gb. Most of SQL's memory is the buffer pool.
thank you for quick reply!
But I searched onn Microsoft website, it says SQL Server 2008 R2 standard edition identifies the RAM which identified by OS, 128GB is identified by OS, so SQL Server should also identify this size RAM? thanks
November 18, 2021 at 2:27 pm
Also, don't set the max of SQL Server to max of the system. You have to leave room for the operating system to have memory as well. While you can set SQL Server to max, it's just going to fight with the OS, because the OS gets to decide how much memory it needs before it lets SQL Server have any.
Ok, thank you, I will try to change the setting, as the total RAM is 128GB, this server is only for SQL server, so I think 8GB RAM for OS should be enough, thanks !
November 18, 2021 at 2:35 pm
8gb is probably low. Jonathan Kehayias is someone I really trust on this. Follow his advice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply