White Papers
21 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
Table partition test parameters Table 3
Configuration parameters
EqualLogic SAN
One PS6110X (2.5", 24 10 K SAS drives,900 GB)
RAID type
RAID 50
Table partition pests- volume layout
Four partitions
(LineItem table, partitioned into
four 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
• 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
Eight partitions
(LineItem table, partitioned into
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
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
117.7 GB
SQL Server parameters
-E, -T1117 and lock pages in memory
CPU 4* Intel
®
Xeon
®
Processor E5-4620 @2.20 GHz,8 Cores per
socket