This document has been archived and will no longer be maintained or updated. For more information go to the Storage Solutions Technical Documents page on Dell TechCenter or contact support. Dell EqualLogic Best Practices Series Deploying Microsoft SQL Server 2008 based Decision Support Systems using Dell EqualLogic 10GbE iSCSI Storage A Dell Technical Whitepaper This document has been archived and will no longer be maintained or updated. For a full list of papers go to http://en.community.dell.
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND. © 2010 Dell Inc. All rights reserved. Reproduction of this material in any manner whatsoever without the express written permission of Dell Inc. is strictly forbidden. For more information, contact Dell.
Table of Contents 1 2 Introduction ............................................................................................................................................................ 1 1.1 Audience.......................................................................................................................................................... 1 1.2 The Rest of This Paper......................................................................................................................
.5.3 5 Use SQL Server Table Partitioning to Improve Query Processing Performance ................. 18 Test Results ...........................................................................................................................................................19 Appendix A Dell PowerEdge M1000e high-speed fabric architecture .................................................... 22 Appendix B System Specifications ...............................................................................
1 Introduction The goal of this paper is to describe best practices for deploying Decision Support System (DSS) or Data Warehousing (DW) solutions based on Microsoft® SQL Server® 2008 using Dell™ EqualLogic™ storage. DSS solutions require scalable storage platforms that offer high levels of performance and capacity.
2 Understanding DSS Storage Requirements DSS applications are typically designed to support complex analytical query activities using very large data sets. The queries executed on a DSS database typically take a long time to complete and usually require processing large amounts of data. A DSS query may fetch millions of records from the database for processing. To support these queries the server reads table data from the storage devices.
Each of the component layers in the solution stack requires careful design and configuration to ensure optimal system performance. The considerations at each layer of the solution stack are described as follows: Storage The storage arrays must be configured to support capacity and performance requirements. Considerations include: array controller capabilities, I/O port specifications, RAID policies, hard drive types and spindle speeds.
Note: To set up the iSCSI SAN in our test configuration we created a 10 gigabit Ethernet connection fabric using Dell PowerConnect 8024F 10GbE switches and Dell EqualLogic PS6010XV storage arrays. The arrays were configured with redundant multi-port 10GbE controllers. For more information about the Dell PowerConnect 8024F switch: http://www.dell.com/downloads/global/products/pwcnt/en/switch-powerconnect8024f-spec.pdf For more information about the Dell EqualLogic PS6010XV storage array: http://www.
Figure 2: Test Configuration #1 Deploying Microsoft SQL Server 2008 based Decision Support Systems using Dell EqualLogic 10GbE iSCSI Storage 5
The second test configuration, as shown in Figure 3, includes three PS6010XV arrays for SQL Server® 2008 data and one PS6010XV array for SQL Server® logs. The Storage arrays are connected to a Dell PowerEdge M710 blade server. Figure 3: Test Configuration #2 Once again, we chose the PowerEdge M710 server to take advantage of its expanded I/O capability.
Please note that in both test configurations we implemented hardware redundancy at various levels to ensure high availability (HA). The HA configuration uses dual network cards on the servers, dual network switches and dual storage controllers to create redundant connection paths, thus eliminating single points of failure across the SAN fabric.
3.1.2 Monitoring We configured Windows Server® 2008 Performance Monitor (PerfMon) to monitor the utilization of server resources – processor, memory, disk I/O and network. We also used the Dell EqualLogic SAN HeadQuarters (“SAN HQ”) application to monitor the utilization and operation of the storage resources: physical disks, logical volumes, network ports, member arrays and storage pool activity.
Since DSS environments are highly read intensive and require large storage capacities, it is a best practice to use RAID50 with EqualLogic PS Series storage arrays. RAID 10 is also recommended for environments that include a mix of both read and write operations. For more information on setting RAID policies see Table 1: Matching RAID levels to DSS I/O componentsTable 1, in Section 4.5.2. 4.1.
Figure 4 does not show the separate switching infrastructure used for the Server LAN. It is a best practice to always isolate the SAN so that the iSCSI switching infrastructure is dedicated to storage traffic only. Figure 4: Redundant Connection Paths for High Availability 4.2.2 Use Flow Control and Jumbo Frames Flow control increases the ability of the SAN to regulate packet flow between nodes (server, switches, and storage) during high I/O traffic conditions.
Jumbo frames are Ethernet frames that have a MTU larger than 1518 bytes. Jumbo frames can increase SAN throughput by allowing more data to be transferred in each Ethernet frame. This will reduce the time it takes to transfer large amounts of data across the SAN. In our testing we enabled flow control (TX and RX) and used jumbo frames with a MTU setting of 9216 bytes on the switch ports.
It is a best practice to use the Dell SQL Server Advisor Tool3 as a starting point for sizing server configurations to support SQL Server DSS workloads. 4.3.2 Think beyond Just CPU and Memory In our test configurations we installed two Broadcom mezzanine NICs on the PowerEdge M710 database server for connecting to the external iSCSI storage via Fabric B. Each NIC is connected to the two blade CPU sockets internally through an eight-lane (x8) PCIe interface as shown in Figure 5.
running Quest Benchmark Factory for Databases and the monitoring system running SAN HQ to the database server via the server LAN. It is a best practice to provision the quantity and connection speed of the SAN NIC ports on your server based on the throughput requirements of the workload. 4.4 Operating System Configuration You should consider the following best practices for operating system settings in a DSS solution. 4.4.
TCP offload is disabled by default in Windows Server® 2008 and on the NIC. You will need to make sure it is properly enabled in both locations. To configure ToE enablement in the Operating system, run the following commands in the administrator mode via a Windows® command prompt.
To configure OS TCP offload: C:\>netsh int tcp set global chimney=enabled To verify OS TCP offload: C:\>netsh int tcp show global Note: More information on enabling TCP offload in the operating system can be found in the following Microsoft Knowledgebase Articles: Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008: http://support.microsoft.
Note: Configuring EqualLogic DSM for MPIO provides enhanced performance benefits for bandwidth intensive applications like SQL Server® DSS. The DSM also enables easier configuration by automating setup of the iSCSI sessions (connections) to the target volumes. MPIO settings can be specified using the “Remote Setup Wizard” program that is installed by the HIT Kit. The wizard offers settings for load-balancing policies such as “Least Queue Depth”, “Round Robin” and “Fail-Over”.
be chosen for I/O (instead of the “Round Robin” approach, which would choose the next path in the sequence regardless of queue depth). Figure 7: MPIO Settings 4.5 Microsoft SQL Server 2008 You should consider the following best practice guidelines for configuring SQL Server®. 4.5.1 Properly Size the Storage Volumes for Each Database I/O Component. SQL Server® data layout on storage volumes is an important factor for optimal operation of DSS workloads.
Note: For more information on tempdb sizing and best practices, see the following Microsoft Knowledgebase articles: 4.5.2 Capacity Planning for tempdb: http://msdn.microsoft.com/enus/library/ms345368.aspx Optimizing tempdb Performance: http://msdn.microsoft.com/enus/library/ms175527.aspx Troubleshooting Insufficient Disk Space in tempdb: http://msdn.microsoft.com/enus/library/ms176029.
large tables and when based on the most commonly queried table field. We also recommend as a best practice that you align the indices with the same partitioning scheme used for table data. 5 Test Results A PS6010XV array can deliver up to 2.5 times the throughput (megabytes per second) of a PS6000XV array8. However, every DSS implementation is different. Actual throughput may vary depending on database size and workload characteristics.
The maximum throughput observed at the server NICs scaled almost linearly from the single array configuration to the three array configuration. This was due to the increased storage bandwidth available (three times the number of storage array 10GbE controller ports) and the increase in available storage resources (three times the number of storage controllers and disk drives). The peak throughput rates occurred when multiple queries were fetching very large data sets simultaneously.
Figure 10: Total Query Response Time Deploying Microsoft SQL Server 2008 based Decision Support Systems using Dell EqualLogic 10GbE iSCSI Storage 21
Appendix A Dell PowerEdge M1000e high-speed fabric architecture We used a Dell PowerEdge M1000e blade server enclosure for our test configurations.Figure 11 shows the interconnect architecture. For more information, see Exploring the Dell PowerEdge M1000e Network Fabric Architecture, available at: http://content.dell.com/us/en/enterprise/d/business~solutions~whitepapers~en/Documents~explorin g-the-dell-poweredge.pdf.
Appendix B System Specifications Table 2 shows the configuration of the DSS server used in our test configurations. Dell PowerEdge M710 Configuration9 Component Description Processors 2 x Intel Xeon X5570, 2.93Ghz, 8M Cache, 6.40 GT/s QPI, Turbo, HT Memory 32GB Memory (8x4GB), 1066MHz Dual Ranked RDIMMs for 2 Processors BIOS 1.2.
Table 3 shows the configuration of the storage array and switch used in our test configurations. Dell EqualLogic PS6010XV Configuration Storage Controllers Dual 10GbE controllers with 4GB battery‐backed memory Controller Firmware 4.3.2 Hard Disk Drives 16 x SAS hot‐pluggable disk drives; 15,000 RPM Hard Drive Capacities 450GB each RAID RAID50 SAN Headquarters V 2.0 Dell PowerConnect 8024F SAN Switch Firmware 3.1.1.
Related Publications The following Dell publications are referenced in this document or are recommended sources for additional information. Dell EqualLogic PS Series Network Performance Guidelines http://www.equallogic.com/resourcecenter/assetview.aspx?id=5229 10 Steps to Deploying a High-Performance Fault-Tolerant SAN Using Dell EqualLogic PS Series Storage and Cisco Catalyst Network Infrastructure: http://www.equallogic.com/resourcecenter/assetview.
http://support.microsoft.com/kb/951037 The Microsoft Windows Server 2003 Scalable Networking Pack release: http://support.microsoft.com/kb/912222 Microsoft iSCSI Initiator Version 2.x Users Guide: http://download.microsoft.com/download/a/e/9/ae91dea1-66d9-417c-ade492d824b871af/uguide.doc Capacity Planning for tempdb: http://msdn.microsoft.com/en-us/library/ms345368.aspx Optimizing tempdb Performance: http://msdn.microsoft.com/en-us/library/ms175527.
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND.
