I faced a strange memory issue. Buffer cache hit ratio was at 100%, Lazy writer/sec was at 0 Available Physical memory 112 GB Max memory set 112 GB out of which I could see 85 GB being used from various Perf counters. No memory related errors

The above metrics showed there was enough memory available, however the queries were very slow and the lastwaittype was RESOURCE_SEMAPHORE. I could see the memory granted column in the semaphore DMV as NULL and the requested memory was close to 2.5 GB. I saw the query text and plan and the plan was filled with Hash, merge and sort operators for the top 3 queries.

Is this a configuration issue or bug or inefficient query?

  • What version of SQL Server are you using? Also can you post the values for the other columns in the sys.dm_exec_query_memory_grants DMV? For all 4 queries (the ones hogging the memory grants and the one waiting) I care most about the ideal_memory_kb, max_used_memory_kb, used_memory_kb and granted_memory_kb. But no matter the answers there my first place to look would be query tuning as I indicate in the answer below.
    – Mike Walsh
    Nov 18 '13 at 13:25
  • Also for the configuration part of the question - can you explain the memory settings again? I understand you have SQL max mem set to 112 GB? How much memory is installed on the server?
    – Mike Walsh
    Nov 18 '13 at 13:27
  • Microsoft SQL Server 2008 R2 (SP1) - 10.50.2806.0 (X64) Feb 14 2012 18:18:40 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) Nov 19 '13 at 6:28
  • session_id scheduler_id dop request_time grant_time requested_memory_kb granted_memory_kb required_memory_kb used_memory_kb max_used_memory_kb ideal_memory_kb 299 4 1 13:14.3 NULL 21144352 NULL 7168 NULL NULL 61220802200 437 12 1 44:43.5 NULL 21144352 NULL 7168 NULL NULL 61220802200 630 29 1 15:37.8 NULL 2960032 NULL 17664 NULL NULL 2960032 657 14 1 51:04.2 NULL 2960032 NULL 17664 NULL NULL 2960032 737 8 1 07:01.1 NULL 2100304 NULL 3200 NULL NULL 2100304 331 5 1 00:36.2 NULL 2100304 NULL 3200 NULL NULL 2100304 Nov 19 '13 at 6:31
  • requested_memory_kb granted_memory_kb required_memory_kb used_memory_kb max_used_memory_kb ideal_memory_kb 21144352 NULL 7168 NULL NULL 61220802200 21144352 NULL 7168 NULL NULL 61220802200 2960032 NULL 17664 NULL NULL 2960032 2960032 NULL 17664 NULL NULL 2960032 2100304 NULL 3200 NULL NULL 2100304 2100304 NULL 3200 NULL NULL 2100304 Nov 19 '13 at 6:37

Update: One of your comments raised a really good point that I sadly didn't even think of first. This is actually a pretty common place to look at - the higher your MAXDOP - the more threads you can use. The more you can use, the more memory that can be consumed because you have more going on at once inside of a query - more parallel thread. You said your MAXDOP is 10 server wide. This "feels" high. The latest guidance is 8 if more than 8 logical cpus, or something between "0 and N" if you have less than 8. I tend to look at the number of cores per NUMA node and may go lower than 8 for some systems that are running OLTP workloads based on the number of cores per NUMA node, and sometimes I go higher. But you might look at these guidelines and consider looking at your instance wide MAXDOP and consider changing it and testing to see the impact. Going lower than 10 will reduce your memory grants for these queries.

This doesn't eliminate the need to tun queries, however, and I think there is probably room for that. Basically you need to either continually increase memory until you can satisfy all your grants, consider lowering MAXDOP, tune queries so they consume smaller memory grants, consider reducing the workload - lower the amount of queries executed at once, or get into Resource Governor settings which can artificially lower the grant amount but most assuredly increase TempDB dependence which the memory grants (memory grant % especially).

Those are the main approaches I take. If you can, starting with query tuning and perhaps considering MAXDOP changes are a good place to start. I tend to leave Resource Governor as a final resort, have good luck with tuning queries and playing with server wide or query specific DOP.

Original Answer Query memory grants are a part of SQL Server helping to ensure the right amount of memory is available before running a query. It is a way to try and make sure that you aren't running an excessive amount of high memory consuming queries that will all but guarantee a lot of memory consuming operations spilling to TempDB.

In this case I imagine your Top 3 queries are consuming a lot of memory. And you were right to look at their plans. Hashes and Sorts are probably one of the issues here. Looking at tuning queries so they are appropriate in their plans and, in turn, memory consumption is a great solution here. I doubt this is a bug - it is the way SQL Server was designed.

This article is a bit old, but the knowledge contained within still applies. It is a good place to begin understanding query memory grants.

If tuning the queries doesn't save the day here, there are other tricks that can be employed. I've also asked some questions in the comments section of your question that may help expand an answer from me or someone else.

  • In the sys.dm_exec_query_memory_grants output, I see a lot of different sessions for the same query requesting the same amount of memory with granted_memory_kb, used_memory_kb and max_used_memory_kb being NULL. The requested_memory and Ideal memory are 21144352 and 61220802200 for query 1. Requested and Ideal memory values are the same for Query 2 (2960032) and 3 (2100304). Also kindly refer my comments for the configuration details in the question section. Nov 19 '13 at 6:48
  • From the article you had mentioned I found another interesting point to be noted. We had the Max DOP set to 10 in SP_configure that is server level. However I see the dop column value as 1 for all the memory consuming queries listed as a part of sys.dm_exec_query_memory_grants DMV in the begining and at the bottom of the output I see a lot of queries with DOP column value as 10. But the granted memory is still NULL for these DOP=10 queries and the requested memory is very less in few KBs Nov 19 '13 at 6:56
  • Note that you can also INCREASE the memory grant using resources governor. By default, any single query can only use 25% of RAM. If you absolutely must consume the memory, you can increase this limit. Jan 5 '14 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.