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/prodtechnol/exchange/guides/StoragePerformance/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 Format		Test		IOs/sec	MBs/sec	IO Diff	MB Diff
256/64		Read 8KB random		4370	34	--	--
256/64 BA	Read 8KB random		4461	35	2%	2%
256/64		Read 64KB random 	3005	188	--	--
256/64 BA	Read 64KB random 	3359	210	12%	12%
256/64		Read 128KB random	2057	257	--	--
256/64 BA	Read 128KB random	2040	255	1%	1%
256/64		Read 256KB random	1303	326	--	--
256/64 BA	Read 256KB random	1303	326	0%	0%
							
256/64		Read 8KB sequential	7444	58	--	--
256/64 BA	Read 8KB sequential	7886	62	6%	6%
256/64		Read 64KB sequential	4017	251	--	--
256/64 BA	Read 64KB sequential	4242	265	6%	6%
256/64		Read 128KB sequential	2209	276	--	--
256/64 BA	Read 128KB sequential	2214	277	0%	0%
256/64		Read 256KB sequential	1312	328	--	--
256/64 BA	Read 256KB sequential	1311	328	0%	0%

WRITE							
Drive Format		Test		IOs/sec	MBs/sec	IO Diff	MB Diff
256/64		Write 8KB Random	2767	22	--	--
256/64 BA	Write 8KB Random	3085	24	11%	12%
256/64		Write 64KB Random	1360	85	--	--
256/64 BA	Write 64KB Random	1796	112	32%	32%
256/64		Write 128KB Random	800	100	--	--
256/64 BA	Write 128KB Random	933	117	16%	17%
256/64		Write 256KB Random	468	117	--	--
256/64 BA	Write 256KB Random	517	129	10%	10%
						
256/64		Write 8KB Sequential	5467	43	--	--
256/64 BA	Write 8KB Sequential	5592	44	2%	2%
256/64		Write 64KB Sequential	2130	133	--	--
256/64 BA	Write 64KB Sequential	2276	142	7%	7%
256/64		Write 128KB sequential	1119	140	--	--
256/64 BA	Write 128KB sequential	1138	142	2%	2%
256/64		Write 256KB sequential	641	160	--	--
256/64 BA	Write 256KB sequential	618	155	4%	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:\testfile.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 Format	Test			IOs/sec	MBs/sec
Default		Read 8KB random		1033	 8.1
Default		Read 64KB random 	 296	18.5
Default		Read 128KB random	 170	21.3
Default		Read 256KB random	  85	21.4
Default		Read 8KB sequential	1255	 9.8
Default		Read 64KB sequential	 622	38.9
Default		Read 128KB sequential	 322	40.2
Default		Read 256KB sequential	 164	40.9
 
WRITE							
Drive Format	Test			IOs/sec	MBs/sec
Default		Write 8KB Random	 260	1.9	
Default		Write 64KB Random	  88	5.5	
Default		Write 128KB Random	  44	5.5	
Default		Write 256KB Random	  21	5.2	
Default		Write 8KB Sequential	 314	2.5	
Default		Write 64KB Sequential	  88	5.5	
Default		Write 128KB sequential	  44	5.5	
Default		Write 256KB sequential	  21	5.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[].


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. []

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 Format	Test			IOs/sec	MBs/sec
Default		Read 8KB random		 656	 5.1
Default		Read 64KB random 	 258	16.1
Default		Read 128KB random	 149	18.6
Default		Read 256KB random	  82	20.4
Default		Read 8KB sequential	3246	25.4
Default		Read 64KB sequential	 378	23.6
Default		Read 128KB sequential	 188	23.5
Default		Read 256KB sequential	  94	23.5
 
WRITE
Drive Format	Test			IOs/sec	MBs/sec
Default		Write 8KB Random	1567	12.2
Default		Write 64KB Random	 809	50.5
Default		Write 128KB Random	 486	60.7
Default		Write 256KB Random	 333	83.2
Default		Write 8KB Sequential	7112	55.6
Default		Write 64KB Sequential	1889   118.1
Default		Write 128KB sequential	 977   122.1
Default		Write 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 Format Test IOs/sec MBs/sec
Default Read 8KB random 1523 11.90
Default Read 64KB random 853 53.31
Default Read 128KB random 586 73.21
Default Read 256KB random 365 91.19
Default Read 8KB sequential 17541 137.04
Default Read 64KB sequential 3130 195.63
Default Read 128KB sequential 1774 221.70
Default Read 256KB sequential 907 226.66

WRITE
Drive Format Test IOs/sec MBs/sec
Default Write 8KB Random 350 2.73
Default Write 64KB Random 204 12.73
Default Write 128KB Random 153 19.13
Default Write 256KB Random 114 28.48
Default Write 8KB Sequential 14431 112.74
Default Write 64KB Sequential 2468 154.28
Default Write 128KB sequential 1265 158.17
Default Write 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:\MSSQL\Data\SQLIOSim_Data_01.mdx with 2 threads (0-1) using mask 0x0 (0)
file J:\MSSQL\Data\SQLIOSim_Data_02.mdx with 2 threads (2-3) using mask 0x0 (0)
file H:\MSSQL\DATA\SQLIOSim_Log_01.ldx with 2 threads (4-5) using mask 0x0 (0)



using specified size: 10240 MB for file: J:\MSSQL\Data\SQLIOSim_Data_01.mdx
using specified size: 10240 MB for file: J:\MSSQL\Data\SQLIOSim_Data_02.mdx
using specified size: 5120 MB for file: H:\MSSQL\DATA\SQLIOSim_Log_01.ldx



6 threads reading for 360 secs from files J:\MSSQL\Data\SQLIOSim_Data_01.mdx
, J:\MSSQL\Data\SQLIOSim_Data_02.mdx 
, H:\MSSQL\DATA\SQLIOSim_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 8x15=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 sector IOs/sec MBs/sec
Read - Random 2096.15 16.38
Read - Sequential 3651.36 28.53
Write - Random 1985.08 15.51
Write - Sequential 1914.68 14.96

SQLIO 32k sector IOs/sec MBs/sec
Read - Random 1653.85 51.68
Read - Sequential 2736.05 85.50
Write - Random 1125.63 35.18
Write - Sequential 1337.12 41.79

SQLIO 64k sector IOs/sec MBs/sec
Read - Random 1280.91 80.06
Read - Sequential 1889.55 118.10
Write - Random 724.24 45.27
Write - Sequential 889.01 55.56

Dual 3.0Ghz Xeon
Dual 2GB FC HBAs
IBM DS4300 SAN
RAID 10 with 10 74GB 15K

[SQLIO PARAM]
e:\testfile.dat 2 0x0 8192

SQLIO 8k sector IOs/sec MBs/sec
Read - Random 2096.15 16.38
Read - Sequential 3651.36 28.53
Write - Random 1985.08 15.51
Write - Sequential 1914.68 14.96

SQLIO 32k sector IOs/sec MBs/sec
Read - Random 1653.85 51.68
Read - Sequential 2736.05 85.50
Write - Random 1125.63 35.18
Write - Sequential 1337.12 41.79

SQLIO 64k sector IOs/sec MBs/sec
Read - Random 1280.91 80.06
Read - Sequential 1889.55 118.10
Write - Random 724.24 45.27
Write - Sequential 889.01 55.56