RedPrairie: Improving performance by using Parallelism

By David Erickson, RedPrairie

Most of you know that computer CPUs are not getting faster anymore, instead they are getting more cores. Microsoft SQL Server has two parameters that control whether work goes to one core or to many cores. I'd like to give a simple explanation of these two factors and share my experience tuning them in our labs. You may discover a significant performance improvement from your RedPrairie database.

Note: Often a core is referred to as a logical processor in Microsoft documentation.

Where are the Parallelism Parameters?

In SQL Server Management Studio (SSMS):

1. Select your SQL Server Instance
2. Right mouse click and select [Properties], the Server Properties dialog will appear.
3. On the left side, select [Advanced] under 'Select a page',
4. A dialog similar to the one below will appear
   
5. Under the [Parallelism] section, there are four values. We will look at two values only:
   1. Cost Threshold for Parallelism -- in our current testing there was little improvement seen when we tried different values; so leave it at 5. If you are running on solid state devices, you may wish to experiment with this setting because its effectiveness changes with I/O speed.
   2. Max. Degree of Parallelism [MAXDOP]-- this is a throttle on how many cores may be used for one query.

The default value for MAXDOP is 0 and means that SQL Server may use up to the max CPUs available in any parallel plan. If MAXDOP is set to 1 SQL Server will use exactly 1 CPU, i.e. parallel plan execution is disabled. Any other value will cause SQL Server to use up to the specified number of CPUs in the parallel plan.

If you search the web, you will find a lot of speculation on what is the best value. My personal stance is as follows.

• I dislike the “disable completely” approach. I think the Cost Threshold parameter gives you ample control to confining parallelism to a small subset of plans that could benefit. To force every plan into serial execution no matter how many idle resources are available seems overly cautious to me.
• I dislike the default value of 0 (unlimited CPUs). An ad-hoc query doing some form of data mining may hang the system, grinding production to a halt. I like to set this equal to the number of CPUs / 2, just so any one query can only take up half the system. This setting can be capped at 8, so on large systems with many cores SQL Server will not use too many CPUs for parallel plan execution.

There is a need for balance - the parallel plan will consume more resources, but theoretically free those resources (and any locks) in a shorter period of time. A system with shorter locks is truly better for contention and reduces the duration of blockages.

I’ve heard several Microsoft experts say that this feature is more intended for BI or OLAP type systems, with fewer concurrent users, rather than OLTP systems with higher concurrency requirements. Very recently, I had the chance to revisit this topic again with a Microsoft SQL Server performance expert. We came up with an approach that I think is pretty reasonable for many customers.

• During the most highly concurrent parts of the day, set MAXDOP = 1 to disable parallelism when it is most crucial to serve many simultaneous requests.
• If there are “down time” periods where large batch jobs are run (downloads, reports, data exports, maintenance jobs), and/or fewer users on the application, then set MAXDOP to some higher value for those hours.
• Since it can be configured live via sp_configure, you can actually schedule this job to meet the business needs.
• Monitor the database for how much parallelism is actually used. Stay tuned for a future post where I will show you how to do this.

To make your life simpler, I've included a script below that will create two SQL Server Agent Jobs. The first one sets 'max degree of parallelism' to 1 at 6AM. The second job sets 'max degree of parallelism' to 1/2 of the number of cores at 1 AM. Adjust the hours to your own environment if needed.

Script for regular production

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Parallelism]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Parallelism]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Change Maximum Degree of Parallelism to 1',
 @enabled=1,
 @notify_level_eventlog=0,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'No description available.',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'SERVER2008X64\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SetParallelToOne',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @os_run_priority=0, @subsystem=N'TSQL',
 @command=N'sp_configure ''show advanced options'', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ''max degree of parallelism'', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO',
 @database_name=N'master',
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'At 6AM',
 @enabled=1,
 @freq_type=4,
 @freq_interval=1,
 @freq_subday_type=1,
 @freq_subday_interval=0,
 @freq_relative_interval=0,
 @freq_recurrence_factor=0,
 @active_start_date=20110627,
 @active_end_date=99991231,
 @active_start_time=60000,
 @active_end_time=235959,
 @schedule_uid=N'40a4c2b4-56fa-4a0b-a22a-f0bb15788539'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Parallelism]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Parallelism]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Change Maximum Degree of Parallelism to Half CPUS',
 @enabled=1,
 @notify_level_eventlog=0,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'No description available.',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'SERVER2008X64\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
Declare @CPUCount int=1
Declare @Cmd NVARCHAR(max)
SELECT @CPUCount = cpu_count/2 from sys.dm_os_sys_info;
-- Change to 4 if Intel's Hyper-threading is enabled in BIOS
SET @Cmd=N'sp_configure ''show advanced options'', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ''max degree of parallelism'', '
+cast(@CPUCount as nvarchar(11))+'
GO
RECONFIGURE WITH OVERRIDE;
GO'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SetParallelToHalf',
 @step_id=1,
 @cmdexec_success_code=0,
 @on_success_action=1,
 @on_success_step_id=0,
 @on_fail_action=2,
 @on_fail_step_id=0,
 @retry_attempts=0,
 @retry_interval=0,
 @os_run_priority=0, @subsystem=N'TSQL',
 @command=@Cmd,
 @database_name=N'master',
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'At 1AM',
 @enabled=1,
 @freq_type=4,
 @freq_interval=1,
 @freq_subday_type=1,
 @freq_subday_interval=0,
 @freq_relative_interval=0,
 @freq_recurrence_factor=0,
 @active_start_date=20110627,
 @active_end_date=99991231,
 @active_start_time=10000,
 @active_end_time=235959,
 @schedule_uid=N'40a4c2b4-56fa-4a0b-a22a-f0bb15788540'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

After the above is executed, you will see these jobs listed in SQL Server Management Studio. You may further modify them as needed.

• Degree of Parallelism
• Recommendations and Guidelines for 'max degree of parallelism' configuration option
• General guidelines to use to configure the MAXDOP option
• Determining optimal MAXDOP setting from TSQL in SQL Server 2005