Need Recommendations for MAXDOP and Cost Threshold for Parallelism in SQL Server 2019 Web Edition

Question

Server Details:

• Instance Name: CIMSWMSAPPPROD
• SQL Version: SQL Server 2019 Web Edition (RTM CU27)
• Server Configuration:
  • Processor: Intel(R) Xeon(R) Platinum 8259CL CPU @ 2.50GHz, 16 cores (1 NUMA node)
    • Memory Allocation: 32,000 MB (32 GB)
      • Workload: Warehouse Management System (WMS), primarily handling OLTP operations.

Current Configuration:

• MAXDOP: 4 [Default]
• Cost Threshold for Parallelism: 5 [Default]

Specific Question:

We are seeking recommendations on optimal settings for MAXDOP and Cost Threshold for Parallelism for our SQL Server instance, considering:

1. We have a single NUMA node with 16 cores available.
2. The workload is primarily transactional (OLTP) due to our WMS application.
3. Current settings seem to yield high resource usage during query execution.

Given these conditions, what would be the recommended values for MAXDOP and Cost Threshold for Parallelism to optimize query performance while preventing excessive parallelism?

Answer 1

Hi @Vinith

Quote from MS document Recommendations of MAXDOP, please set MAXDOP to 8 in your environment.

For the Cost Threshold for Parallelism, it depends on your workload, but for OLP workload, a value of 20 or 50 is often recommended.

---

If the answer is helpful,  please click "Accept Answer" and kindly upvote it.