MSSQL customer advisory team posted an article on Partitioned Tables, Parallelism & Performance considerations. IMHO, it's a bit crazy post which also includes "best practices":
"The above comments on partitions and parallel performance are most applicable to Data Warehouses, batch processing, and reporting. Not all data warehouse environments will want to enable parallelism for all queries. Specifically, parallelism is most effective if the system is running only a few queries at a time and you want as many resources possible made available to those queries to minimize execution time. If the data warehouse is already a high concurrency environment, parallelism will not improve throughput or response time since the collection of single-threaded queries are likely already consuming the available resources. Likewise, for peak performance, you do not want parallelism in a high concurrency OLTP workload."
From that I can understand that developers only need parallelism for performance when they use batches and reporting and not when they work with online highly loaded OLTP or semi-OLTP. Speaking of which -- when you don't need to boost performance at all or you can achieve it alternatively. So, avoid it.
Friday, December 02, 2005 4:38 PM