One of probably hundreds of SQL scripts out there. This is what I made and use… and I like it.
There are a lot of other scripts out there that are available and even an SQLIO GUI. Here are a few I looked at myself.
SQLIOToolSet
SQLIO GUI (screenshot)
SQLIO.Scripts
My script is a basic DOS batch file that automates collecting statistics on a few different drives when you’re testing. You need to make sure you have created the %TESTFILE% on the root of the drives you want to test. I create, and I suspect most others do as well, a testfile.dat on the root of each drive I test.
You’ll need to create this before running the script or it will break!
If you want to extend this script, it’s easy. All the testing is put into a function called RUN_BENCHMARK which is called and passed a drive letter argument. So if you want to add more tests, feel free. Just mimic the existing code in that stanza and you should be ok.
@echo off setlocal : This script will benchmark a HDD using SQLIO and store results in the : RESULTS_LOGFILE_NAME%_%X.txt where X is the drive letter(s) benchmarked : in the current path and TIME is the timestamp the script was run. : YOU MUST HAVE CREATED THE %TESTFILE% ON EACH DRIVE. : BUGFIX: Set time; strip colon and spaces. Was erroring on on spaces. set STRTIME=%TIME::=% set STRTIME=%STRTIME: =% : Set duration of each test in seconds set DURATION=30 : Set the buffering. N=none, Y=all, H=hdwr, S=sfwr set BUFFERING=H : Set latencies from (S=system, P=processor) timer set LATENCY=S : Filename of test file created already. You can specify a full path if needed. If spaces : exist in path or name, use quotes. set TESTFILE=testfile.dat : The log file name (prepend) - log file is stored in the same location this script is run : from. You could specifiy a path if you wanted. set RESULTS_LOGFILE_NAME=%COMPUTERNAME%_%STRTIME%_results : Drive(s) to run the tests on. Separate each drive with a comma. set TEST_DRIVES=E,F,G : Set the log to either 1-combine or 0-separate. If separated, a file will be generated : for each TEST_DRIVES disk. set COMBINE_LOG=1 : I should do some error checking, but I'm not going to. : Future plans: IF [%TEST_DRIVES%]==[] call:MissingDrive & goto EOF cls color 1F echo Benchmark of drive(s) %TEST_DRIVES% start: %DATE% %TIME% echo. echo. echo This will take some time. The background will become green once the echo benchmarking completes. Go grab some coffee and read up on the latest echo news of the world. echo. echo. for /D %%D in (%TEST_DRIVES%) do (call:RUN_BENCHMARK %%D) GOTO END :RUN_BENCHMARK IF [%1]==[] call:MissingDrive IF [%COMBINE_LOG%]==[1] ( set APPEND=COMBINED ) ELSE ( set APPEND=%1 ) echo Benchmark [%1] begin: %DATE% %TIME% echo [%1] RANDOM WRITES (64K) sqlio -d%1 -B%BUFFERING% -kW -frandom -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] RANDOM READS (64K) sqlio -d%1 -B%BUFFERING% -kR -frandom -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o2 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o4 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o8 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o16 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o32 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o64 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o128 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] SEQUENTIAL READS (64K) sqlio -d%1 -B%BUFFERING% -kR -fsequential -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o2 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o4 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o8 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o16 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o32 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o64 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o128 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] SEQUENTIAL WRITES (8K blocks to mimic SQL Logfiles) sqlio -d%1 -B%BUFFERING% -kW -fsequential -t1 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t2 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t4 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o2 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o4 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o8 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o16 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o32 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o64 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o128 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo Benchmark [%1] complete: %DATE% %TIME% echo. echo. GOTO EOF :MissingDrive color CF echo Missing drive. Specify %%TEST_DRIVE%% variable please. goto EOF :END color 2F echo Benchmark of drive(s) [%TEST_DRIVES%] completed: %DATE% %TIME% endlocal :EOF
