MAXDOP in SQL Server & recommended settings for Business Central On-Prem

21-07-2025
MAXDOP in SQL Server.


For best SQL Server performance


MAXDOP controls how many CPU cores SQL Server can use to execute a single query in parallel. It helps balance performance and resource usage, especially in systems with multiple processors.


1. General Guidelines

  • MAXDOP = 0: SQL Server uses all available processors (not recommended).
  • MAXDOP = 1: Disables parallelism (for OLTP systems).
  • MAXDOP = 4 or 8 or 16: Choose the number as per the server cores. It is best to fine tune by checking the performance. 


2. Based on CPU Configuration

  • If hyper-threading is enabled, set MAXDOP to half the number of logical processors.
  • Avoid setting MAXDOP higher than the number of physical cores.


3. Cost Threshold for Parallelism

  • Increase from the default value of 5 to 25–50 to reduce unnecessary parallelism.


4. Per-Query or Per-Database Settings

  • You can override MAXDOP at the query level or database level for more granular control.


⚙️ How to Configure MAXDOP


Right-click the server → Properties.


Go to the Advanced tab.


Set Max Degree of Parallelism.