none
Problem limiting resource utilization with Resource Governor

    Question

  • Hi,

    I have started trying to use Resource Governor to limit the resource utilization of one of our systems but it doesn' seem to work. I have tried searching forums and the web but find no clues to what is my problem.

    I have created a resource pool named ResourcePool1 that has a maximum CPU limit of 10 %. The resource pool has a maximum memory limit of 10 %. To that resource pool i have created a single workload group WorkLoadGroup1 with a memory grant of 25 %. To that I have associated a WorkGroupClassifier function in the master database using the following code:

    ALTER function [dbo].[WorkloadGroupClassifier]()
    returns sysname with schemabinding
    begin
     declare @workloadGroup sysname
     set @workloadGroup = 'default' -- Default workload group
     
     if (lower(suser_sname()) = 'domain\systemuser') or (lower(app_name()) = 'systemname')
      begin 
        set @workloadGroup = 'WorkLoadGroup1'
      end
      
     return @workloadGroup
    end
    The system user is the user that the application pool is using in the IIS and supplied using the trusted connection attribute of the connection string. The system name is the application name attribute given in the connection string.

    When we run the system above (system name) that takes approximately 30 minutes of constant query execution to rebuild a cache table (yes, it can be rebuilt :-)) the whole db server hangs. If I try to run another count(*) query on another database and table that is not used by the system above that we try to limit the resources for, the count query hangs until the first query is finished. If I run the second query stand-alone with the first system not performing its cache rebuild the query takes one second to complete.

    If I have understood all right all other systems should go to the default resource pool instead using the classifier function above, but that doesn't seem to be the case and I do not understand why.

    Am I doing something wrong in my configuration or is it not supposed to work with this kind of heavy load? If I am using the CPU monitoring tool with only the heavy query running on the server, I can see that all 8 cores (100 % CPU utilization) are used during execution.

    The db server has Windows Server 2008 and SQL Server 2008 Enterprise edition installed. It has two quad-code CPU:s and a RAID 10.

    Thanks in advance!
    Best regards
    Lars
    Monday, May 18, 2009 8:30 AM

Answers

  • Hi Lars,

    First, a couple of references:

    http://blogs.technet.com/sqlos/archive/2007/12/14/part-1-anatomy-of-sql-server-2008-resource-governor-cpu-demo.aspx

    http://blogs.technet.com/sqlos/archive/2008/01/18/part-2-resource-governor-cpu-demo-on-multiple-cpus.aspx

    which may help understand why things work a certain way.

    Few other things, classifier function is executed for every new connection and its output determines where this connection is going to.

    You can verify where each session is by running this simple query:

    select session_id, group_id from sys.dm_exec_sessions

    where session_id > 50

    group_id = 2 is the default, > 255 are user created. You can join this with sys.dm_resource_governor_workload_groups to get the names.

    If this looks as expected (right connections are in the right groups) you are ok with the classifier itself. If not, you should start looking at PreConnect:Starting and PreConnect:Completed to see that it classifies in the right group. Note that existing connections are NOT reclassified.

     

    Then, look at where the work is happening. You should have CPU contention across schedulers (see second post above for queries on how to tell if this is the case).

    Resource pool limit will NOT limit the CPU usage of the process, it allows to redistribute the CPU on contention.

     

    Let's start from here and answer questions as we go along.

     

    Thanks,

    Boris.


    Boris Baryshnikov, SQL Server
    Wednesday, May 20, 2009 7:11 PM

All replies

  • Hi Lars,

    First, a couple of references:

    http://blogs.technet.com/sqlos/archive/2007/12/14/part-1-anatomy-of-sql-server-2008-resource-governor-cpu-demo.aspx

    http://blogs.technet.com/sqlos/archive/2008/01/18/part-2-resource-governor-cpu-demo-on-multiple-cpus.aspx

    which may help understand why things work a certain way.

    Few other things, classifier function is executed for every new connection and its output determines where this connection is going to.

    You can verify where each session is by running this simple query:

    select session_id, group_id from sys.dm_exec_sessions

    where session_id > 50

    group_id = 2 is the default, > 255 are user created. You can join this with sys.dm_resource_governor_workload_groups to get the names.

    If this looks as expected (right connections are in the right groups) you are ok with the classifier itself. If not, you should start looking at PreConnect:Starting and PreConnect:Completed to see that it classifies in the right group. Note that existing connections are NOT reclassified.

     

    Then, look at where the work is happening. You should have CPU contention across schedulers (see second post above for queries on how to tell if this is the case).

    Resource pool limit will NOT limit the CPU usage of the process, it allows to redistribute the CPU on contention.

     

    Let's start from here and answer questions as we go along.

     

    Thanks,

    Boris.


    Boris Baryshnikov, SQL Server
    Wednesday, May 20, 2009 7:11 PM
  • Out of curiosity, do you have your MAXDOP set to some low number?
    Thursday, May 21, 2009 4:00 PM