Configuring Parallelism

Parallelism is a powerful feature in SQL Server designed to allow greater bandwidth for high impact queries. Most of the time however, I’ve seen too many queries qualifying for parallelism and essentially bringing the SQL Server box to it’s knees.

To understand parallelism, we need to understand SPIDs (Server Process ID’s). These SPID’s act essentially as threads on a SQL Server. Normally there is one SPID assigned to each database connection. Sometimes, if the query is determined to be expensive, a SPID can be split out into multiple execution threads. This is known as parallelism.

Cost Threshold for Parallelism

Prior to breaking a SPID out into multiple threads, there is a cost determination. This is called the cost threshold for parallelism (set in seconds) and is set at the server-level. The idea is to allow parallelism when a query is estimated to run over a certain number of seconds.  Generally, the determining factor is the estimated number of rows that will be returned either in the main query or an inner query within the execution plan.  To set parallelism right click on the Server Instance in Management Studio. And select “properties”.

Once properties is selected, a new window pops up displaying a more detailed server settings. We want to select on “Advanced”

Now, in the lower middle of the screen we see the settings for Parallelism. The main two we are concerned with the is the cost threshold and the Max Degree of parallelism.

What Settings to Choose?

You need to choose settings that compliment both the primary function of the SQL Server instance, and the power of hardware you have available. Generally speaking, if the server instance is catered toward end-users and you have a lot of throughput (or connections) hitting the server, then you do not want queries to qualify for parallelism easily.

As an example, imagine you have a common stored procedure accessed by all users that is very sluggish and performs too many reads on every execution. If this query were to qualify for parallelism, then every time it is executed, it would spawn multiple threads (potentially equal to the number of processors). This behavior can bring your SQL Server to it’s knees. Yes, the execution may be quicker for each single user, however the additional resources used may spill over and cause an IO or CPU bottleneck.

With this in mind, for OLTP systems, it is generally better to set the cost for threshold higher, somewhere between 10 and 30. This way, less queries will use less resources. Now there is one additional option, which is, how many threads to spawn once we do qualify for parallelism. To set this you will use the Max Degree of Parallelism option.  Setting this option to 1 will completely disable all parallelism.  This is good to do when troubleshooting slowness issues on a server.  This option is settable using a query hint.

If your server’s primary function is as a data warehouse, then it is plausible that the parallelism option may be set low as there will be relatively few queries running concurrently and therefore can take more of the physical resources available to facilitate quicker executions.

MAXDOP Query Hint

This is probably the query hint I use the most, and the value I use the most is “1”. This is because when creating procedures that contain fairly extensive statements, I usually do not want them to consume resources if things get out of control.

Set the hint this way:

[cc lang=”sql”]
SELECT *
FROM Sales.SalesOrders
OPTION (MAXDOP 1)
[/cc]