none
Why isn't Resource Governor working as expected? RRS feed

  • Question

  • Greetings. I'm attempting my first use of RG for R&D purposes, and it's not working as intended. I have a login named resourceGov, and want it to not use >3% of my CPU. I create everything with the scripts below. Then I launch an SSIS Package from MS Visual Studio in my laptop. I then RDP to the server in question, and watch the CPU spike in Task Manager. I also run some queries to confirm that the resourceGov login is in fact what's doing all the heavy lifting with my "running processes" query at the bottom. 

    Any ideas?

    -- Creating Resource Pool for Report Server
    create RESOURCE POOL resourceGovPool
    WITH
    ( MIN_CPU_PERCENT=0,
    MAX_CPU_PERCENT=03,
    MIN_MEMORY_PERCENT=0,
    MAX_MEMORY_PERCENT=30)
    GO
    
    CREATE WORKLOAD GROUP resourceGovGroup
    USING resourceGovPool ;
    GO
    
    
    create FUNCTION dbo.UDFClassifier()
    RETURNS SYSNAME
    WITH SCHEMABINDING
    AS
    BEGIN
    DECLARE @WorkloadGroup AS SYSNAME
    IF(SUSER_NAME() = 'resourceGov')
    SET @WorkloadGroup = 'resourceGovGroup'
    ELSE
    SET @WorkloadGroup = 'default'
    RETURN @WorkloadGroup
    END
    GO
    
    
    ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
    GO
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO
    
    /*
    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
    GO
    ALTER RESOURCE GOVERNOR DISABLE
    GO
    DROP FUNCTION dbo.UDFClassifier
    GO
    DROP WORKLOAD GROUP resourceGovGroup
    GO
    DROP RESOURCE POOL resourceGovPool
    GO
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO
    */

    Running Processes query:

    --Running processes
    
    select er.session_id, er.start_time, er.status, er.command, er.database_id, sd.name,
    er.wait_type, er.last_wait_type, er.wait_time,
    er.wait_resource, er.cpu_time, er.reads, er.writes, er.logical_reads, 
    [Statement] = SUBSTRING (st.text, er.statement_start_offset/2,
    	(CASE WHEN er.statement_end_offset = -1
    	       THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
    		ELSE er.statement_end_offset END - 
                                    er.statement_start_offset)/2),
    st.text as Query, es.login_time, es.host_name, es.program_name, es.client_interface_name, es.login_name,
    es.status, es.total_scheduled_time, es.total_elapsed_time, es.last_request_start_time, es.last_request_end_time--, qp.query_plan
    --for backups and rollbacks, not queries
    --,er.percent_complete, er.estimated_completion_time
    from sys.dm_exec_requests er
    join sys.dm_exec_Sessions es on er.session_id=es.session_id
    left join sys.databases sd on er.database_id=sd.database_id
    right join sys.dm_os_tasks ot on er.session_id=ot.session_id
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
    --CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
    order by er.cpu_time desc


    Thanks in advance! ChrisRDBA


    • Edited by ChrisRDBA Monday, November 7, 2016 8:41 PM
    Friday, November 4, 2016 11:34 PM

Answers

  • You did this right?

    ALTER RESOURCE GOVERNOR RECONFIGURE

    I found it helpful to limit cpu and memory. Now Resource governor only applies to workloads where a batch is longer than the 4 ms quanta that the schedulers get.  You will not see this for SQL Server as a whole.

    You can't see the cpu throttling in task manger, but you can see it in the resource governor perf counters. Here is something I ran.


    • Edited by Hilary CotterMVP Monday, November 7, 2016 9:01 PM edit
    • Marked as answer by ChrisRDBA Monday, November 7, 2016 9:45 PM
    Monday, November 7, 2016 8:58 PM

