March 28, 2017

Max degree of parallelism Property in sql server

Question -What is  max degree of parallelism in SQL server and how can we find the parallelism which has been in SQL server

Answer:

When we execute a query number of processes use in parallel execution

Parallelism is to execute a query Some process is required if the process required more time to execute the query. So we have to enable parallelism. In parallelism we mention max degree of parallelism is number of processes is you are allowed to execute that query nothing else.

When this parallelism is applied to a query that query cost reach to cost threshold for parallelism by default its value is zero.

You can set it to 1 or another number based on hardware configuration for Memory.

Example

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO