The time it takes for an I/O to complete (How long it takes an operation to finish measured in milliseconds).

Measurement starts when the OS sends a request to the drive (or controller) and ends when the drive finishes processing the request.

Metrics:

• < 8ms : excellent

• < 12ms : good

• < 20ms: fair

• > 20ms: poor

Latency is not only measure of performance, SQL Server workload must also be checked as it differs from other server load.

Monitor Performance:

• Windows Performance Monitor (PerfMon.exe)

> Physical Disk: Avg. Disk Sec/Transfer

> Physical Disk: Avg. Disk Sec/Read

> Physical Disk: Avg. Disk Sec/Write

• Windows Resource Monitor (ResMon.exe)

• SQL Server DMV/DMO 

Input/Output Operations per Second - is the number of read or write operations per second.

• Directly related to latency; constant latency of 1 ms means a drive can process 1,000 I/O per second with queue depth of 1.

IOPS = queue depth / latency

• Block size matters - smaller size will allow higher I/Os per second that large block.

• Important to OLTP workload where transaction are small and read/write are typically single page and highly random.

Monitor Performance:

• Windows Performance Monitor (PerfMon.exe)

> Physical Disk: Disk Transfer/Sec

> Physical Disk: Disk Write/Sec

> Physical Disk: Disk Read/Sec

• Windows Resource Monitor (ResMon.exe)

• SQL Server DMV/DMO 

Measure of data volume over time, usually measured in MB/sec or GB/sec (also known as BANDWIDTH)

Sequential Throughput uses the following:

• SQL Server activities.

• Full database backups and restore.

• Replication synchronization and initialization of HA copies.

• Index creation and rebuild.

• Data warehouse/reporting large scans

Monitor Performance:

• Windows Performance Monitor (PerfMon.exe)

> Physical Disk: Disk Bytes/Sec

> Physical Disk: Disk Write Bytes/Sec

> Physical Disk: Disk Read Bytes/Sec

• Windows Resource Monitor (ResMon.exe)

• SQL Server DMV/DMO 

This is known as NTFS cluster size.

For SQL Server a 64KB allocation unit size should be used to match the size of a single extent.

• This will improve read-ahead performance.

• Reduces the number of splits I/Os

• Does not allow disk to use NTFS compression

Default size =4KB

• useful for file server - optimize for storage of lots of small files

RAID 0 - performance matter more than data loss disk .

Pros:

• Fast read & write performance.

Cons:

• Total loss of data from single disk failure.

RAID 1 - important data that requires redundancy to protect from disk failures.

        Pros:

• Mirrored storage protects from single disk loss.

Cons:

• Only provides half the storage.

• Write performance equals to one disk.

RAID 5- Where reads exceed writes, data files.

        Pros:

• Maximize available capacity from minimal number of disks with redundancy.

Cons:

• Protection from single disk failure only as loss of two disks results in total data loss.

• Write penalty for parity calculation.

• Significant performance impact occurs when degraded.

RAID 10 or 1+0 - Transaction log files, heavy write data files. And, redundancy is more important than cost.

Pros:

• Fast read & write performance.

• Support multiple disk features as long as two of the failed disk are not in the same RAID 1 pair.

Cons:

• Double the cost of storage.

Other RAID:

RAID 6 - similar to RAID 5 but provides double parity disks to fail and the array remains available.

RAID 50, RAID 100, etc - Specialized RAID configuration that stripe other RAID levels to increase performance.

Is any high performance network whose primary purpose is to enable storage device to communicate with computer systems.

Advantages:

• Shared Storage - Increases disk utilization.

> Reduces management by making it easier to create new volume and dynamically allocate storage.

> Create diskless servers that boot from SAN only.

> Mirroring, snapshots, continous data protection, clustering and geo-clustering only offered by SANs

> Improve performance, almost unlimited number of spindles, controllers, and cache can be put together to meet the requirements.

Disadvantages:

• Unpredictable performance -  when you share disk , controllers, and fiber switches between dozens of servers.

• Higher latency - Distance the I/O have to travel (added layer of switches, cabling and ports)

PCI Bus > HBA > FC Switch > FC Ports > Array processor > Disk

• Limited bandwidth

> FC now 16Gb/s max

>iSCI 10Gb/s max

> Note: 1 Gb/s = 128MB/s

• Expensive Cost