MAXDOP Query hints in SQL Server

MAXDOP (Maximum degree of parallelism) controls the number of CPU cores used for a query execution. By default, SQL Server automatically decides how many CPU core to uses. The MAXDOP hint limits the number of threads used for parallel query execution. SQL server breakdown complex query into smaller task that can be executed in parallel using multiplex. This can significantly improve the query performance.

However, excusive parallelism can also lead to resource contention and perform degration if too many query are running in parallel. The MAXDOPP query hint allows for granular control of this. Incorrect use of MAXDOP can negatively impact query performance.

Syntex

Select * from tablename option (MAXDOP N)

MAXDOP 1- Force single treaded execution (no parallelism)

MAXDOP 4- use upto 4 CPU cores for query execution.

MAXDOP 0 – default behavior

We can use MAX DOP hints to Override server default for a specific query. Reduce parallelism for query causing CXOACKET wait. Force parallelism when SQL server is not parallelizing a query.

We can change the setting of MAXDOP

EXEC SP_configure ‘ MAX degree of parallelism 4’ Reconfigure.

We can see the setting of MAXDOP.

Select name, value_in_use from sys.configuration

Its depends on the system CPU Core. My system has 8 logical processor see below.

Do not set MAXDOP higher than our actual CPU cores. For the OLTP system MAXDOP 2 is often recommended. For the data ware house higher MAXDOP value (4-8) may be beneficial.