SQL Server Interview Question #212

What are MAXDOP and Cost Threshold for Parallelism?

Advanced Query Optimization Senior Advanced

Detailed Explanation

MAXDOP controls the maximum degree of parallelism available to an individual parallel query. Cost Threshold for Parallelism influences when SQL Server considers a parallel plan based on the estimated cost of a serial plan.

These settings should be configured according to workload, hardware, NUMA topology, SQL Server version, and operational guidance. Using MAXDOP 1 globally merely to remove CX-related waits can unnecessarily disable beneficial parallelism.

Per-query MAXDOP can also be specified when a specific workload has a justified requirement.

Code Example

SELECT *
FROM dbo.LargeSalesTable
OPTION (MAXDOP 4);