Typically when I start seeing RESOURCE_SEMAPHORE waits (memory waits) I focus in on what queries have the largest memory grants.  In this case I ran into an issue where most queries on the system were stuck in RESOURCE_SEMAPHORE waits but no queries had a particularly large memory grant.  After digging deeper, the system had a very large amount of memory allocated to the OBJECTSTORE_LOCK_MANAGER memory clerk.  Once memory is claimed by OBJECTSTORE_LOCK_MANAGER it does not get released even if the locks that caused it to expand are no longer present.

Spotting the problem

If the problem is currently happening, you can query sys.dm_os_memory_clerks to see which are consuming the most memory.

SELECT pages_kb/1024 AS memory_MB, type, name FROM sys.dm_os_memory_clerks ORDER BY 1 DESC

Typically I would expect to see the MEMORYCLERK_SQLBUFFERPOOL towards the top of the list.  This is where SQL caches your data in memory so it’s normal that it should be at the top of the list.  If you start seeing OBJECTSTORE_LOCK_MANAGER at the top of the list you may be having issues with lock memory usage.

Temporary fix

You can clear memory usage by running the following.  You will need to be sure the “name” column from the previously provided query is passed into this:

DBCC FREESYSTEMCACHE ('Lock Manager : Node 0');

This will free pages that are used by the lock manager, but not currently associated with a lock.

Considerations for a permanent fix

Do you have lock escalations disabled?  This can be done by adding ROWLOCK hints to a query.  If you have very large updates with ROWLOCK hints it can cause a large footprint in the lock manager.  Escalations can also be disabled by creating indexes with pagelocks disabled.