Test SQL IO Performance

Hi,

Here is how to test your SQL IO Performance. This basically just test your hard drive IO so don't actually have anything to do with SQL.
Download SQLIO
http://www.microsoft.com/en-za/download/details.aspx?id=20163

Then install it on the SQL Server
Browse to it in command prompt as Administrator.

*****Do no do this on Production*****

Now for the default test you can just type in SQLIO but that wont help you much, you want to specify your different hard drives in your SQL Server, change the file size ex...
This will better explain it: https://www.youtube.com/watch?v=aF2_rmyrbLU

Here is a good article i got most of my info from:
http://blogs.msdn.com/b/sqlmeditation/archive/2013/04/04/choosing-what-sqlio-tests-to-run-and-automating-sqlio-testing-somewhat.aspx

Copy and Paste from the MSDN blog for easy ref for myself so all credit to Joseph

1. Create 2 Param.txt files in the SQLIO folder. One that uses a single thread and one that uses multiple threads. Call them ParamST.txt and ParamMT.txt

ParamST.txt contents might look like this: G:\testfile.dat 1 0x0 500

ParamMT.txt contents might look like this: G:\testfile.dat 8 0x0 500

2. Copy the text below and paste it into a text file.

echo ****** Read Tests *****

sqlio -kR -s300 -frandom -o8 -b8 -LS -FparamMT.txt > Reads8KRandom8Oustanding.txt
timeout /T 10
sqlio -kR -s300 -frandom -o8 -b64 -LS -FparamMT.txt > Reads64KRandom8Oustanding.txt
timeout /T 10
sqlio -kR -s300 -frandom -o8 -b512 -LS -FparamMT.txt > Reads512KRandom8Oustanding.txt
timeout /T 10

echo ****** Write Tests *****

sqlio -kW -s300 -frandom -o8 -b8 -LS -FparamMT.txt > Writes8KRandom8Outstanding.txt
timeout /T 10
sqlio -kW -s300 -frandom -o100 -b256 -LS -FparamST.txt > Writes256KRandom100Outstanding.txt
timeout /T 10
sqlio -kW -s300 -frandom -o200 -b256 -LS -FparamST.txt > Writes256KRandom200Outstanding.txt

2. Then save the text file as SQLIOTest.bat in the folder that contains SQLIO.EXE and you are ready to go

3. Just run SQLIOTest.bat from Command Prompt

Now to analyse the data:

You can do something like this and parse the data into excel...I am not going to do that.
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx

As far as i can gather from some research(Correct me if im wrong), the important info in the reports is the IOPS and MB/sec. The idea is to run this before you make a change and then again after you finished to see what the performance penalty is.

Hope this it helpful!