Chapter 2. Storage system sizing

Performance tuning SQL Server applications involves finding and addressing performance bottlenecks. While there will always be a bottleneck somewhere, the goal is to reduce the bottlenecks until application performance meets or exceeds the usage requirements, typically defined in a service level agreement (SLA).

Although it's undeniable that the largest performance gains usually come from good application design, inadequate hardware makes resolving performance problems much more difficult. Poorly designed storage systems account for arguably the largest percentage of hardware-based performance problems for SQL Server solutions, and fixing them is usually more complicated than a simple memory or CPU upgrade. It follows that a well-designed storage system removes the biggest hardware-based performance obstacle, and that storage design should therefore lead the way in sizing servers for use in SQL Server environments.

This chapter begins by covering the various I/O loads generated by the two major categories of database applications: online transaction processing (OLTP) and online analytical processing (OLAP). We'll look at the importance of striping data across multiple disks, the use of RAID technology to provide resilience against disk failure, and formulas for estimating the required number of disks to support the expected application load.

We conclude this chapter with a look at the various types of storage systems, including direct-attached storage (DAS) and Fibre/iSCSI storage area networks (SANs), and explore some of the challenges of using SANs with SQL Server. Finally, we cover the emergence of solid-state disks (SSDs) and the significant ramifications this technology will have on server sizing and performance tuning.