DBCC Checkdb with MAX_DOP > 1 using Resource Governer?

[former_member211576]

Hi experts,

   Someone taught me a technique to run checkdb with MAX_DOP > 1 using resource governer. I  tried it but failed. what's wrong?

---

CREATE RESOURCE POOL [zCHECKDB] WITH(min_cpu_percent=0,

                    max_cpu_percent=100,

                    min_memory_percent=0,

                    max_memory_percent=100,

                    AFFINITY SCHEDULER = AUTO

)

GO

CREATE WORKLOAD GROUP [ZCHECKDB] 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=16) USING [zCHECKDB]

GO

CREATE FUNCTION dbo.classify_zCHECKDB() RETURNS sysname

WITH SCHEMABINDING AS

BEGIN

    DECLARE @grp_name sysname

      IF (APP_NAME() LIKE '%Studio%')

          SET @grp_name = 'zCHECKDB'

    RETURN @grp_name

END;

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.classify_zCHECKDB);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

dbcc checkdb('TCD')

---

[Sriram2009]

Hi

Could you share the failed detail log? and also refer the Microsoft & SAP Note

http://connect.microsoft.com/SQLServer/feedback/details/468694/maxdop-option-in-dbcc-checkdb

http://beyondrelational.com/modules/24/syndicated/614/Posts/17824/sql-server-configration-settings-m...

http://www.quest.com/whitepapers/tuning_article_1_final.pdf

142731 - DBCC checks of SQL server

Regards

Ram

[former_member211576]

Hi SS,

   There is no error messages in errorlog.

"2013-11-18 13:18:11.96 spid251     Resource governor reconfiguration succeeded."

  I think I have followed MS's procedure to configure but it just ignores the resoruce governer setting and run checkdb with global max degree of parallelism = 1.

  Apparently, I missed something. I have read Ms' document over and over again but still can't find out what's going on.

[Sriram2009]

Hi Dennis

Kindly check this script & let us know the status:

_____________________________________________________

CREATE RESOURCE POOL [zCHECKDB] WITH(min_cpu_percent=0,

                    max_cpu_percent=100,

                    min_memory_percent=0,

                    max_memory_percent=100, )             

GO

CREATE WORKLOAD GROUP [ZCHECKDB] 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=16) USING [zCHECKDB]

GO

CREATE FUNCTION dbo.classify_zCHECKDB() RETURNS sysname

WITH SCHEMABINDING AS

BEGIN

    DECLARE @grp_name sysname

      IF (APP_NAME() LIKE '%Studio%')

          SET @grp_name = 'zCHECKDB'

    RETURN @grp_name

END;

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.classify_zCHECKDB);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

dbcc checkdb('TCD')

______________________________________________________________

Regards

SS

[former_member211576]

Hi SS,

    I don't understand what do you mean. This script is created a resource group and run checkdb using management studio. The classifier function will run MAX_DOP = 16 because app_name like '%Studio%'.

  If I configure everything correctly like MS guy did, it will run 16 DBCC CHECK TABLE in parallel.

[Sriram2009]

Hi Dennis

Someone taught me a technique to run checkdb with MAX_DOP > 1 using resource governer. I  tried it but failed. what's wrong?

As you mention that the DBCC cehckdb with max_dop>1 script failed. we have do some changes in the script and check the script it was working fine in our side, could you pls check the same script which I  pasted in my previews message. Kindly check the scripts and let us know the status. (All switch settings value are define based on you requirement)

Regards

SS

[former_member211576]

Hi SS,

   Thanks for your reply. I use examdiff.exe and find you remove "AFFINITY SCHEDULER = AUTO", right?

   It is strange.

I use SQL server 2012 E.E.

'max degree of parallelism' = 1

clear up resource governor

run your script

but the problem persists.

---

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)

GO

ALTER RESOURCE GOVERNOR DISABLE

GO

DROP FUNCTION dbo.classify_zCHECKDB

GO

DROP WORKLOAD GROUP ZCHECKDB

GO

DROP RESOURCE POOL ZCHECKDB

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

--

CREATE RESOURCE POOL [zCHECKDB] WITH(min_cpu_percent=0,

                    max_cpu_percent=100,

                    min_memory_percent=0,

                    max_memory_percent=100 )            

GO

CREATE WORKLOAD GROUP [ZCHECKDB] 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=16) USING [zCHECKDB]

GO

CREATE FUNCTION dbo.classify_zCHECKDB() RETURNS sysname

WITH SCHEMABINDING AS

BEGIN

    DECLARE @grp_name sysname

      IF (APP_NAME() LIKE '%Studio%')

          SET @grp_name = 'zCHECKDB'

    RETURN @grp_name

END;

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.classify_zCHECKDB);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

dbcc checkdb('TCD')