White Papers

31 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
MB/sec) compared to the read throughput (895 MB/sec) in Figure 7 is due to the change in the memory
setting to 115 GB.
While implementing the columnstore index on the two largest tables, the query execution time further
reduced by 67% compared to the baseline without columnstore indexing and by 21% compared to having
the columnstore index on just the largest table. This shows that implementing columnstore index on large
tables is beneficial. While columnstore index can be built on very small tables, the performance advantage
is less noticeable when the table is small.
Throughput and execution time comparison with and without columnstore index Figure 11
The decrease in throughput in the columnstore index is due to the change in the I/O block size and
read/write percentage (refer to the SANHQ results shown in Figure 14). This change in block sizes and
read/write percentage happened while using columnstore index because only the needed columns were
brought into the memory and were heavily compressed. The data can often be compressed more
effectively when it is stored in columns rather than in rows. Typically, there is more redundancy within a
column than within a row allowing greater compression.
When data is compressed more, the effective data that is fetched from the storage is less. A larger fraction
of the data can reside in a given size of memory. This reduces the read throughput significantly, allowing
for faster query response times. Retaining more of the working set data in memory speeds up response
times for subsequent queries that access the same data.
Columnstore index efficiency enables heavier workloads to run. EqualLogic storage has more room to
achieve throughput at around 895 MB/sec (table partition studies, Figure 7). However, the higher workload
test (users greater than six) was not performed since the scope of this test was limited to evaluating the
performance benefits of using columnstore index and not saturating the single array.
906
526
335
429
180
141
0
50
100
150
200
250
300
350
400
450
500
0
100
200
300
400
500
600
700
800
900
1000
No
Columnstore
Index
Columnstore
Index on
LineItem
Columnstore
Index on
Lineitem &
Order
Time (minute)
Throughput (MB/sec)
With and without Columnstore Index
Average Read
Throughput
Query Execution
Time