x

CPU throttling for specific webuser

Not sure what I'm doing wrong, but I'm following this example: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/use-resource-governor-to-limit-cpu-usage-by-backup-compression-transact-sql

same idea, except my focus is on a single app causing CPU overhead that I wish to throttle down from their webusers, instead of the backup job...

However, when I look at perfmon, the CPU usage does not throttle at all, I even set (max_cpu_percent=1) and I can't seem to limit it to that 1%. Of course all this is in development... and I'm using a query that's meant to generate CPU specifically, not actually using the app for stress testing...

Not sure what I'm doing wrong... all commands have executed successfully as per the doc and my test connections are coming under the right group name (not default) when running this query:

 USE master;  
 SELECT sess.session_id, sess.login_name, sess.group_id, grps.name   
 FROM sys.dm_exec_sessions AS sess   
 JOIN sys.dm_resource_governor_workload_groups AS grps   
     ON sess.group_id = grps.group_id  
 WHERE session_id > 50;  
 GO  

Just seems as if nothing is happening.... hrmm...

I guess I should include my exact code in case there is a syntax error somewhere...

 CREATE RESOURCE POOL webuser;
 GO
 CREATE WORKLOAD GROUP webuser USING webuser
 GO 
 
 USE MASTER;
 GO
 CREATE FUNCTION dbo.Restrict_cpu()
 RETURNS SYSNAME WITH SCHEMABINDING
 AS
 BEGIN
        DECLARE @GroupName SYSNAME
           IF SUSER_NAME() = 'webuser'
             BEGIN
               SET @GroupName = 'webuser'
            END
        ELSE
        BEGIN
               SET @GroupName = 'default'
        END
        RETURN @GroupName;
 END
 
 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Restrict_cpu);
 
 ALTER RESOURCE GOVERNOR RECONFIGURE;
 
 WAITFOR DELAY '00:00:15'
 GO
 ALTER RESOURCE POOL webuser with (max_cpu_percent=100);
 ALTER RESOURCE GOVERNOR RECONFIGURE;
 GO

and the code I found to generate the CPU overhead is this (running about 6 or 7 of these concurrently):

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


JohnM: Since I can't seem to reply to your comments in the comment section...

The main query causing ~98% of the CPU Usage is a simple select statement, actually its quite long... But its a vendor app, so we can't really modify the query itself.. We have engaged the vendor and even looking to move this application database onto its own server however in the meantime, we need a quick temporary fix so I thought using the resource Governor would be good to limit the amount of CPU usage this one particular database is using since its affecting other production database systems on this instance... When we take this database offline, performance returns to normal for all other applications, so we know its the culprit...

I've attached a screenshot from Quest Foglight to show what this single query is doing in terms of CPU... alt text

Again, comment answer here: looks like this query is not using any degree of parallelism at all from the screenshot... and our instance is set up to the default value of 0. Our SQL Cluster has 6 cores.

Actually, now that I look at it... Most of the cost for this SQL is clustered Index Scans... and its pointing to IO not CPU...I find this odd, as IO barely shows on the workload graph, its dominated by CPU events...

alt text

more ▼

asked Oct 05 at 07:04 PM in Default

avatar image

nlassaline
0 1

Just curious, what about the web app is causing CPU contention? Bad queries?

Oct 05 at 07:24 PM JohnM

I guessed it was going to be a 3rd party application, however it might be worth it to look at the execution plan (if you haven't already) to see if you could implement indexes to help reduce the CPU contention. What about MaxDop setting and/or cost threshold for parallelism? Are they set according to best practices for the instance?

Oct 05 at 08:11 PM JohnM

erm... your code above seems to indicate you're restricting webuser to max_cpu_percentage of 100? ie not restricting it at all?

Oct 09 at 09:38 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Say you want to restrict your webuser process to 30% CPU usage. It'll only happen if everything else on the server can use over 70% of the CPU.

more ▼

answered Oct 06 at 10:35 AM

avatar image

ThomasRushton ♦♦
41.9k 20 50 53

Is there a way to alter this behaviour and apply it at any total CPU usage %?

Oct 06 at 12:10 PM nlassaline

It looks as though the cap_cpu_percent setting might be what you want.

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool

Oct 09 at 09:39 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x32

asked: Oct 05 at 07:04 PM

Seen: 35 times

Last Updated: Oct 09 at 09:39 AM

Copyright 2017 Redgate Software. Privacy Policy