Published: June 5, 2007

Writer: Mike Ruthruff

Contributors: Michael Thomassy, Prem Mehra

Technical Reviewers: Robert Dorr, Stuart Ozer, Juergen Thomas, Lindsey Allen, Burzin Patel, Bill Emmert, Bruce Worthington, Sanjay Mishra

One of the key components that affect the performance of Microsoft® SQL Server™ is the I/O subsystem. 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 paper provides guidance for validating and determining the capacity of an I/O subsystem. A number of tools are available that can be used to perform this type of testing. The focus of this paper is on the SQLIO.exe tool. However, we will contrast and compare all of the available tools. Much of the information in this white paper is in the document that is included with the download of the SQLIO tool; however, this white paper includes updates to existing information and additional information, including basic I/O configuration best practices as related to SQL Server based on recent learnings.

Included in this paper:

• Overview
• Determine I/O Capacity
• Disk Configuration Best Practices & Common Pitfalls
• SQLIO
• Monitoring I/O Performance Using System Monitor
• Conclusion
• Resources

Link to the complete whitepaper: Predeployment I/O Best Practices.

More Resources:

• SQLIO Disk Subsystem Benchmark Tool (Microsoft Download Center)
• How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem (Knowledge Base article)
• IOMeter (IOMeter site)
• SQL Server AlwaysOn Partner Program (Microsoft SQL Server site)
• SQL Server Urban Legends (Product Services and Support (PSS) Engineers blog)
• SQL Server 2000 I/O Basics 
• SQL Server I/O Basics, Chapter 2
• Disk Subsystem Performance Analysis for Windows (Windows Hardware Developer Center)
• Storport in Windows Server 2003: Improving Manageability and Performance in Hardware RAID and Storage Area Networks (Windows Storage Server white paper)