SQL Server Performance Forum – Threads Archive

SQLIO testing

Group, I’d been waiting for some new hardware to arrive when this thread (http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8210) was active. I was extremely interested in seeing how things would compare, and I think actual numbers are too hard to come by to separate out marketing fluff; so I’m sharing my testing. I think a forum sticky of this test would be very useful for everyone to post to. Also, note that the ‘BA’ line is after I formatted the volume with Diskpart, aligning the disk as recommended (http://www.microsoft.com/technet/pr…nce/0e24eb22-fbd5-4536-9cb4-2bd8e98806e7.mspx). There was a notable performance improvement, especially in the 64KB random write test. Let me know if there’s anything missing, or something else you’d like to know. [HARDWARE]
HP Proliant DL385
2x Opteron 875 (Dual Core)
16GB PC3200 RAM
HP SA-6404 RAID Controller; 4ch 256MB Cache; 50/50 Read/Write setting
Win2k3 EE 64-bit SP1 [DISK ARRAY]
2x MSA-30; Dual Channel; U320
28x 146.8GB 15k U320 (Maxtor Atlas 15K II) [TESTED VOLUME]
14x RAID 10 array spanning 2 U320 channels
Stripe Size = 256KB
NTFS Allocation block size = 64KB [RESULTS] READ
Drive FormatTestIOs/secMBs/secIO DiffMB Diff
256/64Read 8KB random437034—-
256/64 BARead 8KB random4461352%2%
256/64Read 64KB random 3005188—-
256/64 BARead 64KB random 335921012%12%
256/64Read 128KB random2057257—-
256/64 BARead 128KB random20402551%1%
256/64Read 256KB random1303326—-
256/64 BARead 256KB random13033260%0% 256/64Read 8KB sequential744458—-
256/64 BARead 8KB sequential7886626%6%
256/64Read 64KB sequential4017251—-
256/64 BARead 64KB sequential42422656%6%
256/64Read 128KB sequential2209276—-
256/64 BARead 128KB sequential22142770%0%
256/64Read 256KB sequential1312328—-
256/64 BARead 256KB sequential13113280%0% WRITE
Drive FormatTestIOs/secMBs/secIO DiffMB Diff
256/64Write 8KB Random276722—-
256/64 BAWrite 8KB Random30852411%12%
256/64Write 64KB Random136085—-
256/64 BAWrite 64KB Random179611232%32%
256/64Write 128KB Random800100—-
256/64 BAWrite 128KB Random93311716%17%
256/64Write 256KB Random468117—-
256/64 BAWrite 256KB Random51712910%10% 256/64Write 8KB Sequential546743—-
256/64 BAWrite 8KB Sequential5592442%2%
256/64Write 64KB Sequential2130133—-
256/64 BAWrite 64KB Sequential22761427%7%
256/64Write 128KB sequential1119140—-
256/64 BAWrite 128KB sequential11381422%2%
256/64Write 256KB sequential641160—-
256/64 BAWrite 256KB sequential6181554%4%
[SQLIO Script]
sqlio -kW -s360 -frandom -o8 -b8 -LS -Fparam.txt
timeout /T 60
sqlio -kW -s360 -frandom -o8 -b64 -LS -Fparam.txt
timeout /T 60
sqlio -kW -s360 -frandom -o8 -b128 -LS -Fparam.txt
timeout /T 60
sqlio -kW -s360 -frandom -o8 -b256 -LS -Fparam.txt
timeout /T 60 sqlio -kW -s360 -fsequential -o8 -b8 -LS -Fparam.txt
timeout /T 60
sqlio -kW -s360 -fsequential -o8 -b64 -LS -Fparam.txt
timeout /T 60
sqlio -kW -s360 -fsequential -o8 -b128 -LS -Fparam.txt
timeout /T 60
sqlio -kW -s360 -fsequential -o8 -b256 -LS -Fparam.txt
timeout /T 60 sqlio -kR -s360 -frandom -o8 -b8 -LS -Fparam.txt
timeout /T 60
sqlio -kR -s360 -frandom -o8 -b64 -LS -Fparam.txt
timeout /T 60
sqlio -kR -s360 -frandom -o8 -b128 -LS -Fparam.txt
timeout /T 60
sqlio -kR -s360 -frandom -o8 -b256 -LS -Fparam.txt
timeout /T 60 sqlio -kR -s360 -fsequential -o8 -b8 -LS -Fparam.txt
timeout /T 60
sqlio -kR -s360 -fsequential -o8 -b64 -LS -Fparam.txt
timeout /T 60
sqlio -kR -s360 -fsequential -o8 -b128 -LS -Fparam.txt
timeout /T 60
sqlio -kR -s360 -fsequential -o8 -b256 -LS -Fparam.txt
[SQLIO PARAM] f: estfile.dat 4 0x0 8192

i really dint understand wat it was abt…sir iam a newbie can u plz help me understand this…it seems very interesting Regards
Suleman
Hyderabad

Just for comparision I run this test on a system with much older hardware, a Compaq DL380 G1 with the built-in array controller. [HARDWARE]
Compaq Proliant DL380 G1
2x Pentium III 933 MHz
2560MB RAM
Integrated Smart Array Controller; 8MB Cache; 100/0 Read/write setting (no write-back cache exist)
Windows 2003 Standard 32-bit SP1 [DISK ARRAY]
4x 36GB Compaq Ultra2 SCSI [TESTED VOLUME]
4x 36GB RAID 5
Stripe Size = default (32KB)
NTFS Allocation block size = default (4KB) [RESULTS] READ
Drive FormatTestIOs/secMBs/sec
DefaultRead 8KB random1033 8.1
DefaultRead 64KB random 29618.5
DefaultRead 128KB random 17021.3
DefaultRead 256KB random 8521.4
DefaultRead 8KB sequential1255 9.8
DefaultRead 64KB sequential 62238.9
DefaultRead 128KB sequential 32240.2
DefaultRead 256KB sequential 16440.9 WRITE
Drive FormatTestIOs/secMBs/sec
DefaultWrite 8KB Random 2601.9
DefaultWrite 64KB Random 885.5
DefaultWrite 128KB Random 445.5
DefaultWrite 256KB Random 215.2
DefaultWrite 8KB Sequential 3142.5
DefaultWrite 64KB Sequential 885.5
DefaultWrite 128KB sequential 445.5
DefaultWrite 256KB sequential 215.2

I’m not really sure what you are asking here. You seem to have a decent hardware platform. If you are trying to see what it is capable of you need to test it in business units which equate to your actual business. Benchmarking, stress testing and performance analysis don’t have to be difficult. Use a tool like DBPerform (ashgrovesoftware.com) and set up transactions which approximate your workload. Set up PerfMon to monitor the basic things (pages/sec, processors, etc) then add PhsicalDisk/Avg Queue Length for each of your arrays…’physical disks’ to the OS. After you have perfmon set up start your package of transactions and then "crank up the volume" (TPM) on individual transactions and watch the monitor to see where "queues" develop in the system and SQL Server. This will show you 1) how much activity your system can handle until you see degredation, and 2) what server components you will need to be ready to upgrade ahead of the business growth curve…if you like smooth growth versus crisis mode.

