Best Practices Dell EMC SC Series: Microsoft SQL Server Best Practices Abstract This document describes best practices when using Dell EMC™ SC Series arrays with Microsoft® SQL Server®.
Revisions Revisions Date Description October 2007 Initial release December 2007 Technical review June 2008 Technical review July 2010 Technical review June 2012 Technical review May 2014 Technical review; included some of the new features of SQL Server 2014 related to I/O July 2014 Technical review; updated Storage Validation and guidance on SQLIO March 2016 Added content for in-memory, new storage profiles, and disk types July 2016 Technical review for SQL Server 2016 June 2019 Techni
Table of contents Table of contents Revisions.............................................................................................................................................................................2 Acknowledgements .............................................................................................................................................................2 Table of contents .................................................................................................
Table of contents 4.3.5 SSD considerations ..........................................................................................................................................15 4.4 5 6 7 SC Series snapshots ........................................................................................................................................15 Windows setup and configuration ...........................................................................................................................
Executive summary Executive summary This document describes the best practices for running Microsoft® SQL Server® (versions 2008 and later) with Dell EMC™ SC Series arrays. SQL Server performance tuning and profiling are beyond the scope of this paper. Due to dependencies on application design and requirements, environments and their recommendations can differ greatly. Visit Microsoft SQL Server Documentation for in-depth information on tuning SQL Server databases.
SQL Server on SC Series arrays 1 SQL Server on SC Series arrays An SC Series array provides a flexible, easy-to-manage storage platform that helps meet the demanding uptime and performance requirements common in SQL Server environments. 1.1 Manageability SQL Server environments are often very dynamic. An SC Series array makes it easy to adapt the storage configuration to meet changing database requirements. Volumes can be created or expanded with very little effort.
Sizing for SQL Server 2 Sizing for SQL Server The I/O subsystem 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. Monitoring performance in an existing environment using a tool like Live Optics can help define the I/O requirements. For best results, capture performance statistics for a time period of at least 24 hours that includes the system peak workload. 2.
Sizing for SQL Server Write penalty by RAID type RAID type Write penalty I/O description RAID 10 2 2 writes RAID 10 DM 3 3 writes RAID 5 4 2 reads, 2 writes RAID 6 6 3 reads, 3 writes By default, writes to SC Series volumes use RAID 10. Since RAID 10 has the lowest write penalty, it is strongly recommended to always use RAID 10 for writes. All standard SC Series storage profiles write using RAID 10.
Sizing for SQL Server 2.5 OLAP/DSS workloads An online analytic processing (OLAP) or decision support system (DSS) workload is typically dominated by large, sequential reads. A storage system servicing this type of workload is primarily sized based on throughput. When designing for throughput, the performance of the entire path between the server and the disks in the SC Series array need to be considered.
Sizing for SQL Server 2.7.1 Traditional SSD configurations A single SSD type can be used as the high-performance tier in an SC Series array either alone (all-flash) or combined with spinning drives (hybrid). When using SSDs as a tier that participates in automated tiering, there needs to be enough drives in the tier to handle 100 percent of the IOPS and at least 30 percent of the capacity of the volumes that use the SSD tier.
Validating the storage design 3 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. Diskspd is a free Microsoft utility that can simulate I/O patterns generated by SQL Server. There are several other utilities available as well. When selecting a utility to simulate I/O, it should meet the following requirements: • • • • • • 3.
Validating the storage design • Some I/O test tools, including Diskspd, SQLIO, and IOMeter, generate files full of zeros. By default, SC Series arrays track pages full of zeroes in the metadata, but do not store them on a disk. This behavior, known as a thin write, causes inaccurate results when testing with files containing only zeros. Avoid using test utilities that write zeros for disk validation.
Storage setup and configuration 4 Storage setup and configuration 4.1 Single or multiple disk pools Dell EMC recommends using a single virtual disk pool when implementing SQL Server. This provides better performance by leveraging the aggregate I/O bandwidth of all disks to service I/O requests from SQL Server.
Storage setup and configuration requirements can be spread across two or more data files on separate volumes to leverage resources on both controllers. 4.2.2 Flexibility and manageability For ultimate flexibility, create a volume for each user database file. This provides the ability to independently optimize the storage and snapshot configuration for each individual database. With thin provisioning, there is no space penalty for creating a lot of volumes.
Storage setup and configuration 4.3.5 SSD considerations The type of files that benefit the most from SSDs varies from environment to environment. Monitor the performance of the database volumes to determine the best fit for SSDs. If there are not enough SSDs to function as tier 1 storage for all SC Series volumes, the Storage Profiles for Flash Optimized Storage can be used to control tiering. 4.
Storage setup and configuration In environments where other Dell EMC array models exist, Dell EMC AppSync™ can also be used for taking SQL Server database snapshots on SC Series arrays, as well as several other Dell EMC arrays. For more information about using snapshots with SQL Server using Replay Manager, see the Replay Manager Administrator's Guide. A wealth of resources on Dell EMC AppSync can be found in the Dell EMC AppSync site.
Windows setup and configuration 5 Windows setup and configuration 5.1 Allocation unit size Use a 64 KB allocation unit size when formatting volumes that will contain database files (transaction log and data) or database backups. 5.2 MPIO Set the MPIO policy to Round Robin for all database volumes. This is the default for Windows Server 2008 and newer. It allows all paths to be used, enabling higher throughput between the server and the array.
Server setup and configuration 6 Server setup and configuration 6.1 HBA considerations Be sure that the HBA firmware and drivers are up to date. As with any update, it is important to verify functionality in a test or QA environment before implementing in production. SQL Server is a very I/O intensive application. It is important to configure the queue depth of the HBA properly to optimize performance. See the "HBA Server Settings" section in the Dell Storage Manager Administrator’s Guide. 6.
Optimizing SQL Server I/O 7 Optimizing SQL Server I/O 7.1 Memory Unnecessary I/O can be avoided and performance can be increased by allocating the proper amount of memory to SQL Server. SQL Server performs all I/O through the buffer pool (cache) and therefore uses a large portion of its memory allocation for the buffer pool. Ideally, when SQL Server performs I/O, the data is already in the buffer pool and it does not need to go to disk.
Optimizing SQL Server I/O SQL Server data pages are compressed in memory before being written to disk resulting in fewer pages needed to store the same number of rows and therefore less I/O. 7.5 Instant file initialization By default, SQL Server writes zeros to the data file during the allocation process. The process of zeroing out the data files consumes I/O and acquires locks as the SQL Server data pages are written. This activity can occur for minutes or even hours depending on the file size.
Optimizing SQL Server I/O 7.7.1 Database design The foundation of the entire database and the schema for how data will be stored and ultimately accessed is determined by the database design. The database design should support both usability and efficient data access. This includes efficient table design and data types as well as indexes, partitioning, and other features that can improve efficiency. It is common for database design to only be focused on usability. 7.7.
Additional resources A Additional resources A.1 Technical support and resources Dell.com/support is focused on meeting customer needs with proven services and support. Storage technical documents and videos provide expertise that helps to ensure customer success on Dell EMC storage platforms. A.1 SQL Server resources There is a tremendous amount of SQL Server information available online. While not a complete list, the table below contains several useful links.
