SQL Server Parallelism - MAXDOP and Resource Governor Option

MAXDOP only controls the number of parallel threads PER OPERATOR during query execution. Since queries can have multiple operators, it’s entirely possible for SQL Server to throw multiple threads at a single operation to handle different operators.

Resource Governor enables to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests.

For example if a server has four processors and there is a need to use only three for parallelism, then DBA should set the max degree of parallelism option to 3. When DBA want to use a specific number of processors for an individual query or index operation, she can use the MAXDOP query hint or index option to provide better control. MAXDOP overrides the settings defined in the max degree of parallelism option. The use of MAXDOP is limited to theexecution of a query that contains the MAXDOP query hint or index option.

Following are the sequence of steps that can be used for MAXDOP query hint or index option to provide better control:

1- Create Resource Pool

CREATE RESOURCE POOL [indexMaintenance] WITH(min_cpu_percent=0,
             max_cpu_percent=100,
             min_memory_percent=0,
             max_memory_percent=100,
             AFFINITY SCHEDULER = AUTO
)
GO

2- Create a Workload Group

CREATE WORKLOAD GROUP [wg_indexMaintenance] WITH(group_max_requests=0,
        importance=Medium,
        request_max_cpu_time_sec=0,
        request_max_memory_grant_percent=100,
        request_memory_grant_timeout_sec=0,
        max_dop=8) USING [indexMaintenance]
GO

3- Create the user that will be associate with workload group and give them permissions:

USE [master]
GO
CREATE LOGIN [indexMaintenance_user] WITH PASSWORD=N'ChangeThis',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [indexMaintenance_user]
GO

4- create a classifier function, which is the function SQL Server will run against new logins in order to determine their proper workload group:

CREATE FUNCTION fnRGClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
       DECLARE @group sysname
     IF(SUSER_NAME() = 'indexMaintenance_user')
     BEGIN
          SET @group =  N'wg_indexMaintenance';
     END
ELSE
        BEGIN
             SET @group =  N'default';
       END
       RETURN @group;
END;
GO

5- Set Resource Governor to use this function:

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnRGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

6- Index maintenance processes run as indexMaintenance_user

EXECUTE AS LOGIN = 'indexMaintenance_user';