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
- 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.
- Download SQLIO Disk Subsystem Benchmark Tool from http://www.microsoft.com/download/en/confirmation.aspx?id=20163
- Install this Utility, by default this will be installed on C:\Program Files\SQLIO
- After installing SQLIO, edit the param.txt file which is located C:\Program Files\SQLIO\param.txt and change these two parameters
- First parameter – the physical location, drive letter which you wanted to test., like S:\testfile.dat. In my case this is C:\
- 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.
- 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.