White Papers

36 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
without columnstore index. Using columnstore index along with table partition would help make use of
the table partition benefits (such as DW loading and maintenance) as well as the columnstore index
benefits of improved query execution times.
6.4 SAN scaling
The SAN scaling test measured the throughput scalability as the number of EqualLogic PS Series storage
arrays increased within a group. This section covers,
• Performance comparisons between IOMeter and TPC-H test results on a single array.
• Scaling the arrays (1 server and 2 arrays).
• Scaling both the arrays and the hosts (2 servers and 2 arrays).
6.4.1 Performance comparaison - IOMeter versus TPC-H
This section compares the maximum throughput achievable from a single array using IOMeter (section 5)
and table partition tests (section 6.2.2) prior to performing the scalability studies. In the TPC-H application
test, performance data was gathered from six users with 132 queries. The read/write ratio for the actual
application testing was about 96/4, the read I/O block size was around 170 K and the number of volumes
was 13 (the read/write ratio and the I/O block size are from SAN HQ). These results can be compared to
the 100% sequential reads with 128 K block size IOMeter test on 10 volumes (refer to
section 5, Figure 3).
The results collected from the studies with one array are graphed in Figure 15.
Storage I/O throughput comparison on 1 array – IOMeter and application simulation Figure 15
Using a more realistic TPC-H Benchmark Factory test, the results presented in Figure 15 show that a single
array could achieve an average throughput of 895 MB/sec. A difference in throughput of around 14% was
observed between the baseline-IOMeter test simulating a DSS workload and the actual database test on
1042
895
600
650
700
750
800
850
900
950
1000
1050
1100
IOMeter
(128 K, Read/Write%-100/0)
TPC-H Database
(170 K, Read/Write%-96/4)
Throughput (MB/sec)
Average Read throughput with one array
1xarray