Parallelism setting in SQL Server – reduce CXPACKET waits?

This is one of the hot topic within the DBA community, what should be the best setting for parallelism in your environment? The answer is – it depends! J

Let’s look at what is the default setting and where should we start to tune it. There are two related setting for parallelism, they are max degree of parallelism and cost threshold for parallelism. The default setting for them are 0 (use all processors) and 5 respectively. Let go through each of them in details.

This setting defines how many CPU will be used per query execution, it’s a global server setting that govern the whole instance. While the default of using all available CPU might be great for large queries, it might cause other performance problem. For a pure OLTP-type workload (very small insert, update and deletes), setting it to 1 might be beneficial, but for most general system, you going to have a mix workload, so setting it to 1 might not be the best scenario. A general rule would be setting it to be the number of physical core for a given CPU. E.g. A 2 processor with 8 physical core each and enabled hyperthreading, you will have a total of 32 logical core, the recommended setting would be 8. Which is also a recommendation from Microsoft as well (https://support.microsoft.com/en-us/kb/2806535)

This is the value used by the optimizer to determent if a parallel plan should be used for a given query. The default value is 5, which is an internal cost value used by the optimizer in modern servers to decide if it should split to use more than 1 CPU. With this leaves as the detail, it means most of the query will use a parallel plan. Setting it to a higher value (e.g. 50) might be a good place to start, and check if all your critical queries perform the same if not better. You might need to keep tune this value up or down depends on your workload,

but the key to look out for is the balance in OLTP-type workload and large queries co-exists on the same box.

Many will panic that their system has CXPACKET waits and it’s a bad thing to have them, where they will need to get rid of them altogether. Before you jump to conclusion, let’s check out what CXPACKET really is.

Imagine you are a manager and got a project comes in, you distribute it to all the staffs to work on that project by breaking down into smaller tasks. Some might finish faster than the other, or some task might be easier than the others, thus the time required to complete is different. CXPACKET is the wait time for everyone to finish and complete all their small tasks for the given project. So is that a huge issue? Not really, you will always have CXPACKET waits if you have parallel query! However, getting a large number of CXPACKET waits might indicate it’s something worth looking at, and might look for area of improvement somewhere.

Many have suggested that the way to reduce CXPACKET waits is to set the max degree of parallelism to 1, which means it do not need to wait for other thread since it will only use 1 core to process any query, it will take out this waits type altogether. If that is your goal, that will be the way to do it, but does it helps the overall performance – probably not! So what should be a better approach?

A more proper approach would be set the max degree of parallelism as suggested above, tune the cost threshold for parallelism, ensure that small query will use single-threaded plan, but parallel plans will be used for larger queries where it needs the power. To achieve this, you will need to understand your workload! Use SQL server plan cache to find out queries that requires more resources, repeatedly test your workload and review the setting to ensure you get the most optimal value for your workload.

This is a big and one of the hottest debate topic in SQL community, there is no absolute correct answer for those values. The key is to keep reviewing the workload and tuning it based on that.