Here what can be done when you have one of our DBA plans.
The procedure below was extracted from a stored procedure that was found during one of our comprehensive database assessments.
What was done
We removed the MAXDOP hint.
In this case, the procedure was refactored, but the dev team did not change this query hint.
The results
The result, you can see below.
Technical background
MAXDOP is a query hint that can be added to the end of your SQL query to control parallelism. When you use MAXDOP, you specify the maximum number of parallelism threads that SQL Server uses for that query.
As with all query hints, it is recommended that you know internally what you are controlling by specifying this hint.
Sometimes DBA’s are forced to work with what we have and choose one evil over the other. A temporary solution for a problem can be forgotten and becomes permanent.
Learn more about the MAXDOP setting in the SQL Server.
See more SQL Server Performance Optimization examples here.
