question

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