Downloading and Configuring SQLIOSQLIO Video Tutorial： http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_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 and run 'em all. Copy/paste this into a text file called SANTest.bat: sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t2 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t4 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t8 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t16 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t32 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kW -t64 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t2 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t4 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t8 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t16 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t32 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o1 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o2 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o4 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o8 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o16 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o32 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o64 -fsequential -b64 -BH -LS Testfile.dat sqlio -kR -t64 -s120 -dM -o128 -fsequential -b64 -BH -LS Testfile.dat 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 > RESULTS.TXT This will run our newly created batch file and dump the results into a text 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 RESULTS.TXT 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
CREATE TABLE [dbo].[SQLIO_Import](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[ParameterRowID] [int] NULL,
[ResultText] [varchar](max) NULL,
CONSTRAINT [PK_SQLIO_Import] PRIMARY KEY CLUSTERED
(
[RowID] ASC
))
GO
CREATE TABLE [dbo].[SQLIO_TestPass](
[TestPassID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](50) NOT NULL,
[DriveQty] [int] NOT NULL,
[DriveRPM] [int] NOT NULL,
[DriveRaidLevel] [nvarchar](10) NOT NULL,
[TestDate] [datetime] NOT NULL,
[SANmodel] [nvarchar](50) NOT NULL,
[SANfirmware] [nvarchar](50) NULL,
[PartitionOffset] [int] NULL,
[Filesystem] [nvarchar](50) NULL,
[FSClusterSizeBytes] [int] NULL,
[SQLIO_Version] [nvarchar](20) NULL,
[Threads] [int] NULL,
[ReadOrWrite] [nchar](1) NULL,
[DurationSeconds] [int] NULL,
[SectorSizeKB] [int] NULL,
[IOpattern] [nvarchar](50) NULL,
[IOsOutstanding] [int] NULL,
[Buffering] [nvarchar](50) NULL,
[FileSizeMB] [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_TestPass] PRIMARY KEY CLUSTERED
(
[TestPassID] ASC
))
GO
CREATE PROCEDURE [dbo].[USP_Import_SQLIO_TestPass]
@ServerName NVARCHAR(50),
@DriveQty INT,
@DriveRPM INT,
@DriveRaidLevel NVARCHAR(10),
@TestDate DATETIME,
@SANmodel NVARCHAR(50),
@SANfirmware NVARCHAR(50),
@PartitionOffset INT,
@Filesystem NVARCHAR(50),
@FSClusterSizeBytes INT
AS
SET nocount off
IF @TestDate IS NULL
SET @TestDate = Getdate()
/* Add a blank record to the end so the last test result is captured */
INSERT INTO dbo.SQLIO_Import
(ParameterRowID,
ResultText)
VALUES
(0,
'');
/* Update the ParameterRowID field for easier querying */
UPDATE dbo.sqlio_import
SET parameterrowid = (SELECT TOP 1 rowid
FROM dbo.sqlio_import parm
WHERE parm.resulttext LIKE '%\%'
AND parm.rowid <= upd.rowid
ORDER BY rowid DESC)
FROM dbo.sqlio_import upd
/* Add new SQLIO_TestPass records from SQLIO_Import */
INSERT INTO dbo.sqlio_testpass
(servername,
driveqty,
driverpm,
driveraidlevel,
testdate,
sanmodel,
sanfirmware,
partitionoffset,
filesystem,
fsclustersizebytes,
sqlio_version,
threads,
readorwrite,
durationseconds,
sectorsizekb,
iopattern,
iosoutstanding,
buffering,
filesizemb,
ios_sec,
mbs_sec,
latencyms_min,
latencyms_avg,
latencyms_max)
SELECT @ServerName,
@DriveQty,
@DriveRPM,
@DriveRaidLevel,
@TestDate,
@SANmodel,
@SANfirmware,
@PartitionOffset,
@Filesystem,
@FSClusterSizeBytes,
(SELECT REPLACE(resulttext,'sqlio ','')
FROM dbo.sqlio_import impsqlio_version
WHERE imp.rowid + 1 = impsqlio_version.rowid) AS sqlio_version,
(SELECT LEFT(resulttext,(Charindex(' threads',resulttext)))
FROM dbo.sqlio_import impthreads
WHERE imp.rowid + 3 = impthreads.rowid) AS threads,
(SELECT Upper(Substring(resulttext,(Charindex('threads ',resulttext)) + 8,
1))
FROM dbo.sqlio_import impreadorwrite
WHERE imp.rowid + 3 = impreadorwrite.rowid) AS readorwrite,
(SELECT Substring(resulttext,(Charindex(' for',resulttext)) + 4,
(Charindex(' secs ',resulttext)) - (Charindex(' for',resulttext)) - 4)
FROM dbo.sqlio_import impdurationseconds
WHERE imp.rowid + 3 = impdurationseconds.rowid) AS durationseconds,
(SELECT Substring(resulttext,7,(Charindex('KB',resulttext)) - 7)
FROM dbo.sqlio_import impsectorsizekb
WHERE imp.rowid + 4 = impsectorsizekb.rowid) AS sectorsizekb,
(SELECT Substring(resulttext,(Charindex('KB ',resulttext)) + 3,
(Charindex(' IOs',resulttext)) - (Charindex('KB ',resulttext))- 3)
FROM dbo.sqlio_import impiopattern
WHERE imp.rowid + 4 = impiopattern.rowid) AS iopattern,
(SELECT Substring(resulttext,(Charindex('with ',resulttext)) + 5,
(Charindex(' outstanding',resulttext)) - (Charindex('with ',resulttext)) - 5)
FROM dbo.sqlio_import impiosoutstanding
WHERE imp.rowid + 5 = impiosoutstanding.rowid) AS iosoutstanding,
(SELECT REPLACE(CAST(resulttext AS NVARCHAR(50)),'buffering set to ',
'')
FROM dbo.sqlio_import impbuffering
WHERE imp.rowid + 6 = impbuffering.rowid) AS buffering,
(SELECT Substring(resulttext,(Charindex('size: ',resulttext)) + 6,
(Charindex(' for ',resulttext)) - (Charindex('size: ',resulttext)) - 9)
FROM dbo.sqlio_import impfilesizemb
WHERE imp.rowid + 7 = impfilesizemb.rowid) AS filesizemb,
(SELECT RIGHT(resulttext,(Len(resulttext) - 10))
FROM dbo.sqlio_import impios_sec
WHERE imp.rowid + 11 = impios_sec.rowid) AS ios_sec,
(SELECT RIGHT(resulttext,(Len(resulttext) - 10))
FROM dbo.sqlio_import impmbs_sec
WHERE imp.rowid + 12 = impmbs_sec.rowid) AS mbs_sec,
(SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM dbo.sqlio_import implatencyms_min
WHERE imp.rowid + 14 = implatencyms_min.rowid) AS latencyms_min,
(SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM dbo.sqlio_import implatencyms_avg
WHERE imp.rowid + 15 = implatencyms_avg.rowid) AS latencyms_avg,
(SELECT RIGHT(resulttext,(Len(resulttext) - 17))
FROM dbo.sqlio_import implatencyms_max
WHERE imp.rowid + 16 = implatencyms_max.rowid) AS latencyms_max
FROM dbo.sqlio_import imp
INNER JOIN dbo.sqlio_import impfulltest
ON imp.rowid + 20 = impfulltest.rowid
AND impfulltest.resulttext = ''
WHERE imp.rowid = imp.parameterrowid
/*AND (SELECT Substring(resulttext,(Charindex('size: ',resulttext)) + 6,
(Charindex(' for ',resulttext)) - (Charindex('size: ',resulttext)) - 9)
FROM dbo.sqlio_import impfilesizemb
WHERE imp.rowid + 7 = impfilesizemb.rowid) > 0 */
ORDER BY imp.parameterrowid
/* Empty out the ETL table */
DELETE dbo.sqlio_import
SET nocount off
GO
The script creates three things:
The stored procedure 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 2005
Open a new query and verify that the data was successfully imported by typing: SELECT * FROM dbo.SQLIO_Import
If there's no rows, something went wrong with the import process. Stop here and troubleshoot. Otherwise, execute the stored procedure to move the data into the reporting table, like this: EXECUTE [dbo].[USP_Import_SQLIO_TestPass]
'MyServerName'
,10
,15000
,'RAID 10'
,'2008/5/6'
,'IBM DS4800'
,'6.62'
,1024
,'NTFS'
,'64000'
The data will be parsed and inserted into the reporting table, which we can query: SELECT * FROM dbo.SQLIO_TestPass
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_TestPass 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:
|
本文转自 Fanr_Zh 博客园博客，原文链接：http://www.cnblogs.com/Amaranthus/archive/2011/09/16/2178747.html，如需转载请自行联系原作者
