SQL Server Performance Tuning

How Removing One MAXDOP Hint Made a Query 5.5 Times Faster

Updated August 21, 20252 min read

Removing one MAXDOP hint: 5.5x faster. Ploom carries away a leftover traffic cone from a finished road.

Improvement after tuning

5.5xDURATION

A query hint is a decision someone made for the optimizer.

It can stay in the code long after the reason for it is gone.

Problem

One leftover query hint was making a stored procedure 5.5 times slower. We found it during one of our database assessments.

The procedure had been refactored at some point. But the developers left an old `MAXDOP` query hint (max degree of parallelism) in place. It limited how many threads the query could use, and it no longer matched what the query did.

Here’s a glance at the pre-tuning metrics:

  • Duration (ms): 2,933

Solution

This is what we did to fix the issue: we removed the MAXDOP hint.

We did not add an index or rewrite anything.

Before vs. After

The run went from 2,933 ms to 531 ms.

BeforeAfterImprovement
Duration (ms)*2,9335315.5x
*Measured with one run before and one run after the change. CPU and disk reads were not recorded for this case.

After removing the hint, the query ran 5.5 times faster.

Final thoughts

`MAXDOP` controls parallelism for one query. It can be the right tool, but it is a fixed answer to a question that changes when the code or the data changes.

And temporary fixes tend to stay. A hint added to get through one bad week can still be in your code years later.

When you refactor a query, review its hints too. Each one should still have a reason to be there.

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.

Discover More ➜

SQL Server Managed Services

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

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