SAN Performance Tuning with SQLIO
From SQLServerPedia
|
SQLIO is a free utility from Microsoft that measures storage IO performance. The name "SQLIO" is horribly misleading, because it doesn't really have anything to do with SQL Server.
SQLIO Video TutorialIn this ten-minute video, Brent Ozar explains how to get started with SQLIO, how to create a batch file to test your SAN, and how to analyze the results. http://tutorials.sqlserverpedia.com/SQLServerPedia-20090413-SQLIO.flv For more video tutorials like this, check out the SQL Server Tutorials page.
Downloading and Configuring SQLIO[Download SQLIO from Microsoft] Notice that I said SQLIO, not SQLIOSIM. Experienced database administrators will often direct you to SQLIOSIM because it's easier to use and mimics SQL Server's disk activity patterns. Here's the catch: it won't necessarily test your SAN to its maximum potential. Your SAN team may indicate that if your SQLIOSIM results aren't fast enough, it's a SQL-related problem, not a SAN-related problem. They may use testing utilities from vendors that mimic results closer to what SQLIO will give you. We want to push the SAN's pedal to the metal and find out how fast it'll go in any situation. After installing SQLIO, edit the param.txt file and change these two parameters:
After saving param.txt, run this at the command line in the same directory where SQLIO is installed in order to create the test file: sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10 When it finishes, your test file is created, and it's time to run our real tests. Testing Your SAN PerformanceInstead of picking and choosing individual parameters to use, I like to take the shotgun approach: try every possible combination of random versus sequential, low and high numbers of threads, read versus write, etc. The below batch file will take all of the possibilities (except sequential - just duplicate and replace random with sequential) and run 'em all. Chuck Lathrope Update Note: Most people have not noticed that the thread count affects your outstanding IO values, including our fearless Brent. Luckily, this is a wiki and we all can participate. For example, if you have thread count of 2, you need to double your provided SQLIO "o" parameter value to get the actual IO outstanding value. This is incorporated into new version of import proc below. Also, cache on the SAN can really have an effect on startup and subsequent runs of tests. Below, I have updated to add a timeout after each run so you can see the effects on your storage device. Some people prefer to run tests for long period and throw out the start and tail of the test results. Copy/paste this into a text file called SANTest.bat: Echo ====================================================================================>> FileExists-WriteSession1.txt Echo Test Start - Files exist already 0 sec Timeout>> FileExists-WriteSession1.txt Echo ====================================================================================>> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt Echo ====================================================================================>> FileExists-WriteSession1.txt Echo b64 No delay >> FileExists-WriteSession1.txt Echo ====================================================================================>> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 Echo ====================================================================================>> FileExists-WriteSession1.txt Echo Test Start - Files exist already 10 sec Timeout>> FileExists-WriteSession1.txt Echo ====================================================================================>> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 Echo ====================================================================================>> FileExists-WriteSession1.txt Echo b64 10 sec delay >> FileExists-WriteSession1.txt Echo ====================================================================================>> FileExists-WriteSession1.txt sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-WriteSession1.txt timeout /T 10 sqlio -kW -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-WriteSession1.txt Echo ====================================================================================>> FileExists-ReadSession1.txt Echo Test Start - Files exist already 0 sec Timeout>> FileExists-ReadSession1.txt Echo ====================================================================================>> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt Echo ====================================================================================>> FileExists-ReadSession1.txt Echo b64 No delay >> FileExists-ReadSession1.txt Echo ====================================================================================>> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 Echo ====================================================================================>> FileExists-ReadSession1.txt Echo Test Start - Files exist already 10 sec Timeout>> FileExists-ReadSession1.txt Echo ====================================================================================>> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b8 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 Echo ====================================================================================>> FileExists-ReadSession1.txt Echo b64 10 sec delay >> FileExists-ReadSession1.txt Echo ====================================================================================>> FileExists-ReadSession1.txt sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t1 -o64 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o2 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t4 -o16 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o1 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile1.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile2.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile3.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile4.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile5.dat >> FileExists-ReadSession1.txt timeout /T 10 sqlio -kR -s30 -t8 -o8 -frandom -b64 -BH -LS h:\TestFile6.dat >> FileExists-ReadSession1.txt Whew! And that's just one pass - if you want to do multiple passes of the same tests for consistency's sake, like to eliminate the chance that other servers are running on the same SAN and affecting your performance results, you would want to paste that same set of 200+ lines multiple times into your batch file. Let's take the first line of the batch file and analyze what it's doing: sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat The most important parameters are:
Do a find & replace in your text file and replace -dM with the drive letter of your choice. If you're testing on your S drive, for example, you would replace -dM with -dS. Then go to the command prompt in the same directory as SQLIO is installed and type: SANTEST.BAT This will run our newly created batch file and dump the results into a text file you specified in the batch file. This will take a long time, like six hours or more, and it will be hammering your SAN. Don't run this on a production server, and don't even run it on the same SAN as a production server when the production server is under load because it may time out. I've had instances where this batch file has actually caused a SAN to restart itself, so use this with caution - preferably in a test lab or before your SAN goes live. Hours later, when it finishes, you'll have a text file with lots of juicy metrics about your storage performance. Importing SQLIO Results into SQL ServerThose text file results are cryptic - time to bring them into our favorite data analysis platform, Microsoft Access. Wait, I'm kidding, put the axe down - we'll import them into SQL Server. Script to Create the Tables and and ETL Stored ProcedureBefore we start, create a database that you'll use for SQLIO data storage or designate an existing utility database that you'll use. This script requires SQL Server 2005 or newer, since it uses the varchar(max) field. In that database, run the below script to create the tables for results storage:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Import of raw text file output. It is just a temporary table.
CREATE TABLE dbo.SQLIO_Import(
[RowID] int IDENTITY(1,1) NOT NULL,
[ResultText] [varchar](1000) NULL,
CONSTRAINT [PK_SQLIO_Import] PRIMARY KEY CLUSTERED
(
[RowID] ASC
));
--Parent table for test result details.
CREATE TABLE dbo.SQLIO_TestPass(
[TestPassID] int IDENTITY(1,1) NOT NULL,
[ServerName] nvarchar(50) NOT NULL,
[ServerOSInfo] nvarchar(100) NOT NULL,
[DriveQty] int NULL,
[DriveType] nvarchar(10) NULL,
[DriveRPM] int NULL,
[DriveRaidLevel] nvarchar(10) NULL,
[TestDate] datetime NULL DEFAULT GETDATE(),
[Storagemodel] nvarchar(50) NULL,
[Storagefirmware] nvarchar(50) NULL,
[MPIOInfo] nvarchar (100) NULL,
[PartitionOffset] int NULL,
[RAIDStripeSize] smallint NULL,
[CacheReadWriteRatio] nvarchar (10) NULL,
[HBAQueueDepth] smallint NULL,
[Filesystem] nvarchar(50) NULL,
[FSClusterSizeKB] smallint NULL,
[TestNotes] nvarchar (200) NULL
CONSTRAINT [PK_SQLIO_TestPass] PRIMARY KEY CLUSTERED
(
[TestPassID] ASC
));
--Child table for test passes.
CREATE TABLE dbo.SQLIO_TestPassDetail(
[TestPassDetailID] int IDENTITY (1,1) NOT NULL,
[TestPassID] int NOT NULL,
[SQLIO_Version] nvarchar(20) NULL,
[Buffering] nvarchar(70) NULL,
[FileInfo] nvarchar (150) NULL,
[FileSizeMB] int NULL,
[Threads] int NULL,
[ReadOrWrite] char(1) NULL,
[DurationSeconds] int NULL,
[SectorSizeKB] int NULL,
[IOpattern] nvarchar(50) NULL,
[IOsOutstanding] int NULL,
[IOs_Sec] decimal(18, 0) NULL,
[MBs_Sec] decimal(18, 0) NULL,
[LatencyMS_Min] int NULL,
[LatencyMS_Avg] int NULL,
[LatencyMS_Max] int NULL
CONSTRAINT [PK_SQLIO_TestPassDetail] PRIMARY KEY CLUSTERED
(
[TestPassDetailID] ASC
));
ALTER TABLE dbo.SQLIO_TestPassDetail
ADD CONSTRAINT FK_SQLIO_TestPassDetail_SQLIO_TestPass
FOREIGN KEY (TestPassID) REFERENCES dbo.SQLIO_TestPass (TestPassID);
/*
Notes:
*** You must provide the -B parameter in SQLIO as not providing it will skip an output row which all this code is dependent on.
*/
CREATE PROCEDURE dbo.USP_Import_SQLIO_TestPass
@ServerName NVARCHAR(50),
@ServerOSInfo NVARCHAR(100),--Windows 2008 Ent SP1
@DriveQty INT = NULL,--How many drives are in the diskgroup/raid.
@DriveType NVARCHAR(10) = NULL,--FC, SAS, SATA, PATA, FATA, iSCSI, etc
@DriveRPM INT = NULL,
@DriveRaidLevel NVARCHAR(10) = NULL,--e.g. RAID 10
@TestDate DATETIME = NULL,
@Storagemodel NVARCHAR(50) = NULL,
@Storagefirmware NVARCHAR(50) = NULL,
@MPIOInfo NVARCHAR(100) = NULL,--Notes on version and method used for LB/Failover.
@PartitionOffset INT = NULL,
@RAIDStripeSize SMALLINT = NULL,
@CacheReadWriteRatio NVARCHAR(10) = NULL,--R/W e.g. 50/50, 0/100, on/off.
@HBAQueueDepth SMALLINT = NULL,
@Filesystem NVARCHAR(50) = NULL,--FAT32, NTFS
@FSClusterSizeKB SMALLINT = NULL,
@TestNotes NVARCHAR(200) = NULL
AS
BEGIN
SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Identity int
DECLARE @SQLIOImport Table (RowID Int IDENTITY(1,1), ParameterRowID SmallInt, ResultText varchar(1000))
IF @TestDate IS NULL
SET @TestDate = Getdate()
--Data cleanup. If you remove any of these lines, adjust row offset "imp.rowid + x" in select statements below.
DELETE FROM SQLIO_Import
WHERE (ResultText LIKE 'Waiting for%'
OR ResultText LIKE '%timeout /T%'
OR ResultText IS NULL
OR ResultText LIKE 'parameter file used:%'
OR ResultText LIKE '%using mask%'
OR ResultText LIKE '%size of file%needs to be%'
OR ResultText LIKE 'current file size:%'
OR ResultText LIKE 'need to expand by:%'
OR ResultText LIKE 'expanding %'
)
--Using parameter file with SQLIO inserts different rows and messes with row numbering
--Create temp table in memory for updated row numbering.
Insert into @SQLIOImport (ResultText)
Select ResultText
From SQLIO_Import
Order by RowID
/* Update the ParameterRowID field for easier querying */
UPDATE @SQLIOImport
SET ParameterRowID = (SELECT TOP 1 rowid
FROM @SQLIOImport parm
WHERE parm.resulttext = 'sqlio v1.5.SG'
AND parm.rowid <= upd.rowid
ORDER BY rowid DESC)
FROM @SQLIOImport upd
/* Add new SQLIO_TestPass records from SQLIO_Import */
INSERT INTO dbo.sqlio_testpass (
ServerName,
ServerOSInfo,
DriveQty,
DriveType,
DriveRPM,
DriveRaidLevel,
TestDate,
Storagemodel,
Storagefirmware,
MPIOInfo,
PartitionOffset,
RAIDStripeSize,
CacheReadWriteRatio,
HBAQueueDepth,
Filesystem,
FSClusterSizeKB,
TestNotes)
SELECT @ServerName,
@ServerOSInfo,
@DriveQty,
@DriveType,
@DriveRPM,
@DriveRaidLevel,
@TestDate,
@Storagemodel,
@Storagefirmware,
@MPIOInfo,
@PartitionOffset,
@RAIDStripeSize,
@CacheReadWriteRatio,
@HBAQueueDepth,
@Filesystem,
@FSClusterSizeKB,
@TestNotes
SET @Identity = @@IDENTITY
INSERT INTO SQLIO_TestPassDetail (
TestPassID,
SQLIO_Version,
Threads,
ReadOrWrite,
DurationSeconds,
SectorSizeKB,
IOpattern,
IOsOutstanding,
Buffering,
FileInfo,
FileSizeMB,
IOs_Sec,
MBs_Sec,
LatencyMS_Min,
LatencyMS_Avg,
LatencyMS_Max)
SELECT @Identity,
--Test Debug: Select
(SELECT REPLACE(resulttext,'sqlio ','')
FROM @SQLIOImport i
WHERE imp.rowid = i.rowid) AS sqlio_version,
(SELECT LEFT(resulttext,(Charindex(' thread',resulttext)))
FROM @SQLIOImport i
WHERE imp.rowid + 2 = i.rowid) AS threads,
(SELECT Case When Charindex('reading',resulttext) > 0 Then 'R' Else 'W' End
FROM @SQLIOImport i
WHERE imp.rowid + 2 = i.rowid) AS readorwrite,
(SELECT Substring(resulttext,(Charindex(' for',resulttext)) + 4,
(Charindex(' secs ',resulttext)) - (Charindex(' for',resulttext)) - 4)
FROM @SQLIOImport i
WHERE imp.rowid + 2 = i.rowid) AS durationseconds,
(SELECT Substring(resulttext,7,(Charindex('KB',resulttext)) - 7)
FROM @SQLIOImport i
WHERE imp.rowid + 3 = i.rowid) AS sectorsizekb,
(SELECT Substring(resulttext,(Charindex('KB ',resulttext)) + 3,
(Charindex(' IOs',resulttext)) - (Charindex('KB ',resulttext)) - 3)
FROM @SQLIOImport i
WHERE imp.rowid + 3 = i.rowid) AS iopattern,
--Messy case statements are for missing row with 0 outstanding IOs.
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT Substring(resulttext,(Charindex('with ',resulttext)) + 5,
(Charindex(' outstanding',resulttext)) - (Charindex('with ',resulttext)) - 5)
FROM @SQLIOImport i
WHERE imp.rowid + 4 = i.rowid)
Else 0
End AS iosoutstanding,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT REPLACE(CAST(resulttext AS NVARCHAR(70)),'buffering set to ', '')
FROM @SQLIOImport i
WHERE imp.rowid + 5 = i.rowid)
ELSE (SELECT REPLACE(CAST(resulttext AS NVARCHAR(70)),'buffering set to ', '')
FROM @SQLIOImport i
WHERE imp.rowid + 4 = i.rowid)
END AS buffering,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT Substring(resulttext,(Charindex('for file: ',resulttext)) + 10, LEN(resulttext))
FROM @SQLIOImport i
WHERE imp.rowid + 6 = i.rowid)
ELSE (SELECT Substring(resulttext,(Charindex('for file: ',resulttext)) + 10, LEN(resulttext))
FROM @SQLIOImport i
WHERE imp.rowid + 5 = i.rowid)
END AS fileinfo,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT Substring(resulttext,(Charindex('size: ',resulttext)) + 6,
(Charindex(' for ',resulttext)) - (Charindex('size: ',resulttext)) - 9)
FROM @SQLIOImport i
WHERE imp.rowid + 6 = i.rowid)
ELSE (SELECT Substring(resulttext,(Charindex('size: ',resulttext)) + 6,
(Charindex(' for ',resulttext)) - (Charindex('size: ',resulttext)) - 9)
FROM @SQLIOImport i
WHERE imp.rowid + 5 = i.rowid)
END AS filesizemb,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT LTRIM(RIGHT(resulttext,(Len(resulttext) - 9)))
FROM @SQLIOImport i
WHERE imp.rowid + 10 = i.rowid)
ELSE (SELECT LTRIM(RIGHT(resulttext,(Len(resulttext) - 9)))
FROM @SQLIOImport i
WHERE imp.rowid + 9 = i.rowid)
END AS ios_sec,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT LTRIM(RIGHT(resulttext,(Len(resulttext) - 9)))
FROM @SQLIOImport i
WHERE imp.rowid + 11 = i.rowid)
ELSE (SELECT LTRIM(RIGHT(resulttext,(Len(resulttext) - 9)))
FROM @SQLIOImport i
WHERE imp.rowid + 10 = i.rowid)
END AS mbs_sec,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM @SQLIOImport i
WHERE imp.rowid + 13 = i.rowid)
ELSE (SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM @SQLIOImport i
WHERE imp.rowid + 12 = i.rowid)
END AS latencyms_min,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM @SQLIOImport i
WHERE imp.rowid + 14 = i.rowid)
ELSE (SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM @SQLIOImport i
WHERE imp.rowid + 13 = i.rowid)
END AS latencyms_avg,
CASE WHEN (SELECT resulttext FROM @SQLIOImport i WHERE imp.rowid + 4 = i.rowid) like '%enabling%' Then
(SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM @SQLIOImport i
WHERE imp.rowid + 15 = i.rowid)
ELSE (SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM @SQLIOImport i
WHERE imp.rowid + 14 = i.rowid)
END AS latencyms_max
FROM @SQLIOImport imp
INNER JOIN (Select distinct ParameterRowID From @SQLIOImport) batchStartLine ON imp.rowid = batchStartLine.ParameterRowID
ORDER BY imp.ParameterRowID
--Update IOsOutstanding to be a multiple of thread counts (SQLIO output bug).
Update SQLIO_TestPassDetail
Set IOsOutstanding = Threads * IOsOutstanding
Where TestPassID = @Identity
And Threads > 1
END --Proc
GO
/* Empty out the ETL table - Moved to truncate table before bcp import to help with your troubleshooting.
DELETE dbo.sqlio_import
*/
The script creates three things:
The stored procedure, which you NEED to run in order to move on, expects these parameters, which it uses when inserting into SQLIO_TestPass. None of these have to be formatted a specific way - as long as they fit the SQL Server field definitions, they're fine:
Now that our framework is in place, let's import our first round of results. Importing the Text File into SQL Server 2005Method 1
Open a new query and verify that the data was successfully imported by typing:
SELECT p.*, d.*
FROM dbo.SQLIO_TestPassDetail d
JOIN SQLIO_TestPass p ON d.testpassid = p.testpassid
WHERE p.TestPassID = 1
If there's no rows, something went wrong with the import process. Stop here and troubleshoot. Method 2Using bcp with a view allows for full scripting of import and saves a ton of time. Create View vw_SQLIO_Import as Select ResultText From SQLIO_Import GO TRUNCATE TABLE SQLIO_Import BULK INSERT vw_SQLIO_Import FROM '\\sqlguychuck\bcp\ChuckIOLoadTesting\FileExists-WriteSession1.txt' with (FIELDTERMINATOR = '\r\n', BATCHSIZE = 500, TABLOCK) Otherwise, execute the stored procedure to move the data into the reporting table, like this: EXECUTE dbo.USP_Import_SQLIO_TestPass @ServerName='sqlguychuck' ,@ServerOSInfo='Windows 2008 SP1' ,@DriveQty=30 ,@DriveType='SAS FC' ,@DriveRPM=15000 ,@DriveRaidLevel='RAID 10' ,@TestDate='2009/8/28' ,@Storagemodel='HP EVA8000' ,@Storagefirmware='' ,@MPIOInfo='HP DSM, 6.0.6001 SQST' ,@PartitionOffset=1024 ,@RAIDStripeSize=128 ,@CacheReadWriteRatio='50/50' ,@HBAQueueDepth=128 ,@Filesystem='NTFS' ,@FSClusterSizeKB='64' ,@TestNotes='Run by Chuck Lathrope. Single Server, FileExists-WriteSession1.bat' The data will be parsed and inserted into the reporting table, which we can query (add columns as needed):
SELECT Testnotes ,
p.TestPassID ,
FileSizeMB ,
threads ,
DurationSeconds ,
SectorSizeKB ,
IOsOutstanding ,
IOs_Sec ,
MBs_Sec ,
LatencyMS_Avg
FROM dbo.SQLIO_TestPassDetail d
JOIN SQLIO_TestPass p ON d.testpassid = p.testpassid
WHERE p.TestPassID = 1
Now, the data is arranged in a way that's easier for reporting. Analyzing the SQLIO ResultsStart by analyzing the data to find the fastest SAN throughput: SELECT * FROM dbo.SQLIO_TestPassDetails ORDER BY MBs_Sec DESC That column is the SAN's throughput. Notice the LatencyMS_Avg column, though, which indicates the milliseconds of latency. A high throughput number is not necessarily good if the system is taking a long time to respond. Look for five or so of the highest throughput numbers that represents a good balance of high throughput and low latency. Then, look at the parameters that were used in order to achieve that throughput. Make a note of these, because if you need to do repeated throughput testing on your SAN to test different configurations, you can use these high-throughput configurations to run a much shorter testing pass. Instead of testing your SAN overnight, you can get a quick idea in a matter of minutes because you'll know what parameters tend to drive very high throughput on your SAN. You'll still want to test all parameters when possible, because changes to the SAN may affect how it handles other parameters, but this will give you a quick estimate of whether things are getting better or worse. More Reading About SQLIOHere's a few interesting links about other bloggers' experience with SQLIO:
|