Max Degree of Parallelism in SQL Server for a Query

IF your Computer is having Multiple CPU’s.are you sure that you are using Multiple Cpu’s for the Execution of a Query?We can just look into that,

I run a command in sql server  and watched the execution Plan

SELECT DISTINCT ID from dbo.submit_messages

image

My Computer is having Dual core.So I want to make the query to use two processors.For that I want to Enable  Maximum Degree of Parallelism (Maxdop) to 2.So i wrote the query and enable Maxdop option as follows.Here Maxdop 0 to N is the no of processors.Here we assign 2 for Maxdop.

image2

We can Enable the Maxdop Globallyfor the Database Server.The Steps are,

1. Right click the Database Server and select properties.

2.Select the Advanced tab and select Parallelism.

3. Set the Max Degree of Parallelism to 2 and click ok.

image3

Now your Database server will execute the query in dual core. This can offer a big performance gain in certain circumstances.