Limitation of processor resource utilization for specific user

Question

Is that possible that for one single user, I will limit somehow procesor resources that can be utilized? For a single query, I can use something like:

Option (MAXDOP 4) 

Is that possible to limit similar way all queries that were executed by a specific user? Of course, if the user will run 10 separate queries I would like not only they will be limited but a limit will be set for cumulative overload.

Answer 1

See if SQL Server Resource Governor is what you need.

SQL Server Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical I/O, and memory that incoming application requests can use.

I don't know if you can limit per-user, but you can set workload groups and assign users to one.

Answer 2

If you have the "Enterprise Edition" of SQL Server, then you can limit the server resources by few steps:

1- Create Resource Governor Resource Pool:

A resource pool, or pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.

1.1- Create limited resource pool: the resources are limited in this pool

CREATE RESOURCE POOL LimitedResourcePool
WITH
(MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=25,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=25)
GO

1.2- Create unlimited resource pool: the resources are unlimited in this pool

CREATE RESOURCE POOL UnlimitedResourcePool
WITH
(MIN_CPU_PERCENT=25,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=25,
MAX_MEMORY_PERCENT=100)
GO

2- Create Resource Governor Workload Group:

A workload group serves as a container for session requests that are similar according to the classification criteria that are applied to each request.

2.1- Create Workload Group in the LimitedResourcePool:

CREATE WORKLOAD GROUP LimitedResourceGroup
USING LimitedResourcePool;
GO

2.2- Create Workload Group in the UnlimitedResourcePool:

CREATE WORKLOAD GROUP UnimitedResourceGroup
USING UnlimitedResourcePool;
GO

3 - Creating Resource Governor Classifier Function:

The SQL Server resource governor classification process assigns incoming sessions to a workload group based on the characteristics of the session.

Resource Governor supports the classification of incoming sessions. Classification is based on a set of user-written criteria contained in a function. The results of the function logic enable Resource Governor to classify sessions into existing workload groups.

CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'LimitedUser') 
SET @WorkloadGroup = 'LimitedResourceGroup'
ELSE IF (SUSER_NAME() = 'UnlimitedUser')
SET @WorkloadGroup = 'UnlimitedResourceGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
-- If the user is within a specific domain, please ensure to provide the username as follows: <DomainName>\<UserName>.

4- Enable Resource Governor:

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

---

If you want later to Disable Resource Governor:

ALTER RESOURCE GOVERNOR DISABLE;
GO

---

If you want to undo the previous 4 steps:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO
DROP WORKLOAD GROUP LimitedResourceGroup
GO
DROP WORKLOAD GROUP UnlimitedResourceGroup
GO
DROP RESOURCE POOL LimitedResourcePool
GO
DROP RESOURCE POOL UnlimitedResourcePool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO