SQL Server comes with built-in support for using parallelism for query execution. That means that SQL Server will split up the execution of a query among different CPUs if it thinks that it can get the query results faster that way. The default setting in SQL Server will use the number of CPU cores on the system as the maximum number of parallel processors to use. Okay… this is great, right?
The Grass Isn’t Always Greener
Although the thought of running 1 SQL query but using multiple processors sounds great, sometimes it can actually sometimes cause slower queries. A couple issues I have ran in to in our production environment are:
- Sometimes the parallelism can take away CPU from other processes that are trying to run at the same time.
- Sometimes the time that it takes to reassemble the results is longer than the not using parallelism. What this means is that when a query is split among different processors, it will need to merge them back together before it sends it to you. Sometimes this can be a longer task then just using 1 processor the whole time.
MAXDOP
MAXDOP is a query hint that can be added to the end of your SQL query to control the amount of parallelism that happens. When you use MAXDOP, you will specify the maximum number of parallelism threads that SQL Server uses for that query. In fact, the word MAXDOP stand for “Maximum Degree Of Parallelism”.
Here is an example of how to use the MAXDOP query hint to limit the number of parallel threads to 1 (essentially disabling the parallelism):
SELECT * FROM Animal A INNER JOIN AnimalType AT ON AT.AnimalTypeID = A.AnimalTypeID ORDER BY A.AnimalName OPTION (MAXDOP 1)
To show how this affects the query, let me show you the execution plan when I supply the MAXDOP 1.
Here is a screenshot of the execution plan of that exact same query without specifying the MAXDOP. You can see that it now uses parallelism.
Will Removing MAXDOP Help My Queries Run Faster
Is MAXDOP a silver bullet for making queries faster? No. In most cases you will just want SQL Server to manage the parallelism. This is usually the fastest/best way for your query to execute. But occasionally if you are having a slow query, you can try changing the MAXDOP setting. You will need to test your query before and after to see if there is significant improvement.
How Do You Know If Your Query Is Using Parallelism?
Here are a couple ways that I use to find out if parallelism is happening to a query.
- Check out the actual execution plan. After you run the query and the execution plan shows up, you will see a “Parallelism” step. You can see the screenshot above for what that looks like.
- Monitor the Activity Monitor in SQL Server Management Studio. This is a great way to detect if your production applications are causing parallelism. It’s really easy to see in Activity Monitor because it looks like the same line repeated many times.
Reference: http://msdn.microsoft.com/en-us/library/ms181714.aspx