I/O performance monitoring for SQL Server 2008

I/O performance diagnostics

SQL Server performance is very dependent on the I/O subsystem. Unless your database is fit for physical memory, SQL Server often has database pages in and out of the cache pool. In this way, the actual I/O traffic occurs. Similarly, log records need to be written to disk before a transaction is committed explicitly. SQL Server can use tempdb for a variety of purposes, such as storing intermediate results, sorting, keeping lines of versions or others. So a good I/O subsystem is very important for SQL Server performance.

The performance of I/O depends on some of the following:

The types of disks include the IDE, SATA, SCSI, SAS, Fibre Channel Drive, where the IDE, SATA is generally used on personal computers.

At the same time, in order to balance data security, data performance and data capacity, a raid,raid is developed to combine multiple independent disks in different ways to form a hard drive group, providing higher storage performance than a single hard drive and improving data backup technology. RAID mainly includes several specifications, such as Raid0~raid7, common raid types are RAID0, RAID1, Raid5,raid10.

In addition, depending on how they are connected, they can also be divided into: Direct attached Storage (DAS), Storage area Networks (SAN), Fibre Channel and Storage area NETWORKS,ISCSI Storage area Networks.

Throughput and IOPS Metrics

Throughput depends primarily on the schema of the array, the size of the fibre Channel, and the number of hard drives. The architecture of the array is different from each array, but there is also internal bandwidth, but in general, the internal bandwidth is well designed, not the bottleneck. Next is the fiber channel to the data flow influence, in order to achieve the 1GB/S data flow request, we must use the 1GB*8=8GB fiber card, also may use 4 block 2GB fiber card. In fact, the number of hard drives, you can refer to the following indicators to calculate the way, assuming to meet the 1GB data flow requirements, the number of disks required.

IOPS (input/output Operations per Second), that is, the number of read/write (I/O) operations for each second, often used in databases, to measure the performance of random accesses.

The main determinant of IOPS depends on the array algorithm, cache hit ratio, and number of disks. Cache hit rate depends on the data distribution, cache size, data access rules, and cache algorithm.

Disk limitations, the IOPS that each disk can handle is limited, and typically the maximum IOPS per disk is determined, such as the ioPS of the IDE and SATA hard drives within 100 (we can use the HD Tune tool for IOPS testing), And the IOPS test results and test methods (such as random Read and write, sequential read-write, the proportion of read and write, the size of the transfer database, the number of disks is very related, although the disk's ioPS index is still to assess the pressure of the disk and whether it can meet the performance requirements of the system has a certain guiding significance.

Assuming that the business requirement is 10000 iops,120 block SCSI disks, what is the IOPS requirement for each disk in different cache hit rates, different read and write ratios, and different RAID levels?

Raid 0– I/o calculations per disk = (read + Write)/number of disks

Raid 1-I/o calculation per disk = [Read + (2* write)]/2

Raid 5-I/o calculation per disk = [Read + (4* write)]/disk count

Raid 10-I/o calculation per disk = [Read + (2* write)]/disk count

In addition, I/O bottlenecks occur when throughput exceeds 85%, so a single disk IOPS calculation rule is

((10000* (1-cache hit rate) * Read ratio) +10000* write proportional *raid factor)/disk number/0.85

That is, the IOPS per disk around 200 can meet the requirements of RAID0, RAID5, RAID10.

In addition, the general planning and recommendations for deployment of SQL Server are as follows:

The operating system and SQL Server are built separately on RAID1 disk mirrors; for high speed and security, the log files need to be installed on the RAID1/RAID10 alone; the tempdb file is best placed on the RAID0, and the data file is for security, performance, capacity, The comprehensive consideration of cost generally uses RAID5.