0

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.

  • Are the research queries coming from a different application or host. You can use one of the following system functions to get the details of the query or task so that it can be directed to the right pool: HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), or IS_MEMBER(). The UDF can also use lookup tables to configure the request to the proper pool. – Scott Hodgin Apr 8 at 12:55
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.