It’s not really a question thread. It’s just a thread to post the results of the scripts in the original post to compare systems.

I begin to tougth that you work for ashgrovesoftware[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Responding to SQLGeek, this is most definately not a tuning thread. Read the articles on this site for good tips on that. I just saw that many people were posting about what performance levels they could expect with their hardware and configurations. Typically, after buying it. I also see a large amount of vendors dodging real performance numbers and instead trying to pass off vague expected performance levels. The purpose of this thread is the full disclosure of a certain hardware platform using a simple, easily accessible, repeatable test. If you want to contribute to the thread, post your own system numbers. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] thanks.

Here is another one: [HARDWARE]
Dell PowerEdge 6650
2x Xeon MP 2.7 GHz
2048 MB RAM
DELL PERC 3/DC RAID Controller; 128MB cache; Write-back cache option
Windows 2003 Standard 32-bit SP1 [DISK ARRAY]
4x 36GB SCSI, Ultra/320, 15000 RPM [TESTED VOLUME]
4x 36GB RAID 10 array
Stripe Size = 64KB
NTFS Allocation block size = 4KB [RESULTS]
READ
Drive FormatTestIOs/secMBs/sec
DefaultRead 8KB random 656 5.1
DefaultRead 64KB random 25816.1
DefaultRead 128KB random 14918.6
DefaultRead 256KB random 8220.4
DefaultRead 8KB sequential324625.4
DefaultRead 64KB sequential 37823.6
DefaultRead 128KB sequential 18823.5
DefaultRead 256KB sequential 9423.5 WRITE
Drive FormatTestIOs/secMBs/sec
DefaultWrite 8KB Random156712.2
DefaultWrite 64KB Random 80950.5
DefaultWrite 128KB Random 48660.7
DefaultWrite 256KB Random 33383.2
DefaultWrite 8KB Sequential711255.6
DefaultWrite 64KB Sequential1889 118.1
DefaultWrite 128KB sequential 977 122.1
DefaultWrite 256KB sequential 494 123.5

