Part of a DBA’s job is making sure everyone is comfortable with proposed changes. I recently created a Change Request to increase the SQL setting Cost Threshold for Parallelism. Here is the justification I provided to get sign-off from the responsible manager:
- A parallel plan is able to use more CPU, meaning memory and/or disk are the next potential bottleneck(s)
- We have an excessive ratio for the wait type we see (called CXPACKET) when there are too many parallel plans for the disk subsystem per the documentation provided by the DBA Team. This results in SQL Server waiting for disks while tying up CPU time. In other words, the disks are currently the performance bottleneck. Time was requested for the research and write up due to our ad hoc experience of seeing queries with the CXPACKET wait type.
- The query processor assigns a numerical cost to each query and, based whether this is over or under the cost threshold for parallelism setting, will choose to execute a non-parallel or parallel plan.
- By raising the threshold we are reducing the potential number of parallel plans which [should] free up CPU time for other queries and configuring SQL Server to match the underlying hardware. The potentials & shoulds are because we may have so many high cost plans that we’ll need to further increase the number until we see a drop in CXPACKET waits.
- When we first installed <<app 1>> and also on <<SQL instance 1>> in general we had query issues due to this wait type. The quick fix was to set the max degree of parallelism to 1. This setting determines home many CPUs SQL can use: by setting to 1 we effectively set the cost threshold to an infinite number ensuring we would have no parallel plans. Thus we have already tested this at a higher setting in Production.
- This is a very old setting and the default has not changed. As you know, computer hardware has evolved greatly. The cost threshold default set during installation has not evolved with the hardware.
- We’ve run this setting in the Dev, SIT and UAT SQL Instances for over a month with no issues.