[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post.
Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results and mine. [2008/08/25: Okay the difference is not as significant as I had thought.]
My results were obtained on SQL Server 2005 Enterprise x64 Edition running on Windows Server 2003 Enterprise x64 Edition. The test server, whose make and model will remain undisclosed, had four 2.93GHz quad-core Intel Tigerton sockets (Xeon X7350) with 64GB of RAM with 20GB allocated to the SQL Server buffer pool. The TPC-H scale factor was set to 10, and the data were generated with DBGEN from tpc.org.
My results are as follows:
|| MAXDOP 1
|| MAXDOP 4
|| MAXDOP 8
|| MAXDOP 16|
All the numbers in the above table are query elapsed times in milliseconds except the very last line highlighted in yellow, which are in seconds. The last line contains the total elapsed time for Query1 through Query22 for each MAXDOP setting. All the results were obtained with all the database pages cached in the buffer pool. The same TPC-H query stream (i.e. Query 1 through Query 22) was repeated for 10 times with all the results from the first run thrown out, and the max elapsed time and the min elapsed time for each query for each of the subsequent runs were also thrown out before the elapsed times were averaged.
So what's the difference between Joe's results and mine?
Although there are some differences in the elapsed times in terms of the absolute numbers, the differences are minor enough to be no cause for alarm, especially when you take into consideration the platform differences and probably some differences in the test setup.
However, the deline in the elapsed times in my test results is more significant than it is in Joe's results when MAXDOP is changed from 1 through 16. The difference is not as dramatic as I had throught, and initially commented on in the original version of this post (because I was reading the wrong line for whatever reason).
Given that TPC-H has been around for a long time and all DBMS vendors have tried very hard to optimize their products to performance well on TPC-H, I do not expect SQL Server to be an exception, and am rather pleased to see that it scales very well with MAXDOP on these queries.
I'm a bit embarrassed that I didn't read Joe's results correctly first time around, but felt that it's time well spent to be able to cross check independent tests results from realistic envrionments.