How to fix a slow query performance issue in SQL Server related to MAXDOP (Maximum Degree of Parallelism)?

Step-by-Step: Tune MAXDOP for Better Performance

1. Check Current MAXDOP Setting: Run this:

   EXEC sp_configure 'show advanced options', 1;
   RECONFIGURE;
   EXEC sp_configure 'max degree of parallelism';
   

2. Understand the Default:

   • Default is 0, meaning SQL Server uses all available CPU cores for parallel plans.
   • This can cause CPU pressure or CXPACKET waits.

3. Determine Optimal MAXDOP: Follow Microsoft’s recommendation:

   • If server has ≤8 logical CPUs → set MAXDOP = number of CPUs
   • If >8 CPUs → set MAXDOP = 8
   • For NUMA nodes, set MAXDOP = number of CPUs per NUMA node (but not more than 8)

4. Set MAXDOP: For example, to set MAXDOP to 4:

   EXEC sp_configure 'max degree of parallelism', 4;
   RECONFIGURE;
   

5. Test Performance:

   • Run the slow query again and compare execution time.
   • Check for reduced CXPACKET waits in sys.dm_os_wait_stats.

6. Optional – Use Query Hint (Scoped): If you don’t want to change the server-level setting:

   SELECT * FROM YourTable
   OPTION (MAXDOP 2);