question

nlassaline avatar image
nlassaline asked

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][1] 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][2] [1]: /storage/temp/4313-webuser-cpu-usage.jpg [2]: /storage/temp/4314-webuser-query-analisys.jpg
cpu
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just curious, what about the web app is causing CPU contention? Bad queries?
0 Likes 0 ·
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?
0 Likes 0 ·
erm... your code above seems to indicate you're restricting webuser to max_cpu_percentage of 100? ie not restricting it at all?
0 Likes 0 ·

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.