Parallel Plan Execution in SQL Server

Parallel plan execution is useful to increase the task performance, as one task is performed by more than one processor in parallel. Let’s see how parallel execution process is implemented in SQL Server.

SQL Server that runs on a computer which has more than one microprocessor or CPUs, and then we can define that how many system processors can be used to execute single SQL query statement

Max Degree of Parallelism – option is used to define the number of processor usage for parallel plan execution. And MAXDOP Query hint can be used to override the Max Degree of parallelism execution.

Setting Maximum Degree of Parallelism - option to 0[default value] allows SQL Server to use all the available processors up to 64 processors. To defeat the parallel plan execution, set max degree of parallelism to 1. To restrict the maximum number of processors used by a single query execution then set the value to a number greater than 1.

We can either use SQL Server Management Studio or T-SQL Script to configure parallel plan execution in SQL Server.

Steps to configure through SQL Server Management Studio

1. In Object Explorer, right-click a server and select Properties.

2. Select the Advanced node.

3. In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

Refer below screen shot for the reference.

Steps to configure through T-SQL Script

Let’s use below script to configure through T-SQL.

USE [master];

GO

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure 'max degree of parallelism', 8;

GO

RECONFIGURE WITH OVERRIDE;

GO

Refer below the screen shot for the test results.