professional database solutions Ltd.
Caring for your SQL Server environment

Testimonials

"Through the many years I have known Tim he has proved to be dependable, knowledgeable and resourceful - a great asset for any Microsoft SQL Server project."  - Ken England, Author of numerous SQL Server books.

Disk Subsystem Performance

In all the years I have been using SQL Server the most significant, reoccurring issue I have come across is that the performance of a database disk subsystem is not always what it should be.  Most customers will have specified a RAID system to improve reliability or speed (RAID 5 or more recently RAID1+0). You can monitor what figure you are getting on your live system using performance monitor but how do you know what the maximum possible is? There is a tool from Microsoft, recently improved from v7.0,  you can easily use to test what throughput your disks should be achieving.  This can be downloaded from http://download.microsoft.com/download/sql70/Utility/3.0.9.0/WIN98MeXP/EN-US/SQL70IOStress.exe , see article Q231619 for more details.  

What this neat little (floppy capable), stand alone utility does is to create two files in the location of your choice, one for database and one for transaction log, defaults sizes are 260MB and 63MB respectively.  It then carries out intense IO on these files in exactly the same way the SQL Server engine does.  At the end of 5 iterations a report is generated that includes an average throughput in MB/sec.  The figures in the table below are what I have collected from various types of PC.  Don't be too surprised that the figure is way below that specified by your HDD manufacturer, remember throughput is dependant on the type of activity and disk manufacturers will always give you the figure from the best type of activity.  If you find your subsystem is not performing as well as you think it should look towards the array configuration or the disk controller.  Or you could always ask PDS to have a look for you - info@pdsolutions.co.uk

 Disk throughput figures

 These figures are only general for the type of hardware specified.  They were obtained on an otherwise quiet computer with no network traffic or other background processing.