Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 102

many transactions you need to handle, you can predict how many underlying disks will be needed in
your storage array.
For example, assume that you will handle 10,000 transactions, and your storage is made up of 10K
SAS drives that are directly attached to your server. Based on the math described earlier, you know
that a 10K SAS drive will perform about 140 IOPS; therefore, you need 142 of them. Seventy-one
disks would be enough to absorb 10K reads, but you are going to coni gure a RAID 1+0 set, so you
need double the number of disks.
If you need to handle a lot of tempdb operations, then you need to appropriately size the tempdb
drive. In this case, you are going to design tempdb to be about 20 percent of the expected I/O for the
main data i les, so you expect 2,000 IOPS. This will require that 28 disks in a RAID 1+0 coni gura-
tion back the tempdb database.
You expect that a backup load will be entirely sequential in nature. Remember that SATA drives
perform sequential operations extremely well, so they are a great candidate for dump drives. These
drives are usually less expensive and offer greater capacity than SAS or Fibre Channel disks.
Large SATA disks store data in an extremely dense format, which puts them at greater risk for a
failure and at the same time increases the chance that a second failure will occur before the RAID
system can regenerate data from the i rst failure. For this reason, RAID 6 is an ideal candidate to
protect the data drives. RAID 6 has the greatest performance overhead, so it is important to adjust
the backup I/O appropriately. In most cases backup I/O should not exceed a 512KB transfer size.
Finally, you need to plan for your log drives. Logs are extremely latency sensitive. Therefore, you
should completely isolate the log onto its own set of RAID 1+0 SAS disk drives. You expect sequen-
tial I/O, so the number of disks is governed by capacity rather than random performance I/O.
Why wouldn't you mix the log drive with the backup drive? Sequential disk access assumes that
the drive head enters a track and reads sequential blocks. If you host applications that sequentially
access a set of disk drives, you will cause the disk drive head to seek excessively.
This excessive head seeking is called large block random access . Excessive seeking can literally drop
the potential IOPS by half:
15K SAS Max Seek: 90 = 1,000 ÷ (9 + ((60,000 ÷ 15,000) ÷ 2))
Isolating sequential performance not only applies to log i les. Within a database you can often iden-
tify specii c data tables that are sequentially accessed. Separating this sequential access can greatly
reduce the performance demand on a primary storage volume.
In large databases with extremely heavy write workloads, the checkpoint process can often over-
whelm a storage array. SQL Server checkpoints attempt to guarantee that a database can recover
from an unplanned outage within the default setting of one minute. This can produce an enormous
spike of data in an extremely short time.
We have seen the checkpoint process send 30,000 write IOPS in one second. The other 59 seconds
are completely idle. Because the storage array will likely be overwhelmed with this workload, SQL
Server is designed to dial back the I/O while trying to maintain the one-minute recovery goal. This
slows down the write process and increases latency.