Cost Threshold For Parallelism Testing

Tim Peters shows that the Dynamics people are probably right with their assertion regarding Cost Threshold for Parallelism:

These plans are from a SQL Server 2008 R2 SP3 instance that runs a version of Microsoft Dynamics. The MS Dynamics team knows their product issues a bunch of really small queries and recommend a Max Degree of Parallelism of 1. In my case there is one query plan with a cost of 34,402 query bucks that a MAXDOP = 1 can’t afford. Increasing the MAXDOP from 1 while keeping the Cost Threshold at 5 will keep all of my little queries running in their single threaded paradise while allowing some infrequent biguns to spread their lovely wings across a few more threads. My Cost Threshold For Parallelism is set to 5 and I hope to never have to defend my settings in a dark alley with Erik Darling.

Different systems will behave in different ways, so it makes sense that they might require different settings.  In the case of Dynamics, it appears that almost all of the queries are extremely low-cost, so relatively few plans would go parallel and the ones that do, you probably want going parallel.

Related Posts

Schema Comparison With Visual Studio

Arun Sirpal shows off the Schema Compare functionality within Visual Studio: A very common requirement which can be satisfied by various tools. Personally I like using Visual Studio 2017 Community Edition and I thought I would do a quick overview of it. First thing, you can find the download from this link: https://www.visualstudio.com/downloads/  and once installed (making […]

Read More

Using Query Governor

Monica Rathbun explains what the query governor is in SQL Server: It’s simple. This option, available in SQL Server 2008 standard and forward, will prevent long running queries based on run time measured in seconds. If I specify a value of 180 the query governor will not allow any execution of a query that it estimates will […]

Read More

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031