DBCC CHECKDB Hangs - Resource Governor Issue

Question

DBCC CHECKDB Hangs - Resource Governor Issue

krypt0rg

SSC Veteran

Points: 277
More actions
November 13, 2013 at 9:06 am

#300140

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.
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

krypt0rg SSC Veteran Points: 277 More actions · Answer 2

Thanks. I tried that as well. I created a seperate pool for it and only made 10% of Memory Available for DBCC. yet same result ?Anything i am doing wrong here?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

maybe preprod is actually consuming that 70% of memory the app pool wants but dev isn't? I would be quite leery of setting a MIN memory so high personally.

If you set min to 40% does it exhibit the same behavior?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

krypt0rg SSC Veteran Points: 277 More actions · Answer 4

i think i confused you. i did not set a min memory for DBCC pool. here are my pool settings for DBCC and APP pools:

CREATE RESOURCE POOL [DBCCMaint]

WITH (min_cpu_percent=0,

max_cpu_percent=100,

min_memory_percent=0,

max_memory_percent=10,

cap_cpu_percent=100,

AFFINITY SCHEDULER = AUTO);

GO

CREATE WORKLOAD GROUP [wg_DBCCMaint]

WITH (group_max_requests=0,

importance=Medium,

request_max_cpu_time_sec=0,

request_max_memory_grant_percent=25,

request_memory_grant_timeout_sec=0,

max_dop=0)

USING [DBCCMaint];

GO

my classifier func:

-- APPPOOL:

CREATE RESOURCE POOL [APPPool] WITH(min_cpu_percent=70,

max_cpu_percent=100,

min_memory_percent=70,

max_memory_percent=100,

cap_cpu_percent=100,

AFFINITY SCHEDULER = AUTO

)

GO

-- Classifier Func:

ALTER FUNCTION [dbo].[Fn_RGClassifier]()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

DECLARE @WorkloadGroup SYSNAME

IF (SUSER_SNAME() IN ('DOMAIN\APPUser'))

SET @WorkloadGroup = 'APPGroup'

ELSE IF SUSER_NAME() IN ('DBCCUser')

SET @WorkloadGroup = 'wg_DBCCMaint'

ELSE

SET @WorkloadGroup = 'default'

RETURN @WorkloadGroup

END

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

krypt0rg SSC Veteran Points: 277 More actions · Answer 6

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???

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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.

You are welcome - glad you got it working! 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service