Databases Reference
In-Depth Information
TABLE 4-1:
Windows Storage Performance Counters
LOGICALDISK PERFMON OBJECT
Average Disk sec/Read
Average Disk sec/Write
Disk Reads/sec
Disk Writes/sec
Disk Read Bytes/sec
Disk Write Bytes/sec
The latency counters are measured in milliseconds. A reading of 0.001 is one millisecond, 0.010
is 10 milliseconds, and .100 is 100 milliseconds. SQL Server best practices call for latency that is
under 20 milliseconds. This is not a hard-and-fast rule, however, as many applications will not
tolerate latency that exceeds several milliseconds.
It is important to understand the underlying hardware coni guration, application, and workload.
In some cases, such as a SQL Server standard backup, large I/O sizes will drastically increase
latency. If you change the backup I/O size to 8MB, the latency will increase, but you can still
achieve a lot of work.
If you are implementing a specialized system, such as a SQL Server Fast Track Data Warehouse, you
will actively coni gure the data i les so they issue sequential I/O. Be sure to test your specii c con-
i guration so you can properly interpret the results.
The Disk Reads and Writes per second counters list how many I/Os are generated each second
(Storage administrators often refer to this as IOPS). Disk Read and Write Bytes per second demon-
strate the throughput of your storage system. To calculate average I/O sizes, simply divide bytes per
second by the number of operations per second.
Knowing the size of the I/O can rel ect application behavior. When performing highly random
I/O access, SQL Server will write 8K data pages and read 64KB data extents from the data i les.
Performing sequential operations, such as a table scan, will generate I/O that is dynamically sized
from 8K to 512KB. Dynamic I/O sizing, also known as Read-Ahead, is one of the hidden gems of
SQL Server. Increasing I/O size decreases the number of I/Os and increases efi ciency.
Disk Drive Performance
A disk drive (see Figure 4-12) is made up of an external logic board and an internal hard drive
assembly. The logic board provides connectivity between the disk and the host. Each drive interface
supports one of many available communications protocols. Modern interfaces use a high-speed
serial connection. The interfaces that are most commonly used for database applications are SATA
(Serial Advanced Technology Attachment), FC (Fibre Channel), and SAS (Serial Attached SCSI).
Search WWH ::
Custom Search