White Papers

29 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
Configuration parameters
Columnstore index on
LineItem table
LineItem table
• Eight partitions
• Columnstore index aligned
with the table partition
scheme
• 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
Columnstore index on
LineItem & Order tables
LineItem table
• Eight partitions
• Columnstore index aligned
with the table partition
scheme
Order table
• Columnstore index
•
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
Data warehouse workload parameters
Database load & workload
generation
TPC-H from Benchmark Factory
Database size
300 scale (~620 GB including data and indexes)
Number for users/streams 6 (As per TPC-H standard, the minimum required streams/users to be
run for a 300 scale database is 6)
Queries
22 TPC-H queries per user
SQL Server memory (Max
memory allocation) in GB
115 GB (refer Figure 10 & note below)
SQL Server parameters
-E, -T1117 and lock pages in memory
CPU
4* Intel® Xeon® Processor E5-4620 @2.20 GHz,8 cores per socket