It's regarding the SQL Server memory. When the server is running out of memory, we recommend adding additional memory to the hardware to overcome CPU utilization issues.
But before proceeding to the memory upgrade, as a part of troubleshooting, how do we check/calculate the SQL memory that is currently consumed on the instance.
SERVER NAME : XXXXXXXXXXXXX
Installed Physical Memory (RAM) : 64.0 GB
SQL Server memory currently allocated : 58982 MB --> 58.982 GB
In this case, we have only one instance (MSSQLSERVER) on the server and 80% memory has been allotted to the instance as per the standard. And 20% is allotted to Application & OS.
When we check on the Resource Monitor, it will show as 80% filled as we have set the SQL Server Memory to that.
But it doesn’t mean that 80% of SQL is completely utilized, as SQL will keep some space in the buffer at the backend. When I surfed, I got the below formula to calculate the SQL Memory consumed on the instance but these parameters have been removed on the performance monitor tool long back.
Is there a way to check, currently how much does the SQL utilized out of 80% (OR) on what metrics we can proceed with memory upgrade.
FORMULA FOR CALCULATING THE SQL MEMORY: (Below parameters has been removed from Performance Monitor)
*Database Usage(GB) = (Totalpages-Freepages-Stolenpages)8/1024=MB/1024=GB