All replies

  • My experience of resource governor is that it throttles when it is under memory or cpu pressure, and it does not throttle the current workload, but subsequent connections made after the server is under memory or cpu pressure.
    Saturday, November 5, 2016 3:09 AM
  • I didn't realize that. This is going to make testing tough. Thanks!

    Thanks in advance! ChrisRDBA

    Saturday, November 5, 2016 3:50 PM
  • Hi ChrisRDBA,

    I test your scenario, I use the Resource Governor Classifier Function shown as below and I set the MAX_CPU_PERCENT=3 for the testgroup:

    create FUNCTION dbo.UDFClassifier()
    RETURNS SYSNAME
    WITH SCHEMABINDING
    AS
    BEGIN
    DECLARE @WorkloadGroup AS SYSNAME
    IF(SUSER_NAME() = 'test')
    SET @WorkloadGroup = 'testgroup'
    ELSE
    SET @WorkloadGroup = 'default'
    RETURN @WorkloadGroup
    END
    GO

    After that I use the login ‘test’ to sign in the instance and then use a script running on this instance to make the CPU usage of SQL Server Service increasing and I get the result 6% as shown in the below picture, it does exceed the set value:

     

    Based on my searching and testing, this is by design, Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption, in your scenario, resource governor will not be useful because only one process using resource. If at the time of executing a task in that pool there are no other active or workable SPIDS in other pools, CPU caps will not be taken into account.

    You can test it in some more complex scenarios, for example, we can set two servers which sync data with each other and one server runs OLTP transactions and the second server runs all the resource intensive reports. For more information, I suggest you read this article.

    Best Regards,
    Teige
    Monday, November 7, 2016 9:37 AM
    Moderator
  • Hi ChrisRDBA,

    I test your scenario, I use the Resource Governor Classifier Function shown as below and I set the MAX_CPU_PERCENT=3 for the testgroup:

    create FUNCTION dbo.UDFClassifier()
    RETURNS SYSNAME
    WITH SCHEMABINDING
    AS
    BEGIN
    DECLARE @WorkloadGroup AS SYSNAME
    IF(SUSER_NAME() = 'test')
    SET @WorkloadGroup = 'testgroup'
    ELSE
    SET @WorkloadGroup = 'default'
    RETURN @WorkloadGroup
    END
    GO

    After that I use the login ‘test’ to sign in the instance and then use a script running on this instance to make the CPU usage of SQL Server Service increasing and I get the result 6% as shown in the below picture, it does exceed the set value:

     

    Based on my searching and testing, this is by design, Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption, in your scenario, resource governor will not be useful because only one process using resource. If at the time of executing a task in that pool there are no other active or workable SPIDS in other pools, CPU caps will not be taken into account.

    You can test it in some more complex scenarios, for example, we can set two servers which sync data with each other and one server runs OLTP transactions and the second server runs all the resource intensive reports. For more information, I suggest you read this article.

    Best Regards,
    Teige

    Thanks. I used that article to set up what I did. I think you're agreeing with Hillary, that RG won't be used unless it's actually needed, right?


    Thanks in advance! ChrisRDBA

    Monday, November 7, 2016 3:08 PM
  • I shifted testing to my laptop instance, as it has much less CPU that my DEV instance, and I assumed it would be easier to have this work as expected. 

    I also started running the code below to generate CPU load. I can peg my CPU by opening 5 windows in MGMT Studio and running this guy. I've tried using various combinations of the resourceGov login as well as my AD login, combinations of Mgmt Studio and sqlCmd to run the code, etc. No matter what I do, I can't seem to get RG to work as expected. 

    I've also queried some of the system tables pertaining to RG and it appears to be setup correctly, though I could be wrong?

    Any ideas?

    DECLARE @T DATETIME, @F BIGINT;
    SET @T = GETDATE();
    WHILE DATEADD(SECOND,95,@T)>GETDATE()
    SET @F=POWER(2,30);
    


    Thanks in advance! ChrisRDBA

    Monday, November 7, 2016 8:38 PM
  • You did this right?

    ALTER RESOURCE GOVERNOR RECONFIGURE

    I found it helpful to limit cpu and memory. Now Resource governor only applies to workloads where a batch is longer than the 4 ms quanta that the schedulers get.  You will not see this for SQL Server as a whole.

    You can't see the cpu throttling in task manger, but you can see it in the resource governor perf counters. Here is something I ran.


    • Edited by Hilary CotterMVP Monday, November 7, 2016 9:01 PM edit
    • Marked as answer by ChrisRDBA Monday, November 7, 2016 9:45 PM
    Monday, November 7, 2016 8:58 PM
  • You did this right?

    ALTER RESOURCE GOVERNOR RECONFIGURE

    I found it helpful to limit cpu and memory. Now Resource governor only applies to workloads where a batch is longer than the 4 ms quanta that the schedulers get.  You will not see this for SQL Server as a whole.

    You can't see the cpu throttling in task manger, but you can see it in the resource governor perf counters. Here is something I ran.


    Yes I had ran the RECONFIGURE statement. RG had been working all along, I just wasn't able to see it. Once I started using perfmon while starting/ stopping my test queries, it was clear that all is well. Thanks!

    Thanks in advance! ChrisRDBA

    Monday, November 7, 2016 10:04 PM