SQL Server Parallel Execution Plans

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

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |