All,I ran into a strange issue on one of our servers when playing around with DBCC CHECKDB and resource governor. I am currently on SQL Server 2012 Enterprise Edition with 24GB memory allocated to SQL Server. Have two env's DEV and Pre-Prod. I setup an resource pool for an application to take min mem of 70% and max of 100 and the rest is for DEFAULT/INTERNAL in the classifier function. This same setup exists on DEV/Pre-Prod.

However when i run DBCC CHECKDB on PRE-PROD, say against a small database i.e. Model in this case (for testing), the command never completes , it hangs for hours doing nothing. However, it completes successfully on our DEV server. I checked if any memory grants were waiting and it did seem that the DBCC command was waiting for memory to be granted (SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null). This is totally bizzare because there is plenty of memory available and the requested memory (is around 10MB) is not being granted to DBCC.

But, If i disable Resource Governor, the DBCC CHECKDB completes in less than 5 seconds. I am not able to understand why RG is not dedicating memory from DEFAULT pool to DBCC. Does anyone have any idea about this behavior? Any help would be extremely helpful.

Someone (Argenis Fernandez??) recently had issues with memory grants and checkdb. My recollection is that they REDUCED the memory available for checkdb to make it run WAY faster and consume WAY less resources at the same time. Other perf issues can be latching, computed columns, and maxdop.

I didn't say you set the min memory for the dbcc group. I think it could be related to the 70% min for the app group. Reduce that to 40% or something much lower than 70 and see if you get the memory you need on the preprod server. Just aiming at the most likely cause at this point - nothing definitive to my recommendation.

Thank you. That suggestion was extremely helpful. I reduced the AppPool min memory to 40% and DBCC works like a charm now. I'll see if i can leave it at 40 or bump it up a little bit to workout an optimal value for it.

Thanks Again. But do you know why RG doesnt allow the requested memory for DBCC in default pool when i set my Apppool to 70% ? my max memory is 24GB, out of that 70% if i give it to APppool, i still have 7GB. DBCC only requestes 900MB out of 7GB. so technically RG should allocate rather than waiting on memory grants, correct???

krypt0rg (11/14/2013)Thank you. That suggestion was extremely helpful. I reduced the AppPool min memory to 40% and DBCC works like a charm now. I'll see if i can leave it at 40 or bump it up a little bit to workout an optimal value for it.

Thanks Again. But do you know why RG doesnt allow the requested memory for DBCC in default pool when i set my Apppool to 70% ? my max memory is 24GB, out of that 70% if i give it to APppool, i still have 7GB. DBCC only requestes 900MB out of 7GB. so technically RG should allocate rather than waiting on memory grants, correct???

Memory stuff is pretty complex, especially when you throw in RG. My guess is that not everything is taken into account and you just can't get the grant you need when you ask for it.

You may want to file a Connect item about this. It is repeatable and could be either a bug or at least an opportunity for improvement by the SQL Server product team. Remember, the memory subsystem was pretty much rewritten for SQL 2012 and there have been a few oopsies as a result.