Sql-server – SQL Server memory

memoryperformance-tuningsql serversql-server-2012

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.

For example:

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

Best Answer

how do we check/calculate the SQL memory that is currently consumed on the instance.

That is quite simple to do. You have DMV sys.dm_os_process_memory that will give you the information. It will give you physical memory used and total memory used (RAM+Page file)

    select (physical_memory_in_use_kb/1024) as Phy_Mem_used_MB, (virtual_address_space_committed_kb/1024) as Total_mem_Used_MB
   from sys.dm_os_process_memory

But it doesn’t mean that 80% of SQL is completely utilized, as SQL will keep some space in the buffer at the backend

Once SQL Server reaches max server memory value it will keep hold of that memory unless low memory notification asks it to release memory. So yes it might be not using it all but still it will hold it. This is called caching as this helps in avoiding hard page faults.

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.

Like I said once utilized it will show all memory as utilized even when it not needs that pages in query processing. When new request comes these pages will be moved out to create space for new pages.

Related Question