Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC CHECKDB Hangs - Resource Governor Issue Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 9:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 19, Visits: 407
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.
Post #1513943
Posted Wednesday, November 13, 2013 11:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1513995
Posted Wednesday, November 13, 2013 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 19, Visits: 407
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?
Post #1514034
Posted Wednesday, November 13, 2013 2:08 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1514057
Posted Wednesday, November 13, 2013 2:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 19, Visits: 407
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
Post #1514065
Posted Thursday, November 14, 2013 8:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1514312
Posted Thursday, November 14, 2013 9:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 19, Visits: 407
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???
Post #1514345
Posted Thursday, November 14, 2013 12:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1514438
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse