White Papers

3 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
Table of contents
Revisions ............................................................................................................................................................................................. 2
Acknowledgements .......................................................................................................................................................................... 5
Feedback ............................................................................................................................................................................................ 5
Executive summary .......................................................................................................................................................................... 5
1 Introduction ................................................................................................................................................................................ 6
1.1 Objective........................................................................................................................................................................... 6
1.1.1 Audience ........................................................................................................................................................................... 6
1.2 Terminology ..................................................................................................................................................................... 7
2 Dell EqualLogic PS6110 series product overview ................................................................................................................ 9
3 Nature of data warehouse workloads and storage ........................................................................................................... 10
4 Test configuration .................................................................................................................................................................... 11
4.1 Physical system configuration- SQL Servers ............................................................................................................ 11
4.2 High-level system design ............................................................................................................................................ 12
5 Baseline I/O profiling using IOMeter .................................................................................................................................... 13
6 DSS performance studies using TPC-H Benchmark Factory .......................................................................................... 15
6.1 SQL Server startup parameters test studies ............................................................................................................. 15
6.2 Table partitioning studies ............................................................................................................................................ 18
6.2.1 Identifying candidates for table partitioning ............................................................................................................ 18
6.2.2 Number of partitions and volumes ............................................................................................................................ 19
6.3 Columnstore index studies ......................................................................................................................................... 25
6.3.1 Understanding columnstore index ............................................................................................................................ 25
6.3.2 Columnstore index benefits ........................................................................................................................................ 27
6.3.3 Limitations of using columnstore index.................................................................................................................... 27
6.3.4 Columnstore index performance comparisons - TPC-H...................................................................................... 28
6.4 SAN scaling ..................................................................................................................................................................... 36
6.4.1 Performance comparaison - IOMeter versus TPC-H............................................................................................. 36
6.4.2 Scaling only the arrays with constant user load – TPC-H .................................................................................... 37
6.4.3 Scaling the arrays and user load- TPC-H ................................................................................................................ 40
7 Best practices recommendations ......................................................................................................................................... 44
7.1 Storage ............................................................................................................................................................................ 44
7.2 Network Infrastructure ................................................................................................................................................. 44