Sql-server – Using Resource Governor to limit query CPU usage for same user

resource-governorsql server

I have a remote MS SQL Server and I have several types of queries.

I run some queries that are part of a low-latency production application, and need to ensure they get domne in a timely manner.

But I also have "research" queries that tend to be CPU heavy.

How can I set the classifier such that different queries (from the same user) run with different resource pools so that my research queries don't hog all the CPU?

I am stuck on how to do this because I can't differentiate by user.

Best Answer

The bad news

The only way to throttle the sessions you want is to uniquely identify them. If you cannot do this, then you cannot selectively throttle resources using the Resource Governor.

The good news

There are loads of ways to stratify connections into different resource pools. You may need the cooperation of the person(s) managing the application to segregate the incoming connections, but the options for classifying incoming connections for the purposes of resource pool assignment are functionally infinite. I recommend sticking to connection properties that you set on the application connection string. My favorites are...

...but you can use any arbitrary logic that will uniquely (hopefully) apply to the incoming sessions you want to throttle. If you're feeling dangerous, you can use a lookup table and change the output of your classifier function on the fly (although the docs recommend against this).

For the copy-pasta hungry...

Demo Script

use [master]
go
create resource pool demo_pool;
create workload group demo_group using demo_pool;
create login my_bad_user with password='Plz_Halp_Me_StackExchange';
go
create or alter function dbo.GetResourceGroup()
returns sysname
with schemabinding
as
begin;
    declare @rg sysname = 'default';
    if original_login() = 'my_bad_user' set @rg = 'demo_group';
    if host_name() = 'MYDOMAIN\APP_HOST_1' set @rg = 'demo_group';
    if program_name() = 'My Custom Data Science App' set @rg = 'demo_group';
    return @rg;
end;
go
alter resource pool demo_pool with (
     max_cpu_percent = 25
    ,cap_cpu_percent = 25
);
go
alter resource governor with (classifier_function = dbo.GetResourceGroup);
alter resource governor reconfigure;
go

Demo Cleanup

use [master]
go

alter resource governor with (classifier_function = null)
alter resource governor disable;
drop workload group demo_group;
drop resource pool demo_pool;
drop login my_bad_user;
drop function if exists dbo.GetResourceGroup;
go
Related Question