[HARDWARE]
HP Proliant DL385
2x Opteron 275 (2.2GHz dual core)
8GB PC3200
HP Smart Array P600 controller; 2ch 256MB Cache; 75/25 Read/Write
Windows 2003 Server R2 Standard x64 [DISK ARRAY]
8x72GB SAS, 10000 RPM [TESTED VOLUME]
4x72GB RAID 5 array
Stripe Size = 128KB
NTFS Allocation block size = 32KB [RESULTS]
READ
Drive FormatTestIOs/secMBs/sec
DefaultRead 8KB random 1523 11.90
DefaultRead 64KB random 853 53.31
DefaultRead 128KB random 586 73.21
DefaultRead 256KB random 365 91.19
DefaultRead 8KB sequential 17541 137.04
DefaultRead 64KB sequential 3130 195.63
DefaultRead 128KB sequential 1774 221.70
DefaultRead 256KB sequential 907 226.66 WRITE
Drive FormatTestIOs/secMBs/sec
DefaultWrite 8KB Random 350 2.73
DefaultWrite 64KB Random 204 12.73
DefaultWrite 128KB Random 153 19.13
DefaultWrite 256KB Random 114 28.48
DefaultWrite 8KB Sequential 14431 112.74
DefaultWrite 64KB Sequential 2468 154.28
DefaultWrite 128KB sequential 1265 158.17
DefaultWrite 256KB sequential 169 42.15 Tried 64 KB stripe size and 64 KB block size, without noticable results.
Tried partition alignment 32 & 64 without noticable results.
Will post one shortly with 4 disks raid 0+1

DELL 6850 Quad Xeon 3.4ghz w/4GB RAM. EMC Clariion CX-700. I’ll have to update the LUN info when I get it.
Operation: IOs/sec: MBs/sec:
—————– ——– ——–
8k random write: 14884.96 116.28
64k random write: 1573.03 98.31
128k random write: 1110.79 138.84
256k random write: 722.48 180.62
————————————
8k seq. write: 16545.36 129.26
64k seq. write: 3142.00 196.37
128k seq. write: 1573.62 196.70
256k seq. write: 787.21 196.80
————————————
8k random read: 5776.05 45.12
64k random read: 2857.16 178.57
128k random read: 1567.23 195.90
256k random read: 787.22 196.80
————————————
8k seq. read: 9200.54 71.87
64k seq. read: 2805.23 175.32
128k seq. read: 1506.95 188.36
256k seq. read: 783.07 195.76 Maximum Latency:
———————-
256k seq. read @ 728ms File & CPU setup:
——————————————————————————— file J:MSSQLDataSQLIOSim_Data_01.mdx with 2 threads (0-1) using mask 0x0 (0)
file J:MSSQLDataSQLIOSim_Data_02.mdx with 2 threads (2-3) using mask 0x0 (0)
file H:MSSQLDATASQLIOSim_Log_01.ldx with 2 threads (4-5) using mask 0x0 (0) using specified size: 10240 MB for file: J:MSSQLDataSQLIOSim_Data_01.mdx
using specified size: 10240 MB for file: J:MSSQLDataSQLIOSim_Data_02.mdx
using specified size: 5120 MB for file: H:MSSQLDATASQLIOSim_Log_01.ldx 6 threads reading for 360 secs from files J:MSSQLDataSQLIOSim_Data_01.mdx
, J:MSSQLDataSQLIOSim_Data_02.mdx
, H:MSSQLDATASQLIOSim_Log_01.ldx enabling multiple I/Os per thread with 8 outstanding

