Inserts slow when tranaction log is on SCSI drive | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Inserts slow when tranaction log is on SCSI drive

Whilst investigating another problem, we stumbled across the following : Doing repeated inserts into a simple table is fast if the transaction log is on an IDE drive but slow if the transaction log is on a (non RAID) SCSI drive. It makes no difference what kind of drive the data log is on (i.e. IDE or SCSI). This is with a variety of different spec servers, each with Win 2K SP4 and SQL Server 2000 SP3a. For example, we set-up a dual processor 733 Mhz machine with 512 MB RAM and 4 SCSI drives. Each drive was basic. Drive C is the OS, Drive D is the data log, E is the transaction log, and F contains the OS page file. Inserts are far slower here than on a 500 MHz machine with 256 Mb RAM, with the same database, and with everything on just one IDE drive (i.e. OS, data log, transaction log, and pagefile). Queries are faster on the twin processor machine as expected. We put an IDE drive alongside the SCSI drives, set the tranaction log on that IDE drive and inserts became much faster. In order to rule out a hardware issue, we built a VB app to write to the drives. In all cases, writing for the VB app was faster on the SCSI drives than on the IDE drive. Our conclusion is that the issue really is to do with having the transaction log on a standalone SCSI drive. Any ideas anyone?
Are you really comparing apples to apples? What are the speeds of the drives? How much cache does each drive have? Are the read and write caches turned on or off for both drives? Are there any driver issues (are the current ones being used)? A fast SCSI drive should always outperform an IDE drive, assuming everything else is equal. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
]]>