Writers: Emily Wilson, Mike Ruthruff, Thomas Kejser

Contributor: Juergen Thomas

Technical Reviewers: Sanjay Mishra, Prem Mehra, Lindsey Allen, Kevin Farlee, Ewan Fairweather, Ryan Stonecipher, Alexei Khalyako, Mike Weiner, Michael Thomassy, Wanda He, Kun Cheng, Jimmy May

Published: April 2010

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.

Sizing storage systems for SQL Server can be challenging because I/O characteristics differ significantly between applications depending on the nature of the access patterns. The techniques in this paper will give you the tools you need to monitor and characterize I/O behavior of your SQL Server application as well as understand how this maps into a physical storage configuration. These techniques will provide a more in-depth understanding of common I/O patterns for SQL Server applications. This may be especially useful for ISVs or others who can accurately define their workload to characterize I/O within lab environments and use the information to provide more concrete deployment guidance to their customers.

For more information please refer to this whitepaper: Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications