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

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