SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC CHECKDB Hangs - Resource Governor Issue


DBCC CHECKDB Hangs - Resource Governor Issue

Author
Message
krypt0rg
krypt0rg
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 632
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.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12389 Visits: 8548
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
krypt0rg
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 632
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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12389 Visits: 8548
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
krypt0rg
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 632
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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12389 Visits: 8548
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
krypt0rg
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 632
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
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12389 Visits: 8548
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! Cool

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search