Skip to main content

SAN performance testing using SQLIO

Introduction


This document describes how to use Microsoft’s SQLIO to test disk/SAN performance. It is biased towards SQL Server – which uses primarily 64KB and 8KB data pages so I am running the tests using those cluster sizes, however, other sizes can be specified.
SQLIO is a command line tool with no GUI so you need to open a command prompt at C:\Program Files (x86)\SQLIO after you have installed it.

Configuration

First of all edit param.txt so that you create the test file we will be using. The file needs to be bigger than the combined RAID and on-board disk caches. In this case we are using a 50GB file.

The “2” refers to the number of threads to use when testing, you don’t need to change this now. The “0x0” value indicates that all CPUs should be used, which you probably don’t want to change either, “#” is a comment. The only part you may want to change is 51200 (50GB) and the drive letter.
After saving param.txt, run the command below at the command line in the same directory where SQLIO is installed in order to create the test file, this will save time later when you run the tests proper. Depending on the speed of the hardware and size of the test file this could take 5 – 20 minutes to complete:
sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt

Parameters

The parameters for the command you will be running are:
-kW means writes, use this or:
-kR means reads
-t2 means two threads (increase as required. You can have more threads than CPUs. Max 256)
-s120 means test for 120 seconds
-dE means drive letter E
-o1 means one outstanding request (increase as required)
-frandom means random access, use this or:
-fsequential means sequential access
-b64 means 64kb data clusters to process (useful for testing for SQL Server), use this or:
-b8 means 8kb data clusters to process (useful for testing for SQL Server)
-BH means use the disk cache (hardware buffering). We would normally use this.
-BN (default) means use file system cache (probably don’t want to use this value to get a realistic result)
-LS means show disk latency from the system (use this). Especially on SMP machines
-LP means show from the process (probably don’t want to use this)
/T means timeout in seconds before executing the next command, if one follows.

To give you an idea of how long all the tests will take run, copy the following into a batch file called TestSAN.bat. It should run for 8 minutes.

sqlio -kW -t8 -s30 -dG -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o8 -frandom -b64 -BH -LS Testfile.dat
 
sqlio -kW -t8 -s30 -dG -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o8 -fsequential -b64 -BH -LS Testfile.dat
 
sqlio -kW -t8 -s30 -dG -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o8 -frandom -b8 -BH -LS Testfile.dat
 
sqlio -kW -t8 -s30 -dG -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s30 -dG -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s30 -dG -o8 -fsequential -b8 -BH -LS Testfile.dat 
Execute it via a command prompt at C:\Program Files (x86)\SQLIO
TestSAN.bat > SANTestResults.txt

Reading and analysing the results

Contents of SANTestResults.txt
C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
parameter file used: param.txt
 file e:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file e:\testfile.dat
 using 8KB random IOs
 enabling multiple I/Os per thread with 8 outstanding
 buffering set to use hardware disk cache (but not file cache)
using specified size: 51200 MB for file: e:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec:  1580.91
MBs/sec:    12.35
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 9
Max_Latency(ms): 2927
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%:  0 19 62 14  2  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  2

MBs/sec: Needs to be as high as possible
IOs/sec: As high as possible
Min_Latency(ms):As low as possible
Avg_Latency(ms): As low as possible
Max_Latency(ms): As low as possible

Reads should have higher throughput (MBs/sec) and IOPS (IOs/sec) than writes. Writes should have high latency rates than reads. Larger cluster sizes should have higher latency but maybe higher throughput.
The first row of the histogram (ms) provides groupings for 0ms through 23ms. The 24+ group includes operations that took 24ms and more. The second row in the histogram (%) provides a percentage of I/Os that completed in that time. For example, in the 4ms group 2% of the total I/Os completed running.
Look for where a drive plateaus for random reads, how many outstanding requests it takes to reach maximum capacity for random writes, or where sequential I/Os plateau compared to random ones.

The full test batch file

