White Papers

6 BP1032 | Best Practices and Sizing Guidelines for Transaction Processing Applications with Microsoft SQL Server 2012
using EqualLogic PS Series Storage
Executive summary
Online Transaction Processing (OLTP) applications such as enterprise resource planning (ERP), supply
chain management (SCM), and web-based e-commerce systems can benefit from a Dell™ EqualLogic™
storage solution. With its unique peer storage architecture, the EqualLogic PS Series array delivers high
performance and availability regardless of scale.
Systems such as large e-commerce websites that must respond to spikes in demand from a large number
of users and a high volume of transactions need to be designed appropriately. Therefore, it is essential to
configure a balanced end-to-end system to enable consistent user transactions without any delay or
bottlenecks during the peak loads for SQL Server OLTP database environments. The storage related
performance bottlenecks can only be prevented by properly sizing the storage for performance and
capacity and regularly monitoring the resource utilization.
This paper includes the results of a series of storage I/O performance tests and provides capacity planning
guidelines and best practices based on those results. These guidelines and best practices describe
designing and deploying transaction processing applications with Microsoft SQL Server 2012 using the
EqualLogic PS6100XV storage arrays.
Topics demonstrated in this paper are:
• EqualLogic PS Series arrays provided high levels of I/O performance for OLTP applications while still
maintaining the Microsoft recommended latencies.
• RAID 10 performed better by offering higher IOPS compared to RAID 50 and 6 for OLTP workloads.
• Adding EqualLogic PS Series arrays scaled capacity as well as I/O performance. The scale-out
architecture for all array resources, including controllers and NICs, scaled proportionately.
• Partitioning the largest and most accessed table offered better performance compared to just
spreading the database data files into multiple volumes without a table partition.
Optimal operation of an OLTP application can be achieved when the applicable best practices laid out in
this paper are adhered to. It must be ensured that the entire ecosystem including server, storage and
networking resources are sized and configured appropriately to meet the workload performance
requirements.