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 comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Nov 18, 2013 at 04:56 AM
    Add 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')