Art of the DBA Rotating Header Image

Parallelism, SQL Server, and you: Round 2

Previously on Art of the DBA: The basics of parallelism.

Control! You must learn control!

So now that we’ve gotten the basics of  parallelism under our belt, it’s time to see how SQL Server uses it. The bad news is that most of the decision making about using parallelism is made by the black box known as the query optimizer. However, there are two parameters in the SQL Engine that we can manipulate to control whether or not a query will be parallelized, both managed in the server configuration.

The first option we can adjust is the cost threshold for parallelism.  Generally speaking, this is the value that SQL Server looks at when evaluating the cost of a query. If the query exceeds that cost, it will be parallelized. Otherwise, it will be run as a single threaded operation. Query cost is the estimated number of seconds a query will run in a single thread and to get an idea of what your query cost is, check your query execution plan. The default value for the cost threshold parameter is 5, so if a query is estimated to take longer than 5 seconds, SQL Server will parallelize it.

The second option is Max Degree of Parallelism, commonly referred to as MAXDOP. This defines the maximum number of threads a parallel query will be given.  The default value for MAXDOP is defaulted to 0, meaning  SQL Server will not limit the number of parallel threads it will create for a query, but it’s usually not the best option. You can find a lot of documentation out on the ‘tubes concerning MAXDOP, but the general rule of thumb is to set it to half the number of processor cores you have, up to 8.

The nice thing about MAXDOP is you can also use it as a query hint.  Using it, you can alter the degree of parallelism for your specific query and not have to worry about mucking with the server setting. Usually you’ll want to make use of this hint to set your max parallelism to 1 if you’re running a long running background task, like maintenance, and you don’t want it to interfere with your normal workload.

Decision Time

Knowing all this, how do we adjust our SQL Server to get the best use out of parallelism? The key is understanding which workloads take advantage of parallelism and which don’t. The first step is figuring out whether your server is an Online Transactional Processing (OLTP) server or a Decision Support System(DSS).

A DSS server, also known as a data warehouse, will benefit most from parallelism. Since your server will usually have a limited number of reporting queries with large datasets, you’ll want that workload spread out to get it processed faster. Also, because you’re dealing with data that is relatively static (it really only changes when you’re processing your warehouse loads), you’re not concerned with holding any locks on your databases, nor do you need to worry about preventing or delaying any data changes from happening while your parallel queries are running.  So parallelism is ideal for devoting as much horsepower to each query as possible.

This is also why parallelism is not a good choice for OLTP systems. Remember, when a CPU execution unit is working on a thread for your query, it won’t be working on anything else. This means that you could be queuing up and delaying important transactions waiting on parallel queries to complete. Since your OLTP system will usually be expected to be processing a lot of small transactions very quickly, a couple parallel queries could really bottleneck your system.  Instead, you want to make as many threads available to your processes as possible and not limit the number of processes you can handle at one time.  This is why you will usually see recommendations for low MAXDOP settings for OLTP, so you can make more CPU execution units available to your total transaction load.

When all is said and done, managing your MAXDOP and cost of parallelism settings are very much part of the DBA art form, where you will need to test and evaluate your workload to find the settings that are appropriate for your server. Here’s a couple steps to get started with on your server:

  1. What kind of queries and how many transactions does my server see? Talk to your developers about the processes and take a look at your Batch Requests/second perfmon counter to help you evaluate this.
  2. Check your current MAXDOP and cost of parallelism settings on your server.  Are they set to the defaults?
  3. What do your CXPACKET waits look like relative to other waits on your server? High CXPACKET waits might be an indication that the MAXDOP on your server is set to high.  To find out your waits, Glenn Berry has an excellent DMV query within his SQL Server 2008 Diagnostic Information Queries that will provide this information for you.

Once you have this information, you can make a plan to adjust your parallelism settings, but you’ll need to test any changes to your settings before implementing them.

Parallelism, while it may not have a tremendous impact on your system, is an important factor nonetheless. Understanding and making proper use of it will help you manage and improve performance on your server.  As an editorial note, I would like to see SQL Server give you some more fine grained control over parallelism, like Oracle does in their query hints.  Hopefully we’ll get to see that in a future release.

Thanks for reading and feel free to leave comments or questions below!

2 Comments

  1. Glenn Berry says:

    Nice post, Mike. When I see high CXPACKET waits, I always look pretty hard for missing indexes or poorly written queries that might be causing the query optimizer to parallelize a query to try to compensate for that. After I have eliminated that, then I think about setting MAXDOP to a lower value.

    Thanks for the mention!

    1. Mike Fal says:

      Thanks for the feedback Glenn! Yeah, CXPACKET waits are pretty ambiguous and I could have probably written 4-5 posts on them alone.

      Thanks for making your scripts available as well! Those queries are probably the number one tool in my DBA toolbox, particularly the queries for file I/O. The more people who know about your scripts, the better!

Leave a Reply

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