I was recently reading a great post by Norman Chan about the use of maximum degree of parallelism, or MaxDOP, settings to decrease index rebuild times. Norman has some interesting information in his post and I suggest reading it. The specific case he is looking at is a system in which MaxDOP is set to a value lower than 8, which he finds has the best performance. This post is a quick rundown on an alternate way to implement a custom MaxDOP for specific SQL Server jobs, such as index maintenance.
This process requires the use of the Resource Governor. Keep in mind that this feature is available in Enterprise Edition only.
Per technet, “Resource Governor enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests”. Conveniently, one of the settings Resource Governor allows us to set for specific requests is MaxDOP. The reason Resource Governor is a good way to handle this is that changing the server-wide MaxDOP setting will clear your entire procedure cache, however setting it for specific requests with Resource Governor does not.
The first step is to create our 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
These are default values and should not affect performance.
We then need to 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
Here, everything but the max_dop and request_max_memory_grant_percent settings are a default value. This is where our custom max_dop is set. Note that request_max_memory_grant_percent defaults to 25, meaning it would only consume 25% of the maximum memory available to the resource pool we associate it with, indexMaintenance. I have set this to 100 here, as there will likely not be other requests sharing the pool. Bare in mind that these settings are effectively “wide open” and tweak as your system requires.
Next, we need to create the user that will be associate with our 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
Please change your password to a strong password.
Next, we need to 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 -- Use the default workgroup if there is no name match ELSE BEGIN SET @group = N'default'; END RETURN @group; END; GO
This is a basic function based on code I originally received from Jonathan Kehayias(blog|twitter). Note that if you have other workload groups being used in your system, you will need to modify this code to include them.
We then set Resource Governor to use this function:
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnRGClassifier); ALTER RESOURCE GOVERNOR RECONFIGURE; GO
We now have Resource Governor configured with a classifier function that will route all requests from our new user, indexMaintenance_user, to our workload group wg_indexMaintenance, which has a custom MaxDOP of 8, meaning all requests from indexMaintenance_user will run with MaxDOP 8 without having to change any server wide settings!
From here, we just need to make sure that our index maintenance processes run as indexMaintenance_user. This is as simple as adding the following to the beginning of our Index Maintenance Job step, generally:
EXECUTE AS LOGIN = 'indexMaintenance_user';
Here’s an example:
The EXECUTE AS statement could also be added to the beginning of your index maintenance stored procedure.
Keep in mind that this method can be used to apply restrictions to any other kind of job as well. I hope you find this tip useful. If you use Resource Governor for other interesting things in SQL Server, leave a comment!