White Papers

22 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
Figure 7 shows the test results that were collected while running six users (132 queries) on four partitions
and then on eight partitions. The average read throughput remained almost the same since the single
array was running at its maximum achievable throughput. However, the query execution time improved
during the eight partition test by 11% due to the increased number of volumes. This means more reader
threads get issued by SQL Server and fewer volume queue depths seen at the storage.
Four and eight table partition performance comparisons Figure 7
Table partitioning speeds up the load process and provides easy maintainability, but is not beneficial when
it comes to query execution speed in the case of data warehouse databases. In the tests conducted for
this paper, either the four or eight table partition tests did not yield a query performance improvement
over a non-partitioned table when more users ran the same queries. There was throughput improvement
(892 MB/sec) with the table partition (refer to Figure 7) compared to the non-partitioned table (665
MB/sec, refer to Figure 5), but at the cost of execution time. In SQL Server there are multiple threads
accessing the same table partition with more users, more threads access the same partition. This makes
the threads wait and I/O access is more randomized at the logical volume/physical disk level, causing
increased query execution times. In spite of table partition’s reduced query performance; the eight
partition layout was used for the scalability tests (section 6.4
) due to the benefits offered by table
partitions.
Comparing the four and eight partition case in Figure 7, the eight partitions offered better execution times.
To understand the cause for improved query execution time in the eight partition case, the queries were
examined individually by running a single user power test (one user with 22 queries run in the same order).
The power test was run on both the four and eight partitions. Table 4 shows the average response times of
these 22 queries.
892
895
487
433
400
420
440
460
480
500
850
860
870
880
890
900
4 Partitions 8 Partitions
Time (minute)
Throughput (MB/sec)
Read throughput /execution time comparisons
Average Read Throughput
Query Completion Time