SQL Server Technical Article

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

Applies to: SQL Server 2008, SQL Server 2008 R2, SQL Server 2005

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.

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.

This paper is meant not to serve as prescriptive capacity planning guidance for SQL Server but rather to provide an initial look at the tools and methodology for characterizing existing workloads and mapping those into physical storage requirements. This will be done using concrete examples to illustrate the application of the techniques.

 - Download “Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications” from the Microsoft Download Center

 - Ask a question in the SQL Server Forums

 - Send Feedback on the white paper