SQL Server - Degree of Parallelism

In SQL Server 2008, Microsoft introduced a concept called max degree of parallelism. Max degree of parallelism allows the engine to take advantage of all the available processors on the server up to 64 processors to execute a single SQL statement. By default, SQL Server is configured to use all available processors, however, there may be instances where you want to limit the number of processors a query should use. For example, you may have a low priority SQL agent job running in the background and you want to limit it to only use one processor. You can set max degree of parallelism to 1.

The query below will only use up to 2 processors for parallel operation regardless of the number of processors available.

USE AdventureWorks
GO
SELECT ProductID, OrderQty, SUM(LineTotal) As Total
FROM Sales.SalesOrderDetail 
WHERE UnitPrice < $10.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2)
GO

To set the option for SQL Server to always use up to X number of processors, you can use SP_Configure.

For example, the following query forces SQL engine to only use 4 processors.

USE Master
GO
SP_Configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_Configure 'Max Degree of Parallelism', 4
GO
RECONFIGURE
GO

Default "Max Degree of Parallelism" option is 0, which means use all available processors up to 64.

Thank you.