Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQLIO Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 8:10 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
Hi, I'm trying to bench mark the disk IO for my PROD SQL server against SAN.

We have 64 GB Array cache. I have created 100 GB test file using sqlio and ran a sample test as using below code:

The param.txt has the code:

e:\testfile.dat 2 0x0 102400


C:\Program Files (x86)\SQLIO>sqlio -kW -s10 -fsequential -t8 -o8 -b8 -LS -Fpara
.txt timeout /T 10
sqlio v1.5.SG
using system counter for latency timings, 14318180 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 10 secs to file e:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
size of file e:\testfile.dat needs to be: 107374182400 bytes
current file size: 104857600 bytes
need to expand by: 107269324800 bytes
expanding e:\testfile.dat ... done.
using specified size: 102400 MB for file: e:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3932.40
MBs/sec: 30.72

latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 3
Max_Latency(ms): 14
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 0 1 58 34 5 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

C:\Program Files (x86)\SQLIO>


Question:

Are the above IOS/sec and MBs/sec values good?

I will run different tests using the script provided by Brent at http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat
sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat
sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat
sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

but before that just wanted to know if we get any idea by seeing the above disk IO values.

Thanks,
Gary
Post #1499144
Posted Friday, September 27, 2013 12:38 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
Hi,

I ran a sample SQLIO stress test using the below parameters in a batch file called Test.bat files.

I'm just using 100 MB file to start.for the next run, I'm going to use 100 GB file as our SAN array cache is 64 GB

sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat
sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat
sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat
sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat


Here are the results: Based on below results, do you see any issue on IO bottle neck?


C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 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 current size: 100 MB for file: E:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 4669.30
MBs/sec: 36.47
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 13
Max_Latency(ms): 145

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 0 0 0 0 0 0 0 0 0 0 1 31 38 18 6 3 1 1 0 0 0 0 0 0

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads reading for 120 secs from 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 current size: 100 MB for file: E:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 6535.77
MBs/sec: 51.06
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 9
Max_Latency(ms): 108

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 0 0 0 0 0 0 0 14 60 17 4 2 1 0 0 0 0 0 0 0 0 0 0 0

C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads writing for 120 secs to file E:\TestFile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: E:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2032.83
MBs/sec: 127.05
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 30
Max_Latency(ms): 331

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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
8 threads reading for 120 secs from file E:\TestFile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: E:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2477.82
MBs/sec: 154.86
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 25
Max_Latency(ms): 278

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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 6 15 77
Post #1499548
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse