Table 2. Best measured duration (ms) on 1GB LineItem dataset.
When the DOP is 2, it appears that the operating system or SQL Server knows to use the two different physical processors rather than both logical processors on a single physical processor. It is possible this is achieved entirely by the sequencing of physical and logical processors. At DOP 4, there are only 2 physical processors, so this is not a test of the ability of SQL Server parallel execution to scale to 4 physical processors, but rather a test of the benefit of HT in parallel execution plans.
The blue font in the DOP 2 column shows cases where the query execution time speedup was more than 30%. In all, 7 of the 18 queries with parallel plan showed better than 30% speed-up, while 4 were slower. The sum total execution time for all 18 queries with parallel execution plans was 29% faster than the same 18 queries without parallel execution plans. The queries that were slower were not predicted to be slower by the execution plan, and the query predicted to be slower was in fact faster with parallel execution. It does appear that SQL Server significantly speeds up the parallel execution of table and index scans even though this is not reflected in the plan cost estimate.
In all but one query, CPU utilization was higher with the parallel plan compared to the non-parallel plan. For the 18 queries with parallel plans, the overall average increase in CPU utilization was 52%. For this reason, parallel execution plans are not recommended for applications where very high through-put is desired, typically OLTP application. Parallel plans are very valuable when speed is desired for a single or few users, typically maintenance operations and DSS applications.
In five queries, parallel execution on all logical processors provided further speed improvement, but in five other queries, the use of the additional logical processors degraded performance to worse than both non-parallel execution and parallel execution with the two physical processors. Hyper-threading is clearly a feature with potential to be very useful. However, it should not be used blindly. Hopefully Microsoft will investigate HT very carefully in Yukon and devise clever algorithms to determine when HT should be used and when it should not be used.
Summary
The examination of the execution plans for a range of queries definitely suggests that the unit of measure in the plan cost is time rather than CPU usage relative to a single processor. Hence a parallel execution plan may show a lower cost than a non-parallel plan. Furthermore, SQL Server rates the parallel execution of certain operations, such as an index seek and hash match, as being approximately twice as fast with two threads compared to a single thread. For some reason, SQL Server does not rate table and index scans as deriving comparable benefit from parallel execution even though actual parallel query measurements indicate this to be the case.
Parallel execution plans require additional operations that in some cases the additional overhead negates the benefit of a parallel execution plan, but this needs to be determined from actual tests. It has not been determined what the time unit the plan cost represents. A reasonable guess would be that the plan cost is in seconds on a processor that was prevalent at the time SQL Server 7.0 was in development. This could anything from a Pentium 100MHz to a Pentium Pro at 200MHz. This would make sense in that the actual run time was on average 20 times faster on the Xeon 2.4GHz system than represented by the plan cost if the unit of measure was time in seconds.
Published with the express written permission of the author. Copyright 2004 Joe Chang.
]]>