Intelligently Manage Backups
Backups are a double-edged sword. Ideally, you want to back up large databases to premium disk to maximize throughput. The problem is that after your backups are finished, the only thing they require is lots of disk—and they can gobble up premium disk in a hurry. Because most full backups are done during off-peak hours, I typically recommend putting backups on non-premium disk when there isn’t enough premium disk space available. This approach allows backups to take up space only on non-premium disks, and when done during off-peak hours the performance overhead associated with using non-premium disk doesn’t have a big impact.
Another option for backups is a third-party compression backup agent. These are typically much cheaper than additional premium disk and provide the added benefit of decreasing recovery times. Off-box backups to an iSCSI SAN or a network file share can also be good options, but a good rule of thumb is to always have one or two days of complete backups, including full backups and logs, in easily accessible storage such as on the box in case of disaster. The last thing you want in an emergency is to wait while your backups are sucked over the wire on a 100 megabit or gigabit network.
Efficient Log File Management
Many SQL Server environments have only one RAID controller or one RAID array, which data and log files must share. Consequently, log files end up on premium disk and can devour lots of high-performance disk that they don’t need. For example, if you have a 100GB database that sees about 12GB of logged activity each day, you likely want a log file of about 25GB, enough to handle daily load and account for spikes in activity. This, in turn would consume 25GB of premium disk, but log file information is perfectly safe on lesser storage, such as a RAID 1 array. The net result is that you can end up wasting lots of premium disk if you're only backing up your log files on a daily basis—and backing up only once a day is a bad move from a recoverability standpoint anyhow.
Because log file backups and the associated truncation operation are extremely performant, using regular and frequent log-file backups can help maximize the amount of premium disk capacity available. For example, consider the typical workday schedule in Figure 2, where most updates and modifications happen during peak hours. In the case of the 100GB database mentioned earlier, implementing hourly backups of the log allows the log file to be only 4GB, a comfortable size, assuming roughly 1GB–2GB of modifications per hour during peak hours plus ample padding. Hourly backups allow completed transactions to be safely logged and backed up to non-premium disk, freeing more than 20GB of premium disk without sacrificing reliability or performance. On larger systems that are slowly growing over time, this approach, properly used, can reduce the need to purchase additional premium storage.
Figure 2: Usage versus time of day
More Performance, Less Money
Ultimately, managing disk performance can require effort, tuning, tweaking, and even educated guesses. It’s worth the effort, however: By using your existing resources creatively, you can maximize the efficiency of your expensive I/O subsystems, which in turn can boost SQL Server’s overall performance and save the money you'd otherwise spend to upgrade to a more expensive storage solution or purchase new storage.