Dell EMC PowerVault ME4 Series and Microsoft SQL Server Abstract This document provides best practices for deploying Microsoft® SQL Server® with Dell EMC™ PowerVault™ ME4 Series arrays, including recommendations and considerations for performance, availability, and scalability.
Revisions Revisions Date Description September 2018 Initial release October 2018 Added performance section Acknowledgements Author: Doug Bernhardt The information in this publication is provided “as is.” Dell Inc. makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose.
Table of contents Table of contents Revisions.............................................................................................................................................................................2 Acknowledgements .............................................................................................................................................................2 Table of contents .................................................................................................
Executive summary Executive summary This paper provides guidance for using Dell EMC™ PowerVault™ ME4 Series storage systems in a Microsoft® SQL Server® environment. SQL Server is a robust product that can be used in a variety of solutions, allowing you to prioritize performance, manageability, and flexibility depending on your environment.
Introduction 1 Introduction The PowerVault ME4 Series is next-generation, entry-level storage that is purpose-built and optimized for SAN and DAS virtualized workloads. Available in 2U or dense 5U base systems, the low-cost ME4 Series simplifies the challenges of server capacity expansion and small-scale SAN consolidation with up to 336 drives or 4PB capacity.
Best practices overview 2 Best practices overview Use the following general steps to set up and configure an ME4 Series system for SQL Server: 1. Capture the storage I/O performance characteristics and capacity requirements of your SQL Server workload. 2. Review the remaining sections of this document and apply the best practices that are applicable to your workload and environment. Since SQL Server workloads can vary, not all recommendations may apply. 3.
SQL Server design considerations 3 SQL Server design considerations The I/O storage system is a critical component of any SQL Server environment. Sizing and configuring a storage system without understanding the I/O requirements can have disastrous consequences. Analyzing performance in an existing environment using a tool like Live Optics can help define the I/O requirements. Your Dell EMC representative can assist with Live Optics data collection and analysis.
SQL Server design considerations There is a variety of other RAID levels and storage configurations available for very specific workloads. Make sure the design tradeoffs are completely understood when choosing custom configurations and settings. In many cases, modifying the storage configuration for existing volumes will involve halting I/O on those volumes, resulting in a SQL Server outage. For detailed information on all available choices, including ADAPT, consult the ME4 Series Administrator’s Guide. 3.
SQL Server design considerations Figure 1 compares the OLTP performance for each RAID type. Max OLTP IOPS by RAID Level 250,000 192,000 200,000 150,000 115,000 100,000 99,000 50,000 0 ADAPT RAID 5 RAID 10 Maximum OLTP IOPS by RAID level3 3.5 Validating the storage design Once the I/O requirements have been defined, it is easy to determine whether the hardware can provide the desired performance by running some simple tests.
SQL Server design considerations To validate the I/O path, run a large block sequential read test using the following guidelines: • • • • • Create one LUN per storage processor. Format the volumes using a 64 KB allocation unit. Use a block size of 512 KB for the test. Configure the test for 32 outstanding I/Os. Use multiple threads. Eight is the recommended starting point.
Deploying SQL Server on ME4 Series storage 4 Deploying SQL Server on ME4 Series storage Proper architecture and configuration of the SQL Server environment is critical to optimize performance and manageability of the ME4 Series array and SQL Server environment. Apply the following best practices when designing, configuring, and managing SQL Server databases on ME4 Series storage. 4.1 Volume configuration 4.1.1 Creating volumes There are many types of files that are part of a SQL Server instance.
Deploying SQL Server on ME4 Series storage 4.1.4.2 MPIO ME4 Series arrays support Asymmetric Logical Unit Access (ALUA), and when MPIO is configured, the default MPIO policy is round robin with subset. This is the recommended setting for all database volumes. This setting works best for most environments because it is easy to manage and performs very well. Use other MPIO policies with caution and remember to review custom MPIO policies when adding or removing volumes from the host. 4.
Deploying SQL Server on ME4 Series storage When Instant File Initialization is enabled, SQL Server will skip the process of zeroing out its data files when allocating space. Dell EMC recommends enabling Instant File Initialization. 4.2.5 Resource Governor The Resource Governor was added in SQL Server 2008 to allow database administrators to limit the CPU and memory resources that a query is able to consume. This feature was enhanced in SQL Server 2014 to allow I/O resources to be limited as well.
Deploying SQL Server on ME4 Series storage Frequent data modifications can also lead to fragmentation within SQL Server data files, producing unnecessary I/O. Fragmentation can be addressed through index reorganization or rebuilds as part of regular database maintenance. The database maintenance process itself can also have a large I/O impact. Typically, every table and index does not need to be rebuilt or reorganized every time maintenance is run.
Technical support and resources A Technical support and resources Dell.com/support is focused on meeting customer needs with proven services and support. Storage Solutions Technical Documents provide expertise that helps to ensure customer success on Dell EMC storage platforms. A.1 Related resources The following ME4 Series publications and additional resources are available at Dell.com/support.
