SQL Server MAX DEGREE OF PARALLELISM

The max degree of parallelism setting in SQL Server is used to control the maximum number parallel threads available to process your query. The default setting of 0 means use as many as possible. The problem with this default is that you may end up with way more parallel threads working on your query than you have processors available. When this happens you can end up with thrash as these multiple tasks are being swapped between your processors or cores.

If your server has 4 cores, you could use the following script to set max degree of parallelism to match your 4 cores.

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

The recommendation is to set this to match the number of cores on your system, or the maximum number of cores in any NUMA node.

Setting this to 1 tells SQL Server to disable parallel processing, and is generally not recommended.