Proper I/O Capacity Planning and Configuration Resources for SQL Server

  • Article

To understand and perform proper IO Capacity Planning for SQL Server, you must understand the IO characteristics and data access patterns of the workload that will be sent to the SQL Server. Unless you are using a vendor or 3rd party product, the workloads will be specific to each customer, environment, and application making capacity planning a custom effort for each situation. When running multiple instances of SQL Server on one system or node, you must have this same understanding for each individual workload and then combine those resource requirements to see if the resources available can meet that demand.

Some Best Practice topics and items to consider with disk I/O:

SQL Server:

  • Have you separated IO and data access patterns by placing your data and log files on separate physical media? (Random I/O should be separated from Sequential I/O)
  • Do you need to separate large indexes and/or specific tables to separate disks?
  • Is data partitioning implemented where it makes sense? (do you have a set of data that is accessed more regularly on a field that can naturally partition data – or can it help with maintenance and moving of a large data set)
  • Is TEMPDB properly configured according to best practices using multiple, equally sized files based on the number of logical cores in the system?
  • Is the workload and database schema tuned to provide for efficient data load and access? (indexing & statistics, schema design, query design, data load strategy, etc… )
  • Are you using data and/or backup compression?
  • For very large databases (VLDB), do you have an effective backup strategy and DRP plan in place?

OS & Storage:

  • Have you thoroughly tested the I/O configuration with SQLIOSim and SQLIO? (SQLIOSim will allow you to test the integrity of the disk subsystem by writing data exactly as SQL Server does, whereas SQLIO will allow you to stress test the disk subsystem to measure for capacity and throughput such as HBA and cache saturation)
  • Are you using dedicated physical media and RAID groups for the database server? (dedicated spindles on the backend – not shared storage)
  • Are you using proper RAID level configurations that provide adequate read/write performance? And, is the storage cache configured appropriately for SQL Server?
  • Do all the disks have proper sector alignment on all SQL Server volumes? (for Windows 2003 – check for proper starting offset on volumes and correct block size at the storage level and the correct FAU size at the file system level)
  • Are all drivers and firmware for all SAN and other storage hardware up to date?
  • Is the HBA queue depth and other driver settings properly configured - and is the bandwidth adequate to provide the expected response time?

Below are some resources including best practice articles and whitepapers that will help you understand and implement the above:

Here are the *basic* top 10 storage best practices:

Storage Top 10 Best Practices
https://technet.microsoft.com/en-us/library/cc966534.aspx

For information on common problem areas and their symptoms:

Top SQL Server 2005 Performance Issues for OLTP Applications
https://technet.microsoft.com/en-us/library/cc966401.aspx

For the technical details and specifications on how SQL Server performs I/O:

SQL Server 2000 I/O Basics
Summary: Learn the I/O requirements for Microsoft SQL Server database file operations. This will help you increase system performance and avoid I/O environment errors.
https://technet.microsoft.com/en-us/library/cc966500.aspx

Physical Database Storage Design
Summary: This article provides a guide for physical storage design and gives recommendations and trade-offs for physical hardware design and file architecture.
https://technet.microsoft.com/en-us/library/cc966414.aspx

For details on tasks to perform and technical items to understand *before* deploying your application and SQL Server:

Pre-deployment I/O Best Practices: SQL Server Best Practices Article
Summary: The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.
https://technet.microsoft.com/en-us/library/cc966412.aspx

Understanding how to analyze I/O and size a storage system:

Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications
Summary: Understanding how to analyze the characteristics of I/O patterns in the Microsoft® SQL Server® data management software and how they relate to a physical storage configuration is useful in determining deployment requirements for any given workload. A well-performing I/O subsystem is a critical component of any SQL Server application. I/O subsystems should be sized in the same manner as other hardware components such as memory and CPU. As workloads increase it is common to increase the number of CPUs and increase the amount of memory. Increasing disk resources is often necessary to achieve the right performance, even if there is already enough capacity to hold the data.
https://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/Analyzing%20Characterizing%20and%20IO%20Size%20Considerations.docx

Capacity planning and best practices specific to Data Compression:

Data Compression: Strategy, Capacity Planning and Best Practices
Summary: The data compression feature in SQL Server 2008 helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.
https://msdn.microsoft.com/en-us/library/dd894051(SQL.100).aspx

Analyzing performance of the disks in Windows:

Disk Subsystem Performance Analysis for Windows
https://download.microsoft.com/download/e/b/a/eba1050f-a31d-436b-9281-92cdfeae4b45/subsys_perf.doc

Disk partition alignment best practices:

Disk Partition Alignment Best Practices for SQL Server
Summary: Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows.
https://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspx

Loading Data into SQL Server:

The Data Loading Performance Guide
Summary: This document described techniques for bulk loading large data sets into SQL Server. It covers both the available techniques as well as methodologies to performance tune and optimize the bulk loading process.
https://msdn.microsoft.com/en-us/library/dd425070(SQL.100).aspx

Microsoft’s recommendation when deploying SQL Server 2005 on a SAN:

Deploying SQL Server 2005 with SAN #1
https://blogs.msdn.com/b/sqlcat/archive/2005/10/11/479887.aspx?wa=wsignin1.0

SQL Server 2005 Configuration Blog #2
https://blogs.msdn.com/b/sqlcat/archive/2005/11/21/495440.aspx

Deploying SQL Server 2005 with SAN #3
https://blogs.msdn.com/b/sqlcat/archive/2005/11/17/493944.aspx

Proper configuration of SQL Server TEMPDB:

Concurrency enhancements for TEMDB
https://support.microsoft.com/kb/328551

Capacity Planning for TEMPDB
https://msdn.microsoft.com/en-us/library/ms345368(SQL.90).aspx

TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx

For maximum capacities in SQL Server:

Maximum Capacity Specifications for SQL Server
https://msdn.microsoft.com/en-us/library/ms143432.aspx

To download SQLIO and SQLIOSim:

SQLIO Disk Subsystem Benchmark Tool(SQLIO is a tool provided by Microsoft which can also be used to determine the I/O capacity of a given configuration.)
https://www.microsoft.com/downloads/details.aspx?familyid=9A8B005B-84E4-4F24-8D65-CB53442D9E19&displaylang=en

How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
https://support.microsoft.com/kb/231619

For a list of other best practices articles, refer to this link:

https://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

-Jay