[Case study] SQL query hint removal makes TSQL run five times faster

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.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *