Manualshelf

White Papers

ManualsBrandsDell ManualsConverged InfrastructureStorage Solution Resources
21222324252627282930
28 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
6.3.4 Columnstore index performance comparisons - TPC-H
The tests performed in this section evaluate the performance gains in a data warehouse database
application when using columnstore indexes on the largest tables. Three tests were performed.
• Baseline tests, with no columnstore index on the eight partitions file layout (refer to Figure
6Figure 6).
• Implementing columnstore index on the first largest partitioned table (LineItem).
• Implementing Columnstore index on the largest partitioned table (LineItem) and the second
largest non-partitioned table (Order). This test was done to demonstrate the columnstore index
usage on both kinds of tables (partitioned and non-partitioned tables).
Note: When creating columnstore indexes on a partitioned table, changes to the table partitioning
syntax is not required. A columnstore index on a partitioned table must be partition-aligned with the
base table. Therefore a non-clustered columnstore index can only be created on a partitioned table if
the partitioning column is one of the columns in the columnstore index.
Columnstore index studies test parameters Table 5
Configuration parameters
EqualLogic SAN One PS6110X (2.5", 24 10 K SAS drives,900 GB)
RAID type
RAID 50
Columnstore index tests- volume layout
Baseline (no columnstore
index & changed SQL Max
memory to 115 GB)
LineItem table
• Eight partitions
•
Primary data File (.mdf) – 100GB
• LineItem table partition1 & non-clustered indexes (.ndf) – 1 TB
• LineItem table partition2 (.ndf)-200 GB
• LineItem table partition3 (.ndf)-200 GB
• LineItem table partition4 (.ndf)-200 GB
• LineItem table partition5 (.ndf)-200 GB
• LineItem table partition6 (.ndf)-200 GB
• LineItem table partition7 (.ndf)-200 GB
• LineItem table partition8 (.ndf)-200 GB
• Order table & non-clustered indexes (.ndf)-300 GB
• All other tables & non-clustered indexes (.ndf)–200 GB
• Log file (.ldf)-100 GB
•
Tempdb-600 GB