Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 4. Working with Storage

WHAT'S IN THIS CHAPTER

• The differences between RAID levels

• Configuring Windows and SQL Server to get the best storage performance

• Testing storage subsystems to determine if they deliver on their promises

Storage seems to be an impenetrable black box where data comes in and goes back out again, but rarely at the speeds that the database administrator expects. When storage area networking (SAN) is involved (described later in this chapter), the disks might literally be inside a black box.

This chapter will demystify what's inside the black box: the types of storage, how it attaches to the server, and how it works when it's shared between multiple servers in a SAN. After discussing basics and theories, you'll dive into testing storage performance in practice to determine whether the I/O subsystems are delivering on expectations, look at where common bottlenecks could be sapping speed, and examine free or low-cost ways to remove those bottlenecks.

Finally, these storage concepts are related to how they impact SQL Server, and how they change the decisions database administrators make about database design, configuration, and troubleshooting.

TYPES OF STORAGE

Storage is an expensive, long-term investment: It requires careful planning long before the data starts coming in. Other parts of SQL Server can be easy to change on the fly, but even minor changes in storage can require major outages. With that in mind, this section explains the basic types of drives, shows ...