文档介绍:祥解磁盘 io 性能对数据库的影响(Effect of IO on the performance of the MAC disk database)
Effect of I/O on the performance of the MAC disk database
If SQL Server is configured to contain only a few gigabytes (GB) data, and does not bear the heavy reading or writing activities, it isn't much necessary to pay attention on the hard disk I/O theme, and the balance between the SQL Server I/O to obtain the best performance. But it includes the establishment of hundreds of GB bytes of data and / or bear the heavy read and / or write the large SQL Server database, it is necessary to balance the load among multiple hard drives, with the optimal configuration of SQL Server disk I/O performance.
The relation between nominal disk transfer rate and SQL Server
One of the most important aspects of optimizing database performance is tuning I/O performance. SQL Server is no exception, of course. Unless SQL Server runs on a machine with large enough memory to hold the entire database, I/O performance will be determined by disk I/O subsystem processing, SQL, Server data read and write speed.
Remember the following rule of thumb: the standard Wide Ultra SCSI-3 hard disk can provide 75 discontinuous (random) I/O operations and 150 consecutive I/O operations per second for Windows and SQL Server. The nominal transmission rate of this hard disk is about 40 MB/ seconds. Keep in mind that it is more likely to limit the database server's rate of 75/150 I/O per second instead of 40 MB/ seconds. Specific calculations are as follows:
(75 random I/O operations per second) X (8 KB transmission) = 600 KB per second
The above calculation indicated that if given the hard disk random read or write SQL strict Server operation (single page read and write), may the hard disk can reach up to 600 KB per second (., MB per second) processing capacity. This is much lower than the 40 MB per second I/O processing capability of the driver calibration. The SQL Server worker threads, Graphical, ShowPlan, and Lazy