SQL Server Health Check

How to Check MaxDOP in SQL Server and Set it for Optimal Performance

Updated
3 min read
Written by
Mark Varnas

Default max degree of parallelism (MAXDOP)

The max degree of parallelism option is used by SQL Server to limit the number of CPUs to be used for parallel query execution.

This is just a fancy way of saying, how many CPU cores do I want utilized when query runs.

By default, SQL Server uses all available CPUs during query execution.

Defaults are not good.

While using all CPUs available for large queries sometimes makes sense, in most cases this is a bad setting. It can cause performance problems and limit server’s concurrency.

This setting goes hand in hand with the cost threshold for parallelism.

Microsoft best practices for MAXDOP

When a query goes parallel (multiple CPU cores are used to answer the query), you do not necessarily want all CPU cores used. This is where one heavy/bad query will impact everything your SQL Server runs.

But you do not want to let queries go parallel and just have CPU cores idle.

There is no one rule which works for cases.

MAXDOP configuration will depend on the machine and the version of the SQL Server.

Check out the Microsoft best practice guide for setting the max degree of parallelism.

Note: SQL Server 2019 (15.x) automatizes the recommendations for settings MAXDOP configuration during the installation process. You can accept the recommended setting or enter your value.

How to check and change the max degree of parallelism (MaxDOP) settings

To change the default value at the server level using SSMS, follow these steps:

  1. Open Object Explorer
  2. Right-click the instance
  3. Select Properties
  4. Click the Advanced node
  5. In the Max Degree of Parallelism box, set the value.

Also, you can change MAXDOP using T-SQL, using TSQL example below:

EXEC sp_configure 'show advanced options'
GO

RECONFIGURE
WITH OVERRIDE;
GO

EXEC sp_configure 'max degree of parallelism', 16;
GO

RECONFIGURE
WITH OVERRIDE;
GO

More information:

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Learn More

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Learn More

Emergency SQL Support

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Learn More
Explore All Services