In one of our PROD Servers...

The Total Physical RAM is 128GB The Total RAM I assigned for SQL Server is 122GB.

When I checked the SQL Server there were no Open Transactions, No Blockings....But still the Memory Consumption is higher and it show 99% in Task Manager and performace has been slowed down.

Should we increase the Memory or I don't understand if OS or SQL Server does not have enough Memory. Please advice if we need to allot some enough Memory to OS too...

  • 1
    I'd say 6 GB is a little on the low side for the OS but it should be OK. Have you checked to see if any process other than SQL Server is stealing the RAM? Quite often I see that SSRS, SSIS or SSAS are the culprit as they are not included in SQL Server's MAX MEMORY setting. But it could be any other application running on the server. – James Anderson Oct 28 '16 at 8:09
  • 1
    I have a reference for setting the Max Server Memory (see bornsql.ca/memory) on your instance, based on a recommendation by Jonathan Kehayias. The value assumes nothing but SQL Server is running on the server. You'll note Jon's recommendation is 109GB. It is perfectly normal for SQL Server to use as much RAM as you allocate it, to keep as much of the database in RAM, to make queries perform faster. – Randolph West Oct 29 '16 at 2:13