The batch file below will create a lot of logs. It may be more convenient to import them into a database and perform some aggregate queries on the results for easier analysis.

echo ******** Test Start ************
echo %date%
echo %time%
echo.
sqlio -kW -t8 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kW -t8 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kW -t16 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kW -t16 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kW -t32 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kW -t32 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kW -t64 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kW -t64 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kR -t8 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kR -t8 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kR -t16 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kR -t16 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kR -t32 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kR -t32 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
  
sqlio -kR -t64 -s120 -dE -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o8 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o16 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o32 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o64 -frandom -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o128 -frandom -b64 -BH -LS Testfile.dat
  
sqlio -kR -t64 -s120 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o2 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o4 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o8 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o16 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o32 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o64 -fsequential -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o128 -fsequential -b8 -BH -LS Testfile.dat
:---------------------------------------------------------------
sqlio -kW -t8 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kW -t8 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t8 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kW -t16 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kW -t16 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t16 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kW -t32 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kW -t32 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t32 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kW -t64 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kW -t64 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kW -t64 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kR -t8 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kR -t8 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t8 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kR -t16 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kR -t16 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t16 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kR -t32 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kR -t32 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t32 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
  
sqlio -kR -t64 -s120 -dE -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o128 -fsequential -b64 -BH -LS Testfile.dat
  
sqlio -kR -t64 -s120 -dE -o1 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o2 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o4 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o8 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o16 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o32 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o64 -frandom -b8 -BH -LS Testfile.dat
sqlio -kR -t64 -s120 -dE -o128 -frandom -b8 -BH -LS Testfile.dat
echo.
echo %date%
echo %time%
echo ******** Test  Finish ************
echo %date%
echo %time%
echo ***********************************

Comments

Popular posts from this blog

Generate scripts to attach multiple databases

There is a handy little "by product", if you like, when running queries which means you can quickly generate scripts to do different things. Below is an example of generating multiple "attach" commands that you can copy from the results pane into the main SSMS window for execution. I have found this very handy in the past:


SELECT 'CREATE DATABASE ['+name+'] ON ( FILENAME = N''F:\MSSQL\Data\'+name+'.mdf'' ), ( FILENAME = N''E:\MSSQL\Log\'+name+'_log.ldf'' )  FOR ATTACH GO ' FROMmaster.dbo.sysdatabases WHEREnamenotin('master','msdb','model','tempdb')
ORDERBYname

How to move the Microsoft Assessment and Planning Toolkit (MAP) database to a different drive

The Microsoft Assessment and Planning Toolkit (MAP) is a very useful tool for scanning your network to find instances of SQL Server plus all manner of detailed information about the installed product, OS and hardware it sits on.


<Click image to enbiggen>
There is an issue with it the database it uses to store the data it collects, however. Assuming you don't have an instance called MAPS on your server, the product will install using LocalDB (a cut down version of SQL Server Express) and puts the databases on your C: drive. If you then scan a large network you could easily expand the database to 10GB which may cause issues on a server when that drive is often one of the smallest. However, there is a simple solution: connect to LocalDB using Management Studio, detach the databases, move to a different drive, set permissions on the new location if required and reattach the database. How do you connect to LocalDB? Here you go:

Connect to (localdb)\MAPTOOLKIT


The databases I move…

The Purge SQL Agent Job for MDW takes a long time to complete

I use the dbWarden alerts to inform me if a SQL job is taking longer to complete than normal and I got one this morning:
I noticed by looking at the history this purge job was gradually taking longer and longer to complete each day since I installed it again (see mylast post on this):
RunDate RunTime Duration ExecutionStatus JobName 04/10/2013 02:00:01 00:00:45 Succeded mdw_purge_data_[MDW] 05/10/2013 02:00:00 00:13:27 Succeded mdw_purge_data_[MDW] 06/10/2013 02:00:00 00:17:03 Succeded mdw_purge_data_[MDW] 07/10/2013 02:00:01