Databases Reference
In-Depth Information
64KB random reads, it will generate about 12MBs per second. This same drive will perform 88MBs
per second of sequential 64KB reads. Changing the I/O size to 512KB will quickly cause the disk
drive to hit its maximum transfer rate of 600MBs per second.
Increasing the I/O size has its limitations. Most hardware RAID controllers are designed to opti-
mally handle 128KB I/Os. Generating I/Os that are too big will stress the system resources and
increase latency.
One example is a SQL Server backup job. Out-of-the-box, SQL Server backup will generate 1,000KB
I/Os. Producing I/Os this large will cause a lot of stress and high latency for most storage arrays.
Changing the backup to use 512KB I/Os will usually reduce the latency and often reduce the time
required to complete the backup. Each storage array is different, so be sure to try different I/O sizes
to ensure that backups run optimally.
Henk Van Der Valk has written several articles that highlight backup optimization:
This Backup statement will set the maximum transfer size to 512KB.
BACKUP DATABASE [DBName] TO DISK = N'E:\dump\BackupFile.bak'
WITH MAXTRANSFERSIZE=524288, NAME = BackupName
GO
Server Queues
Each physical disk drive can perform one operation and can queue one operation. Aggregating disks
into a RAID set increases the pool of possible I/O. Because each physical disk can perform two
operations, a viable queue setting is determined by multiplying the number of disks in a RAID set
by two. A RAID set containing 10 disk drives, for example, will work optimally with a queue of 20
outstanding I/Os.
Streaming I/O requires a queue of I/Os to continue operating at a high rate. Check the HBA settings
in your server to ensure they are maximized for the type of I/O your application is generating. Most
HBAs are set by the manufacturer to a queue of 32 outstanding I/Os. Higher performance can often
be achieved by raising this value to its available maximum. The SQL Server FAST Track program
recommends that queue depth be set at 64.
Keep in mind that in a shared storage SAN environment, the performance gains of one application
are often achieved at the cost of overall performance. As a SQL Server administrator, be sure to
advise your storage administrators of any changes you are making.
File Layout
This section divides the discussion about how to coni gure storage into traditional disk storage and
array-based storage pools. Traditional storage systems offer predictable performance. You expect
Online Transaction Processing (OLTP) systems to generate random workload. If you know how
Search WWH ::
Custom Search