How to TEST SQL Server Storage drives ?

Leave a comment (0) Go to comments

How will you check, how your SQL Server Drives are performing ?

What is the throughput of the Disk Drive / Storage where your Database file resides ?

What is SQL Server Storage speed ? is that fast on write operations or fast or read operations ?

All this questions can be easily answered by SQLIO tool, which will let your know your disk speed /  performance.

Steps to use this SQLIO

  1. First you should be aware about physical disks and must know what is Disk Buffer Cache, if you are not sure about this, and can not find this information from sys admin then, you can check this by using a utility name AIDA32 . On my laptop this shows that I have only 16 MB of buffer cache.

How to TEST SQL Server Storage drives ? sql server storage throuput sql server storage testing SQL Server IO testing sql server disk testing check sql server disk speed   

  1. Download SQLIO Disk Subsystem Benchmark Tool from http://www.microsoft.com/download/en/confirmation.aspx?id=20163
  2. Install this Utility, by default this will be installed on C:\Program Files\SQLIO
  3. After installing SQLIO, edit the param.txt file which is located C:\Program Files\SQLIO\param.txt and change these two parameters
    1. First parameter – the physical location, drive letter which you wanted to test., like S:\testfile.dat. In my case this is C:\
    2. Last parameter – the size of the testing file in megabytes. this number should be larger than your storage cache, because your real databases will be larger than this cache. Over SAN drive, this CACHE is in GB’s.
  4. Now run the following command on command line in the same directory where SQLIO is installed in order to create the test file
sqlio -kW -s10 -fsequential -o8 -b8 -LC -Fparam.txt timeout /T 10

sqlio -kR -s10 -fsequential -o8 -b8 -LC -Fparam.txt timeout /T 10

Note: You can even make a batch file and make multiple entries to test result analysis.


To understand this command switches and parameters, please read, C:\Program Files\SQLIO\Using SQLIO.rtf, which is installed with the utility.

For More details, Check this great Video post by Brent.

EOF - How to TEST SQL Server Storage drives ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.