system runs slowly, but SQL server can't use the RAM over 74GB?

  • 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!

    • This topic was modified 10 months, 3 weeks ago by  892717952.
  • 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.

    • This reply was modified 10 months, 3 weeks ago by  DNA_DBA.
    • This reply was modified 10 months, 3 weeks ago by  DNA_DBA.
  • 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • DNA_DBA wrote:

    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

  • Grant Fritchey wrote:

    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 !

     

     

     

  • 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply