Table 1 below shows the execution plan estimated costs for the 22 TPC-H queries with a 1GB (data only) LineItem table of 6 million rows. In this case, 18 of the 22 queries generated a parallel execution plan. In some cases (Query 1 and 6), the parallel execution plan is approximately one-half the cost of the non-parallel plan. In most cases, the parallel plan is just somewhat less expensive than the non-parallel plan, but not by as much as 50% less. In one case, Query 5, the parallel plan is actually more expensive than the non-parallel plan, even though the default plan (no hints) is the parallel plan.
|
Query |
Non-Parallel |
Parallel Plan |
|
1 |
121.3 |
60.7 |
|
2 |
5.0 |
|
|
3 |
111.8 |
77.0 |
|
4 |
109.0 |
103.8 |
|
5 |
132.3 |
145.6 |
|
6 |
15.8 |
7.9 |
|
7 |
72.2 |
65.7 |
|
8 |
138.3 |
|
|
9 |
199.3 |
|
|
10 |
112.1 |
107.8 |
|
11 |
33.9 |
|
|
12 |
136.1 |
127.9 |
|
13 |
32.4 |
29.5 |
|
14 |
6.4 |
6.0 |
|
15 |
5.2 |
4.0 |
|
16 |
19.5 |
17.5 |
|
17 |
20.5 |
20.3 |
|
18 |
177.8 |
152.0 |
|
19 |
48.5 |
47.7 |
|
20 |
24.8 |
20.7 |
|
21 |
367.6 |
360.6 |
|
22 |
15.8 |
15.7 |
Table 1. Plan Cost for TPC-H queries on 1GB LineItem dataset.
Table 2 shows the best measured run times for SQL Server 2000 and Windows Server 2003 on a 2×2.4GHz Xeon server with Hyper-Threading (HT) enabled, and 2GB memory. Each query was run several times consecutively, so that the dataset should be mostly cached in memory, as evidenced by the minimal disk activity. The reason for choosing the best time from several consecutive runs was to eliminate the query optimization cost, which itself would make for a very interesting investigation. There is occasional variation in run-times, even between the second, third and fourth runs. The run times were captured in SQL Profiler along with CPU time and the degree of parallelism (DOP) which verifies the actual number of threads used.
|
Query |
DOP 1 |
DOP 2 |
DOP 4 (HT) |
|
1 |
20,703 |
12,686 |
13,563 |
|
2 |
126 |
||
|
3 |
3,153 |
2,686 |
3,016 |
|
4 |
4,140 |
2,983 |
2,610 |
|
5 |
4,423 |
3,796 |
3,953 |
|
6 |
546 |
343 |
373 |
|
7 |
4,110 |
2,486 |
3,060 |
|
8 |
3,376 |
||
|
9 |
11,500 |
||
|
10 |
2,610 |
2,250 |
2,080 |
|
11 |
876 |
||
|
12 |
3,516 |
2,733 |
2,410 |
|
13 |
9,343 |
7,813 |
5,436 |
|
14 |
436 |
516 |
576 |
|
15 |
390 |
610 |
783 |
|
16 |
3,173 |
2,390 |
2,470 |
|
17 |
203 |
373 |
716 |
|
18 |
11,720 |
7,860 |
7,516 |
|
19 |
560 |
360 |
406 |
|
20 |
453 |
1,280 |
1,470 |
|
21 |
14,500 |
9,486 |
9,250 |
|
22 |
2,373 |
1,420 |
2,440 |