SQL Server’s ability to divide a single query into multiple concurrent execution threads running on different CPU cores, improving throughput for large, complex operations.
- SQL Server uses the Cost Threshold for Parallelism setting to determine when a query is expensive enough to warrant parallel execution: The default value is 5, which is typically too low for most environments; values between 25 and 50 are generally more appropriate.
- MAXDOP (Max Degree of Parallelism) controls the maximum number of CPU cores SQL Server can use for a single parallel query: It should be configured based on the server’s NUMA topology.
- Parallelism is beneficial for large analytical queries: Operations such as sorts, hash joins, and aggregations over millions of rows can execute much faster in parallel, while short OLTP transactions often perform worse due to thread coordination overhead.
- Over-parallelism can negatively impact performance: Excessive parallel threads or too many concurrent parallel queries can cause
CXPACKETandCXCONSUMERwaits and may starve other workloads of CPU resources. - Resource Governor can be used to limit parallelism for specific workload groups: This helps isolate resource-intensive analytical queries from OLTP traffic.
- Related terms: MAXDOP, CX Packet Wait, Resource Governor, Execution Plan, Cost Threshold for Parallelism.
