Implement Optimal I/O Subsystem for SQL Server
Implement Optimal I/O Subsystem for SQL Server
By : Kasim Wirama, MCDBA
Database performance is always considered as prone point of performance problem, one of I usually encounter is not optimal I/O subsystem. This article I would share points of planning optimal I/O subsystem.
Here are the points :
1. If your disc subsystem is based on spindle, use faster spindles and distribute database workload evenly across those spindles. One of the way is use multiple filegroups. With multiple file groups, you can backup for each filegroup so backup and restore window period gets shortened.
2. For fast write, implement RAID 1+0 or RAID 1. So place your transaction log, data file and tempdb to the RAID.
3. Isolate tempdb with dedicated physical disc.
4. Isolate your log file from data file, if possible isolate nonclustered index from clustered index if your table is heavily access with read and write, for example TransactionOrder table, this table is heavily written by front end, but it is also intensively read by back end (for example. Accounting staff need to generate point in time financial reporting).
5. If you have n-core processor, for example 2-core processor, create n-files on tempdb.
6. Always update HBA driver from your vendor.
7. Performing I/O benchmarking against your disc to know maximum capacity that it could handle on peak hours with I/O stress test tool (for example : SQLIO tool - http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en)
8. If you suspects your I/O subsystem performs suboptimal, you can make sure by monitoring this performance monitor counter below :
a. Average read bytes/sec, average write bytes/sec b. Reads/sec, writes/sec c. Disk read bytes/sec, disk write bytes/sec d. Average disk sec/read, average disk sec/write e. Average disk queue length