White Papers

6 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
1 Introduction
DSS solutions require scalable storage platforms that offer high levels of performance and capacity. This
paper presents sizing guidelines and best practices for deploying DSS solutions based on the results of
SQL Server 2012 I/O performance tests conducted using Dell™ PowerEdge™ servers, Dell™ Force 10™
switches and EqualLogic storage. The EqualLogic PS Series array builds on a unique peer-storage
architecture that is designed to provide the ability to distribute the load across multiple arrays and provide
a SAN solution that scales as organizations grow. This pay as you grow model, allows customers to add
arrays as their business demands increase the need for more storage or I/O capacity. Deploying the
storage platform using validated best practices and recommended settings for various system
components (storage, server, switch, operating system, and application) ensures optimal performance.
1.1 Objective
This paper identifies best practices and sizing guidelines for deploying SQL Server DSS applications with
EqualLogic storage arrays. It also illustrates that the virtualized EqualLogic PS Series storage arrays scale-
out proportionally providing a flexible and modular infrastructure for today’s increasingly complex
datacenters.
The following two major sections were analyzed during the test studies for this paper.
• Baseline tests: I/O profiling tests using IOMeter were executed to establish baseline I/O
performance characteristics of the test storage configuration when running DSS-like I/O
patterns before deploying databases.
• Database tests: Performance tests were executed by simulating SQL DSS queries using TPC-H
benchmark from the Benchmark Factory
®
for Databases tool.
The test objectives determined:
• Baseline performance that could be achieved from a PS6110X on RAID 50 using IOMeter.
• DSS application performance studies:
- SQL Server tuning parameters.
- Impact of table partition on query performance.
- Impact of columnstore Index on query performance.
- Scalability of the storage arrays I/O performance with a DSS application simulation as
storage arrays were added and while ensuring that the overall configuration was balanced
with no resource bottlenecks on the server.
1.1.1 Audience
This white paper is primarily targeted to solution architects, database administrators, database managers
and storage administrators, who are interested in using Dell EqualLogic storage to design, properly size
and deploy Microsoft SQL Server 2012 running on the Windows Server
®
2012 platform. It is assumed that
the reader has an operational knowledge of the Microsoft SQL Server configuration and management of
EqualLogic SANs and iSCSI SAN network design.