Sunday, May 02, 2010


It's not a word that rolls of your tongue. Try explaining - without appearing to have recently suffered a blow to the head - to a room full of people that turning down your SQL Server's maximum degree of parallelism may solve some of their performance woes.

Parallelism (this definition is stolen from the beginning of Joe Duffy's book, Concurrent Programming on Windows) is the use of concurrency to decompose an operation into finer grained constituent parts so that the independent parts can run [separately].

Parallelism is intended to take advantange of physical resources that might be otherwise unused. If you have a large table or index that needs to be scanned, and you have 24 available cores, SQL Server might want to parallelize your query into 24 concurrent tasks. In this case, it usually leads to resource starvation, how many database servers are sitting idle all the time, waiting for your "embarrassingly parallel" query to arrive. Do you need to put a question mark at the end of a rhetorical question. On a SQL Server, Microsoft's official word is to keep the maximum degree of parallelism (dop) below 8, or the number of cores in a single socket, whichever is lower. Don't forget that in the example above, your server would need 24x the RAM required to turn the sequential operation into a concurrent one.

PFX is a .NET library for enabling parallelism in your C# code. Although I haven't got to the bottom of it, I'm tempted to believe that it - too - would depend entirely on the load profile of your server, so give it a try and make sure you test it with an adequate cross section of concurrent users to make sure that the parallelism selfishness isn't the cause of any new performance issues.

No comments: