SQL Server memory

Question

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

Answer 1

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.

Answer 2

How do you define "utilized"? A page that was read into cache a second ago, would that be counted as utilized? A minute ago? An hour? A week?

The point is that there is no such things as "utilized". SQL server keep pages (and other stuff) in memory. When when there is memory pressure (internal or external) a traditional cache aging mechanism (basically) is used to remove stuff that are least recently used. There are much more details more to the memory (cache) handling of course, but in principal it is handled like any caching mechanism.

So, concepts like "utilized" or optimal configuration just doesn't exist. You can see if there are bunch of memory not being used by SQL server at all, of course. But when SQL server allocated memory, it will hang on to it unless there is memory pressure. Who knows, perhaps somebody will need that page the next second?

You can reduce max server memory and see when your performance starts to hurt, for instance.

Answer 3

Use the MemoryManagerInfo stored procedure, which can be found here:

https://github.com/aleksey-vitsko/Database-Administrator-Tools

It provides nice breakdown of how SQL Server currently uses memory:
See example on this picture below - this is what my server is currently consuming:

Roughly, Total Server Memory = Database Cache Memory + Free Memory + Stolen Server Memory (Stolen Server Memory includes Plan Cache, Locks, etc.)

Answer 4

@Siva There's a lot of good information for determining memory pressure in this StackExchange question I asked in the past: What steps can I take to determine if my server is under-provisioned for memory?

In summary, on my server, looking at the DMV sys.dm_os_wait_stats to see the top wait types were memory related and then cross checking that with the PerfMon counter "Lazy Writes Per Second" helped prove out we had heavy memory pressure and after we upgraded our server's RAM, those indicators got a lot better.