SQL Server's Maximum Workspace Memory decreases over time - only instance restart to increase it

Question

This is an SQL Server 2016 CU2 instance with 15 GB configured as maximum RAM usage. MDOP is at 4. The databases in this instance are very sporadically queried by users and this is a read-only secondary replica.

The problem: on a weekly basis all queries enter into RESOURCE_SEMAPHORE state due to memory exhaustion, even when no other queries are running simultaneously in this server. After an instance restart, the queries start working again. By the images below, you'll notice that we are lacking Maximum Workspace Memory and these are really REALLY bad queries (astronomical query costs).

Before restart: only 1,8 GB of available Maximum Workspace Memory and ALL bad queries entering into a RESOURCE_SEMAPHORE state.

After instance restart: plenty of Workspace Memory (~11GB) and no RESOURCE_SEMAPHORE anymore, even for awful queries.

As you can see, it's a repulsive query that is asking for ~2GB of RAM. In this image, SSMS shows that the required memory was actually granted - because the instance was restarted. Before the restart, memory grant remains at NULL and the query stays at the RESOURCE_SEMAPHORE wait state.

Now, what we would like to know is: why is Maximum Workspace Memory decreasing over time and not being freed when there are no queries running?

Answer 1

It seems like out of a total allocated memory of 15 728 640 000 bytes, Stolen Server Memory takes up 12 828 864 000 bytes, apparently not leaving enough for normal operations.

The documentation says in SQL Server, Memory Manager Object:

Stolen Server Memory (KB)

Specifies the amount of memory the server is using for purposes other than database pages.

An answer says more:

Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

The wasteful query apparently did an enormous sort operation and SQL Server then doesn't release the allocated memory. The memory is taken from the Buffer Pool and cannot be used for anything else.

You don't have too many options:

• Improve that query so it doesn't sort huge amounts of data
• Add more memory to SQL Server
• Restart SQL Server periodically or especially after running this query.