How do you force SQL Server to release memory?

Question

What's a good way of checking how much (actual) memory is currently being used vs. how much is SQL Server allocated to itself?

I've been resorting to memory_utilization_‌​percentage but that doesn't seem to change after running the following to release memory.

SELECT  [Memory_usedby_Sqlserver_MB] = ( physical_memory_in_use_kb / 1024 ) ,
        [Memory_utilization_percentage] = memory_utilization_percentage 
FROM    sys.dm_os_process_memory;

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

SELECT  [Memory_usedby_Sqlserver_MB] = ( physical_memory_in_use_kb / 1024 ) ,
        [Memory_utilization_percentage] = memory_utilization_percentage 
FROM    sys.dm_os_process_memory;

A solution is to drop max server memory for the SQL Server and increase it again to force SQL Server to release unused but allocated memory. However an issue with this approach is that we cannot be sure how far to reduce max server memory, hence run the risk of killing SQL Server. This is why it's important to understand how much SQL Server is 'actually' using before reducing the value for max server memory.

Answer 1

The modified script below worked for me. I needed to temporarily release a bunch of RAM held by SQLServer so that we could run some other one-off processes on the same server. It temporarily releases SQL's reserved mem space while still allowing it to gobble the mem back up as needed.

I added a built-in wait to let SQLServer actually release the mem before bumping it back to the original level. Obviously adjust the values as needed to suit your needs.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
/*** Drop the max down to 64GB temporarily ***/
sp_configure 'max server memory', 65536;  --64GB
GO  
RECONFIGURE;  
GO  
/**** Wait a couple minutes to let SQLServer to naturally release the RAM..... ****/
WAITFOR DELAY '00:02:00'; 
GO
/** now bump it back up to "lots of RAM"! ****/
sp_configure 'max server memory', 215040;    --210 GB
GO  
RECONFIGURE;    
GO  

Answer 2

SQL Server always assumes it is the primary application running. It is not designed to share resources. It will always take all the available memory and it will only release it for the operating system unless you throttle with 'max server memory'.

By design, Sql Server does not play well with others.

This sqlskills article recommends a baseline for throttling followed by monitoring and raising the throttle as needed:

https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/