Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLIO


SQLIO

Author
Message
gary1
gary1
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 2246
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
gary1
gary1
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 2246
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search