Improvement after tuning
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.
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 2,933 | 531 | 5.5x |
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.



