configure max degree of parallelism SQL Server Option.

Problem: How to configure the max degree of parallelism.

Solution: max degree of parallelism

Under server- wide configuration options, there is an option to configure the maximum degree of parallelism.

Parallelism in simple terms means that a query uses resources(processors), parallel execution plan.

Parallelism comes into the execution plan when SQL Server Database Engine chooses to offer more resources in order to commit this query faster.

By default, SQL Server has a configuration set inside to 0, allows SQL Server to use all the available processors up to 64 processors and handles the degree of parallelism based on the execution plans.

select * from sys.configurations

where name ='max degree of parallelism'

To Override the default value for a specific statement we can specify a query hint using OPTION key word.

When an application needs to run serial coding it’s preferred to use one core, by setting the degree of parallelism to 1.

Example:

In this article I am going to show we can limit the resources(processors) for executing a query using query hints.

1.       I am going to create a table employees

CREATE TABLE [dbo].[Employees](

                                                 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

                                                 [Name] [varchar](32) NULL,

                                                 [PhoneNumber] [int] NULL,

                                                         ) ON [PRIMARY]

2.       Insert 1 million records to have some load on the query

insert into dbo.Employees([Name]

      ,[PhoneNumber])

select

                substring(cast(newid() as varchar(68)), 1, 6) as Name

       , (

             SELECT TOP (1) cast (c1 as int)

             FROM (

             VALUES ('123'), ('234'), ('345'), ('456'), ('678')

             ) AS T1(c1)

             ORDER BY ABS(CHECKSUM(NEWID()))

       ) as Ph

GO 1000000

3.       Compare the execution plan results of default SQL server assigned cores and the query hint offered resources.

               

--

select * from dbo.employees

order by employeeid

-- MAXDOP

select * from dbo.employees

order by employeeid

OPTION( MAXDOP 2)

As we can the see the cost changes and the parallelism comparatively got into a level 2.

Example 2:

Configuring the max degree of parallelism using sp_confiugre.

--before configuring the max dop

select * from sys.configurations

where name ='max degree of parallelism'

USE DB ; 

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 

-- after configuring the max dop

select * from sys.configurations

where name ='max degree of parallelism'

OUTPUT: