White Papers

24 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
eight partition test. This was because there were fewer requests queued on each LineItem volume due to
data being spanned across more volumes in the eight partition case. This can be seen from Figure 8.
Figure 8 shows the average queue depth (or number of outstanding I/Os) of the LineItem volumes (which
contained the partitions) from SANHQ. The number of outstanding I/Os on the eight partition test was 41%
less compared to the four partition test. This proved that the request were getting served quicker and
provided a lower execution time.
Average queue depths – LineItem volumes Figure 8
The eight partition test showed improved SQL Server query efficiency compared to the four partition test
case. However, the read throughput remained the same since the single array was running at its maximum
achievable throughput. With more volumes, the number of outstanding I/Os in each volume was less and
provided faster query execution times. The query execution times and I/O throughput depends on the
partitioning scheme chosen, which in turn is heavily dependent on the data and workload characteristics.
Typically for DSS database applications, partitioning offers these benefits when implemented on larger
tables using a scheme based on the application and query behavior.
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. It must be noted that as per
Microsoft (Refer to “Query Speed” in section 6.2
), table partitioning would not improve any query
execution speeds for data warehouse relational databases. It would yield similar performance for
partitioned and non-partitioned fact tables.
29
17
5
10
15
20
25
30
35
4 Partitions 8 Partitions
Number of outstanding I/Os
LineItem queue depth comparison