Post reply

Which is used MAXDOP of Resource Governor or SQL Instance Max Degree Parallelism setting?

  • December 4, 2014 at 8:18 pm

    #299217

    It's been awhile since I last worked with Resource governor and I can't remember.

    Which is used MAXDOP of Resource Governor or SQL Instance Max Degree Parallelism setting if at the instance level I set maxdop to 1... but allow resource gov to use maxdop of 8?

    in detail:

    If in 'sp_configure' - 'max degrees of parellism' = 1 (set to MAXDOP 1)

    Then I setup RESOURCE GOVERNOR

    I create a workload group, resource pool, classifier function to look for my sql login (testing ..etc.

    (set to MAXDOP 8)

    ALTER WORKLOAD GROUP ReportWorkloadGroup WITH (IMPORTANCE = HIGH, MAX_DOP = 8)

    USING HighPriorityPool ;

    I turn on resource governor then run the query under my login which is in the classifier function... I would expect a parallel query plan ?

    Is that what I should expect?

    I tested - but I didn't but my testing could have been wrong thought I'd ask other people to dbl check

    thanks for your time

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • December 5, 2014 at 2:25 am

    #1762885

    Query hint overrides server maxdop

    Resource governor overrides server maxdop

    Query hint does not override Resource governor

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 5, 2014 at 11:49 am

    #1763053

    Thanks Gail!

    I think I will have to 're-run' my testing, I used the following to check for parallel plans.

    SELECT TOP 50

    OBJECT_NAME(p.objectid, p.dbid) as [object_name]

    ,ps.total_worker_time/ps.execution_count as avg_worker_time

    ,ps.execution_count

    ,ps.total_worker_time

    ,ps.total_logical_reads

    ,ps.total_elapsed_time

    ,p.query_plan

    ,q.text

    ,cp.plan_handle

    FROM sys.dm_exec_procedure_stats ps

    INNER JOIN sys.dm_exec_cached_plans cp ON ps.plan_handle = cp.plan_handle

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

    WHERE cp.cacheobjtype = 'Compiled Plan'

    AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

    ORDER BY ps.total_worker_time/ps.execution_count DESC

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply