All the previously posted results (May 25th and May 29th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).
Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.
DBCC DROPCLEANBUFFERS
go
SELECT COUNT(*) FROM dbo.test;
It turns out that with parallelism the test query exhibited significantly different performance characteristics with the three data sets we have been using as test vehicles than without parallelism. And again, the nature of the storage system proved to be a significant confounding factor. The following table shows the results with query parallelism on both the internal drive (the C: drive) and the drive presented from a departmental level disk array (the E: drive), the same two drives used in the May 25th update and the May 29th update.
|
Drive |
Test Run |
Elapsed Time (second) |
|
Internal (C:) |
Adam’s script – test run 1 |
90 |
|
Adam’s script – test run 2 |
88 |
|
Adam’s script – test run 3 |
87 |
|
Tibor’s script – test run 1 |
217 |
|
Tibor’s script – test run 2 |
219 |
|
Tibor’s script – test run 3 |
226 |
|
Linchi’s script – test run 1 |
325 |
|
Linchi’s script – test run 2 |
324 |
|
Linchi’s script – test run 3 |
324 |
|
Disk array (E:) |
Adam’s script – test run 1 |
74 |
|
Adam’s script – test run 2 |
75 |
|
Adam’s script – test run 3 |
72 |
|
Tibor’s script – test run 1 |
93 |
|
Tibor’s script – test run 2 |
97 |
|
Tibor’s script – test run 3 |
89 |
|
Linchi’s script – test run 1 |
51 |
|
Linchi’s script – test run 2 |
51 |
|
Linchi’s script – test run 3 |
51 |
Note the dramatic change in the relative impact of the data sets on the test query when the storage performance and query parallelism were added to the mix. In particular, note how the test query was able to perform much faster on the faster drive (drive E:) with some data sets.
I just want to present the data points in this post, and will dive in a bit in the next follow up.