Sql-server – The best way to limit server hog using resource governor

sql-server-2016

I have a scenario where I have a couple of users that run intensive queries where it uses up most of CPU resource. I've repeated told them to change the queries but doesn't seem to be moving anywhere. I can't really take away their access due to company policy.

What I want to do is to divide good users vs. bad users, and allocate bad users max of 25% CPU (with IO throttling), so when no one else is running the query, the bad users get 25% of CPU power. When good users are running queries, then bad users have the lowest priority thus may not even get 25%.

Is this possible?

Best Answer

The trick to Resource Governor is thinking about what you want to protect rather than what you want to throttle. You set minimum/reserve resources for your good users which protects them from the bad users.

My advice is to start conservative and slowly turn the knobs. You'll likely just need one Resource Group for your "Good Users". You would set their minimum CPU and/or Memory to, say 10%. Everything else, including your Bad Users, will go to the Default pool.

If Bad Users were all running CPU heavy queries, and the Good User's query came along, it would throttle the Bad so the Good would be guaranteed to at least have 10% of the CPU.

You may decide that a 10% minimum isn't enough and you need to increase it. Just be measured in your approach. I personally have never found the need to touch the Maximums, though I suppose there are use-cases.

Also, the Resource Governor DMVs provide some great information. You can create your "Good Group" and just leave it at the defaults to see the stats. They are quite interesting on their own.

Another thing to be aware of is to make your Classifer function very simple. Remember, this function will get called on every connection so SQL will know which Group to route the connection, so don't put a lot of crazy logic in there.

Typically you'll just do simple CASE compare based on Application Name or Host Name or Login Name.

for example:

CREATE FUNCTION [dbo].[my_classifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN

DECLARE @HostName NVARCHAR(256)

SELECT @HostName = HOST_NAME()

RETURN 
(
    SELECT CASE
    WHEN (@HostName = 'GoodUser') THEN N'GoodUser_WorkloadGroup'
    ELSE  'default'
    END
);
END
Related Question