Best Practices and Sizing Guidelines for Transaction Processing Applications with Microsoft SQL Server 2012 using EqualLogic PS Series Storage A Dell EqualLogic Best Practices Technical White Paper Dell Storage Engineering March 2013
Revisions Date Description March 13, 2013 Initial release March 18, 2013 Corrected links © 2013 Dell Inc. All Rights Reserved. Dell, the Dell logo, and other Dell names and marks are trademarks of Dell Inc. in the US and worldwide. All other trademarks mentioned herein are the property of their respective owners.
Table of contents Acknowledgements .......................................................................................................................................................................... 5 Feedback ............................................................................................................................................................................................ 5 Executive summary ......................................................................................
7.4.1 Database volume creation .......................................................................................................................................... 39 7.4.2 Buffer cache size .......................................................................................................................................................... 40 7.4.3 Table Partition .......................................................................................................................................
Acknowledgements This best practice white paper was produced by the following members of the Dell Storage team: Engineering: Lakshmi Devi Subramanian Technical Marketing: Magi Kapoor Editing: Camille Daily Additional contributors: Ananda Sankaran, Mike Kosacek, Darren Miller, Rob Young, and Maggie Smith Feedback We encourage readers of this publication to provide feedback on the quality and usefulness of this information by sending an email to SISfeedback@Dell.com. SISfeedback@Dell.
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.
1 Introduction This white paper presents the results of SQL Server I/O performance tests conducted on EqualLogic iSCSI SANs. It also provides sizing guidelines and best practices for running SQL OLTP workloads. The EqualLogic PS Series array builds on a unique peer-storage architecture that is designed to provide the ability to spread the load across multiple array members and provide a SAN solution that scales with customer needs.
platform. It is assumed that the reader has an operational knowledge of Microsoft SQL Server configuration and management of EqualLogic SANs and iSCSI SAN network design, and familiarity with VMware ESXi Server environments. 1.2 Terminology The following terms are used throughout this document. Group: One or more EqualLogic PS Series arrays connected to an IP network that work together to provide SAN resources to host servers.
and stores it on the server for later retrieval and analysis. Client systems connect to the server to format and display the data in the SAN HQ GUI. Secondary data file (ndf): Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive.
2 Product overview 2.1 Dell EqualLogic PS6100 Series The EqualLogic PS6100 Series array serves as the storage foundation for the virtualized datacenter supporting critical applications such as databases, email, and virtual server workloads. With the same virtualized scale-out architecture as previous product generations, the PS6100 Series increases raw capacity, adds density, and boosts IOPS performance. PS6100 Series arrays include 2.5 or 3.
3 Database application workloads Different types of database applications have varying needs and understanding the models for the most common database application workloads can be useful in predicting the possible application behavior. The most common database application workload models are Online Transaction Processing (OLTP) and Data warehouse (DW). This paper focuses on OLTP database workloads. 3.
SQL Server reads: SQL Server performs two types of reads; logical and physical. Logical reads occur when the Database Engine requests a page from the buffer cache. Physical reads, on the other hand, occur when the page is not currently in the buffer cache and the database engine would retrieve the data from the I/O storage subsystem. These first copies are taken from the disk into the cache. SQL Server writes: There are also logical and physical writes.
4 Test configuration The SQL Server test system used to conduct testing for this paper is shown in Figure 1 and Figure 2. 4.1 Physical system configuration The physical connectivity of the SQL Server that hosted the Databases used for testing is shown in Figure 1.
4.2 High-level system design A high-level overview of the dell infrastructure components used for the test configuration is shown in Figure 2. Figure 2 High-level overview of test configuration Key design details of the test system configuration shown in Figure 2 include: • Three R710s and one R820 Dell™ PowerEdge™ servers were used to host eight SQL Server VMs. Each of these VMs (SQL DB) had SQL Server 2012 Enterprise Edition installed on Windows Server 2008 R2 SP1 Enterprise Edition.
Figure 3 vSwitch0 LAN Configuration for ESXi 01 Host 3. A separate vSwitch (vSwitch1) was created for iSCSI SAN connectivity. 4. Virtual network adapters (type VMXNET 3) within the VM were created and were assigned to the vSwitch1 (refer to Figure 4) on the vSphere host. EQL MPIO DSM was used via Host Integration Tools (HIT) Kit to setup multiple paths from the guest VM to the storage volumes. These paths are labeled “Guest iSCSI Path” in Figure 2.
• ESXi 5 was installed on the servers that were used to deploy the SQL Server databases. The server disks were configured as RAID 5, and the guest virtual machine OS disk partitions were also hosted within the virtual machine file system on the disks. • The INFRA server marked in Figure 2 had VMware ESXi 5 installed and hosted virtual machines for vCenter and Active Directory.
5 I/O profiling using IOMeter I/O profiling tests were executed using IOMeter, to establish baseline I/O performance characteristics of the test storage configuration before deploying any databases. Refer to Table 1 for the workload parameters used for this testing. The baseline IOPS numbers were established by simulating: • Different I/O block sizes • Different RAID policies on the EqualLogic PS Series array • Scaling the EqualLogic PS Series arrays from one to three 5.
The results collected from the tests are illustrated in Figures 5, 6, and 7. Even though 100% random read is not a typical OLTP database I/O pattern, this test was conducted to measure maximum small random read only IOPS achieved by the storage system. A mix of 70% read and 30% write random 8 K I/O represents a majority of OLTP database workloads. However, some OLTP workloads vary that use larger block sizes.
The test results graphed in Figure 6 show the data in the volumes on a PS6100XV array with RAID 10 consumed almost 62% of the available capacity. These volumes also produced approximately 4,278 IOPS for a workload with 8 K block size and 70/30 read/write mix while staying within the generally accepted disk latency limit of 20 ms (for both read and write IOPS measured separately).
Figure 8 below compares the I/O performance of a specific 70/30 read/write mix for an 8 K block size across different array capacity utilization levels. Figure 8 IOPs comparison for different array capacity utilizations IOPS decrease when the PS6100XV array is almost 85% full, compared to 40% full because of data stored across the inner tracks/sectors of the disk drive as well as the outer sectors. The seek time, or I/O latency, for those blocks becomes higher resulting in latency exceeding 20 ms.
5.2 RAID studies This test compared the IOPS values while implementing different RAID policies on the EqualLogic PS6100XV array by running a single workload. The configuration parameters for the test are shown in Table 2. Table 2 Test parameters: RAID policies variation Configuration Parameters EqualLogic SAN One PS6100XV (2.
Figure 9 RAID comparisons The results displayed in Figure 9 confirm a higher IOPS performance in RAID 10 compared to RAID 50 and 6 for a random workload. RAID 10 offered approximately 41% more IOPS compared to RAID 50 and 48% more IOPS compared to RAID 6 for a 70/30 read/write mix with 8 K block size. This is due to the performance cost of the write penalty posed by the distributed parity in RAID 50 and dual parity in RAID 6.
The results collected from the SAN scaling studies using IOMeter are reported in Figure 10. The number of outstanding I/Os per target was increased in IOMeter to push more load to the arrays, as the second and third PS6100XV arrays were added. The IOPS numbers maintained the generally accepted disk latency limit of 20 ms (for both read and write latencies measured separately) for random workload. Figure 10 SAN scalability study As expected, IOPS scaled linearly with the addition of more arrays.
6 OLTP performance studies using TPC-E like workload A series of OLTP application simulations were conducted using Benchmark Factory as the simulation tool running a TPC-E type workload. TPC-E is an industry standard benchmark for OLTP. OLTP like user transactions were run from Benchmark Factory to understand the I/O behavior at the storage arrays when the SQL Server database executed those queries. The test criteria used for the study was: • • • • • 6.
A single database was used for this test and the configuration parameters were set as shown in Table 4. Table 4 Test parameters: Database file and volume layout studies Configuration Parameters EqualLogic SAN One PS6100XV (2.
For the OLTP tests with TPC-E type workload, the impact of change in multiple data files on the following parameters was measured. • IOPS • User Transactions per second (TPS) • Data/log volume storage latencies 6.1.1 IOPs, TPS and data/log volume latencies Figure 11 shows the IOPs and user TPS for the multiple database data files test. The read/write mix was about 70/30% and a single database was deployed in an array.
Figure 12 shows the data volume read/write latencies and log volume write latency. These were measured using Perfmon for the volumes appearing as drive letters at the SQL Server. As shown in Figure 12, similar data read/write latencies and log latencies were achieved for the tests where the database data was spread across three, four, five, and six files and volumes with a 1:1 ratio rather than having all the data in a single file and volume.
6.2 Table partitioning studies Partitioning breaks up database objects by allowing subsets of data to reside on separate file groups. This can be beneficial in several ways. • Improved scalability and manageability - When tables and indexes become very large, partitioning can help by dividing the data into smaller, more manageable sections.
Figure 13 Four and Eight Table Partition data file layout For this test the configuration parameters were set as shown in Table 5.
Table 5 Test parameters: Table partition studies Configuration Parameters EqualLogic SAN One PS6100XV (2.5", 24 15 K SAS drives,146 GB) RAID Policy RAID 10 Multiple SQL DB data files and volume Configuration Four partitions (one .mdf and five .ndf files) • • • • Eight partitions (one .mdf and nine .ndf files) • • • • One 51 GB volume with one .mdf file (primary file group) One 116 GB volume with one .
6.2.1 Four Partitions Figure 14 below shows the results of partitioning the E_Trade table into four partitions compared to spreading the data into multiple files without partitioning (as seen in section 6.1). There were six data files (one .mdf, and five .ndf files) in both the tests and each file was placed in its own volume. User transactions with constant user load of four concurrent users were run from Benchmark Factory to compare the results.
Figure 15 Average I/O rate on each data volume comparison Figure 16 Average I/O rate on each data volume -SANHQ BP1032 | Best Practices and Sizing Guidelines for Transaction Processing Applications with Microsoft SQL Server 2012 using EqualLogic PS Series Storage
The SANHQ results for the I/O rate on each data volumes are shown in Figure 16.The highlighted volumes contain table partitions. Figure 15 shows the comparison of the average I/O write rate for the database with table partition and the database without table partition.
Figure 18 34 Average I/O rate comparison- four versus eight partitions BP1032 | Best Practices and Sizing Guidelines for Transaction Processing Applications with Microsoft SQL Server 2012 using EqualLogic PS Series Storage
Figure 19 Average I/O rate on each volume for eight partitions With eight partitions, the write activity on these partitioned volumes (one-four) was much less compared to four partitions. Also the combination of the workload characteristics and the dataset used caused diminishing returns. 6.3 SAN scaling The goal of the SAN scaling test was to measure how I/O performance scales as the number of EqualLogic PS Series storage arrays (members) were increased within a group.
The user load was run from each of the four Benchmark Factory consoles for one array scaling and eight Benchmark Factory consoles for the two array scaling. The volume layout used for the test configuration is shown in Figure 20 and the other configuration parameters for the test are listed in Table 6. Figure 20 Table 6 Volume layout for the SAN scaling studies Test parameters: SAN scaling Configuration Parameters EqualLogic SAN Two PS6100XV (2.
The results collected from the SAN scaling studies using Benchmark Factory are graphed in Figure 21. With one array the capacity utilization was at 85% and with two arrays, the capacity utilization was about 82%. For this test, the read/write ratio was about 70/30 and the IOPS numbers maintained the generally accepted disk latency limit of 20 ms (both read and write latencies measured separately) for random workload, and 5 ms for the sequential log write latency.
7 Best practice recommendations 7.1 Storage Use the following best practices when configuring Dell EqualLogic storage arrays for a data protection solution. • When possible, use high performance drives for the arrays that host the SQL Server database volumes. For the OLTP test configuration in this paper, 15 K RPM SAS drives were used on the PS6100XV arrays that hosted the database volumes. • For OLTP applications, RAID 10 offers the best performance in addition to high availability.
• On iSCSI SAN switches, spanning tree should be disabled on switch ports connecting to end devices like server and storage ports. The Portfast setting should be enabled in the switch configuration for these ports. Note: General recommendations for EqualLogic PS Series array network configuration and performance is provided in the document titled EqualLogic Configuration Guide at http://en.community.dell.com/dellgroups/dtcmedia/m/mediagallery/19852516/download.aspx. 7.
7.4.2 Buffer cache size SQL Server buffer cache highly optimizes the disk access. The more database pages found in the buffer cache, the more efficient SQL Server will be in responding to the queries which in turn improves application response times. The change in buffer cache sizes changes the logical I/O access pattern and in turn changes the physical I/O access pattern, and storage device’s utilization level.
be aligned with the partitions to yield the maximum benefits. It would be useful to monitor and understand the existing database setup and the query processing using a SQL Profiler, before planning on implementing the table partitions. Note: Reference MSDN SQL Server Development Center, “Partitioned Table and Index Strategies Using SQL Server 2008”: http://msdn.microsoft.com/en-us/library/dd578580.aspx 7.4.4 Files and file groups A database file is a physical allocation of space and can be primary (.
most preferred recovery model is FULL to minimize downtime and data loss. Microsoft’s recommendations for the transaction log are: • Place the log and the data files into separate volumes to avoid both random and sequential I/O going to the same volume. • Set the original size of the transaction log to a reasonable size to avoid constant activation of the auto grow feature, which creates new virtual files and stops logging activity as space is added.
A Configuration details This section contains an overview of the hardware configurations used throughout the testing described in this document. Table 7 Test configuration hardware components Test Configuration 43 Hardware Components SQL Server® (ESXi01) One PowerEdge R710 server running VMware ESXi v5, hosting 2 SQL Server database virtual machines BIOS Version: 6.1.0 ® ® 2 x Quad Core Intel Xeon E5520 Processors 2.
INFRA SERVER One (1) Dell PowerEdge R710 Server running VMware ESXi v4.1, hosting a two (2) Windows Server 2008 R2 virtual machines for Active Directory and vCenter: BIOS Version: 6.1.0 Quad Core Intel® Xeon® X5570 Processor 2.26 GHz 48 GB RAM,2 x 146GB 15K SAS internal disk drives Broadcom 5709c 1GbE quad-port NIC (LAN on motherboard) – firmware version 6.4.5 LOAD GEN SERVER One (1) Dell PowerEdge R710 Server running VMware ESXi v4.
B Table partition steps The first step is identifying the best candidates for partitioning. SQL Profiler is a tool that helps gather the necessary details needed to select a table to partition and its partitioning column. This section provides examples of getting the SQL Profiler trace and analyzing the trace data. Below are some of the guidelines that were followed for the tests performed in identifying the table for partitioning.
The results of the query are shown below. The highlighted row is the one with maximum number of reads (which was the E_Trade table) and was selected as a good candidate to use for performing the partition studies.
Additional resources Support.dell.com is focused on meeting your needs with proven services and support. DellTechCenter.com is an IT Community where you can connect with Dell Customers and Dell employees for the purpose of sharing knowledge, best practices, and information about Dell products and your installations. Referenced or recommended Dell publications: • Dell EqualLogic Configuration Guide: http://en.community.dell.com/dell-groups/dtcmedia/m/mediagallery/19852516/download.
This white paper is for informational purposes only. The content is provided as is, without express or implied warranties of any kind.
