• Adam Thurgar

Cost threshold for parallelism


Is your highest wait statistic type CXPacket?

The simplest explanation of this wait type is that there are parallel plans running.

For more information on wait types go to the following excellent link:

https://www.sqlskills.com/help/waits/

Usually when people see a high percentage of their waits being CXPacket then they think of changing the maximum degrees of parallelism. This could be done at the server level (bit of a sledgehammer approach) or use a MAXDOP hint on the query you don’t want to go parallel or limit parallelism on. This requires very good knowledge of the query.

Since the earliest days of SQL Server there has always been this server configuration setting - cost threshold for parallelism (or CTFP) which is set to five by default. Five whats? I used to think it was some sort of time based setting, but I really don't know. 5 seconds would be way too long for a query to go parallel and 5 milliseconds might be too short. But it has always been 5. This has been since the day of single and dual processors, when SQL Server had no idea about having something like 16 vCPUs available. Plus the change in processor technology has been significant in the past 20 years.

So on a test system with 64GB of RAM and 16 vCPUs available I did some testing. I was able to change the number of processors available to SQL, restart the SQL Server services, run a heavy test case set of queries, clear proc caches and buffers etc and obviously change CTFP.

It took a couple of days of testing but what I found was that the sweet spot for CTFP was a determinant of the number of CPUs and the magic number 5.

CTFP = number of CPUs' x 5 with the maximum being 20.

20 was the sweet spot, even if there were more CPU's it didn't make any difference.

So as a starting point for configuration these days I change CTFP to 20 and then monitor CXPacket.

It may not be perfect but it is a starting point.


8 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t