Skip to Content

DBCC Checkdb with MAX_DOP > 1 using Resource Governer?

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')

---

checkdb.png (14.5 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Nov 18, 2013 at 04:56 AM
    Add a comment
    10|10000 characters needed characters exceeded

    • 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')

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.