the issue with SQLIO is that the test size is only 3GB
so on a san, the entire data set is in the san cache notice how quickly the results saturate at 195MB/sec
that is the limit for a single 2Gbit/sec FC server to -san memory
if server to san to disk, then this would be about 170MB/sec if this were random IOPS to disk, there would only be a small drop off in IOPS from 8K to 64K

Thanks for your input Joe, as always it’s valued. It was quite apparant that ~200 is the max for the CX700, but I don’t have the LUN config still so I don’t know if this is going to be the average no matter what, or if there are better configurations to be had. Hopefully I’ll get to exersize more control over the Symmetrix we’ve recently purchased as the CX700 is already pretty well carved up and doesn’t lend to testing well. I’ll post more numbers and with LUN specs as I can and get to work with the SANs more directly.

the CX700 maxed out, should be able to do 1.3GB/sec
that is, with 8 2Gbit/s FC ports, and 8×15=120 disks so i really think the 200 you are seeing is in-memory over 1 Gb/s port one of several reasons i try to stay away from sans is that the guys responsible fo configuring the san just cannot figure out that you need to spread load over multiple controllers and as many disks as possible
every time i talk to one, they seem infatuated with the cache
no one bothers to see check to see that performance benchmarks are done with the cache turned off
it gets in the way of good performance anyways, always verify random IO capability over a large data set,
use my scripts in the SQL 2005 HW section I like to get sequential IO in to 1GB/sec range so that a large table scan does not shut down my transactions

SQLIO 8k sectorIOs/secMBs/sec
Read – Random2096.1516.38
Read – Sequential3651.3628.53
Write – Random 1985.0815.51
Write – Sequential1914.6814.96 SQLIO 32k sectorIOs/secMBs/sec
Read – Random1653.8551.68
Read – Sequential2736.0585.50
Write – Random 1125.6335.18
Write – Sequential1337.1241.79 SQLIO 64k sectorIOs/secMBs/sec
Read – Random1280.9180.06
Read – Sequential1889.55118.10
Write – Random 724.2445.27
Write – Sequential889.0155.56

Dual 3.0Ghz Xeon
Dual 2GB FC HBAs
IBM DS4300 SAN
RAID 10 with 10 74GB 15K [SQLIO PARAM]
e: estfile.dat 2 0x0 8192 SQLIO 8k sectorIOs/secMBs/sec
Read – Random2096.1516.38
Read – Sequential3651.3628.53
Write – Random 1985.0815.51
Write – Sequential1914.6814.96 SQLIO 32k sectorIOs/secMBs/sec
Read – Random1653.8551.68
Read – Sequential2736.0585.50
Write – Random 1125.6335.18
Write – Sequential1337.1241.79 SQLIO 64k sectorIOs/secMBs/sec
Read – Random1280.9180.06
Read – Sequential1889.55118.10
Write – Random 724.2445.27
Write – Sequential889.0155.56

Dual 3.0Ghz Xeon
Dual 4GB FC HBAs
Hitachi Tagmastore USP600 SAN [SQLIO PARAM]
e: estfile.dat 2 0x0 8192 SQLIO 8k sectorIOs/secMBs/sec
Read – Random16138.50126.08
Read – Sequential13157.22102.79
Write – Random 9095.8071.06
Write – Sequential15734.96122.93 SQLIO 32k sectorIOs/secMBs/sec
Read – Random15668.74489.65
Read – Sequential7213.68225.43
Write – Random 6414.58200.46
Write – Sequential8402.71262.58 SQLIO 64k sectorIOs/secMBs/sec
Read – Random8654.17540.89
Read – Sequential4751.89296.99
Write – Random 5403.13337.70
Write – Sequential5556.33347.27

Im trying to figure out how to use the tool.
I have documentation at hand i understand all the -K etc… sqlio -KW -S10 -Frandom -08 -b8 -LS -F Param.txt
The param.txt is as follows
c:sqlio_test.dat 4 0x0 100 (default) If my database files are on the SAN say Drive R how do i change the script R:mytestdatabase.mdf i do not have dat files. the 4 is the CPU and 0x0 is default The 100 is (Size of test file in MB) Ideally this should be large enough so that the test file will be larger than any cache resident on the SAN (or raid controller). Two or four times teh size of any cache allocated is a good rule of thumb to follow: (The 100 im not sure if this the size of mytestdatabase.mdf) or ?
and how to get dat file when the files are MDF Thanks

]]>