“Add more memory” was in the early days of SQL server often the solution to all problems, and for some people, it still is. But even if you think there is a memory bottleneck, adding more RAM isn’t always solving the issue.
When inspecting the sys.dm_exec_query_memory_grants view, we noticed one session was queued because SQL could not grant the requested amount of memory. Our server had 24GB of RAM available for its buffer cache, almost all was granted to 4 other sessions and the queued session requested another 6GB.
So we added another 32 GB of RAM, leaving the buffer cache with 48GB available memory in total. Where replaying the above sequence, the same issue remained. So what happened? The answer can be found in the [ideal_memory_kb] column of the sys.dm_exec_query_memory_grants view.
Apparently, SQL Server will only grant 25% of its available memory to a single session, even if the session would like to get much more (all sessions had values for [ideal_memory_kb] well above 20GB). Which in our case meant that the 4 running sessions now were granted 4 x 12 Gb of RAM, still forced the fifth session to queue.
What still puzzles me is that [max_used_memory_kb] values for the same session are almost well within the 8GB range. Rather sub-optimal at first sight.
Apart from rescheduling the sessions, I couldn’t find any alternative since rewriting/redesigning is not an option.
If anyone has another suggestion, I’d like to hear it.
I think I found out why SQL Server grants a maximum of 25% of its available memory to a single session. However Resource governor was disabled, the default WORKLOAD GROUP uses 25 as value for request_max_memory_grant_percent. So I changed this value by running
ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=5)
ALTER RESOURCE GOVERNOR RECONFIGURE
and noticed SQL Server would only grant 5% of its memory. This could have negative impact on running large requests, but will avoid queuing.