SQLIOSIM results on a SAN to be used for SQL

hi
i have ran sqliosim on a new server, and gathered some results and would appreciate a 2nd opinion on them
the C drive is the os and is a virtual machine on another system, so its E,F and G i am more interested as they are on a new SAN

all the drives are 300GB 15k rpm
E and G are both raid 1 and 2 discs each
F is 6 discs in raid 10
********** Final Summary for file C:\sqliosim.mdx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 70, Number of times IO throttled = 663, IO request blocks = 16, Reads = 15886, Scatter Reads = 31677, Writes = 539, Gather Writes = 25479, Total IO Time (ms) = 8998320

********** Final Summary for file E:\sqliosim.mdx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 15, Number of times IO throttled = 1672, IO request blocks = 39, Reads = 11935, Scatter Reads = 29518, Writes = 645, Gather Writes = 25870, Total IO Time (ms) = 17795599

********** Final Summary for file F:\sqliosim.mdx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 15, Number of times IO throttled = 1412, IO request blocks = 41 , Reads = 10919, Scatter Reads = 28833, Writes = 451, Gather Writes = 25457, Total IO Time (ms) = 8311949

********** Final Summary for file G:\sqliosim.mdx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 14, Number of times IO throttled = 1079, IO request blocks = 153, Reads = 12575, Scatter Reads = 29844, Writes = 981, Gather Writes = 26168, Total IO Time (ms) = 8284344

********** Final Summary for file G:\sqliosim.ldx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 0, Number of times IO throttled = 0, IO request blocks = 8, Reads = 0, Scatter Reads = 0, Writes = 11031, Gather Writes = 0, Total IO Time (ms) = 199786

********** Final Summary for file F:\sqliosim.ldx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 1, Number of times IO throttled = 0, IO request blocks = 8   Reads = 0, Scatter Reads = 0, Writes = 10826, Gather Writes = 0, Total IO Time (ms) = 170139

********** Final Summary for file E:\sqliosim.ldx **********
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 1, Number of times IO throttled = 14, IO request blocks = 8  , Reads = 0, Scatter Reads = 0, Writes = 10174, Gather Writes = 0, Total IO Time (ms) = 195850

Open in new window

malraffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BigSchmuhCommented:
Some theoric numbers for 15k HDD :
-5-6ms avg access time
-IOPS capability : about 180 with no queue depth up to 400 (>=32 queue depth)
-sequential throughput : 80 (inner) to 140MB/s (outer zone)

E and G are 2x 15k hdd in RAID 1
==> I am surprised by the very different "IO request blocks" for those, I was expecting something closer to the 400 IOPS stated above
==> Please check the queue depth values on both the SAN's LUN and your server's HBA

F are 6x 15k hdd in RAID 10
==> Same pb than above

Although, I guess your partition are NOT aligned (repartition using DiskPart.exe MS utility and a 1MB offset)
Last : any idea of the stripe size used in the LUN ? It should be kept close to 64KB to avoid loosing too much cache by a MSSQL which issues 8KB page size io requests
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
malraffAuthor Commented:
hi big schmuh

ill have to ask the guy that setup the SAN, i just requested the raid setups etc
with these stats could you see potential issues?
0
BigSchmuhCommented:
Your F drive is not at the expected performance level. It should deliver about 2.5x more IO than the E or G ones !

A very low query depth can explain that by itself.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

malraffAuthor Commented:
are you basing that on
********** Final Summary for file F:\sqliosim.mdx **********
 Reads = 10919, Scatter Reads = 28833, Writes = 451, Gather Writes = 25457


 compared to the other drives?
0
BigSchmuhCommented:
Yes + The IO Request blocks should compare to the IOPS expectations for 6x 15k HDD in RAID 10 (500 may be a nice minimum...compared to the current "41")
0
malraffAuthor Commented:
hi bigschmuh
ok if you say the min of 500 woul be good we are some way off!

and how do you calculate the expected/min IOPS?
0
BigSchmuhCommented:
A 15k HDD should deliver about 180 IOPS (10k => 120; 7200 rpm => 80...) at queue depth 1
...and an Intel X25-E 64GB SSD delivers about 13x 15k HDD IOPS !
0
malraffAuthor Commented:
ok, iv updated everything i could on the server - still waiting on feedback on SAN setup

and now the stats seem to be improving, but still way below expected

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 15, Number of times IO throttled = 1092, IO request blocks = 165
Reads = 12846, Scatter Reads = 29710, Writes = 675, Gather Writes = 26028, Total IO Time (ms) = 5615132
0
BigSchmuhCommented:
I think you should focus on:
-Aligning your partition on a 1MB boundary (using Diskpart.exe MS utility)
-Define a higher queue depth at both server side and SAN side (32 is an almost "classic" value on the SAN)
0
malraffAuthor Commented:
ok feedback im getting is we are chatting about SAN and HBA’s

where we actually have iSCSI!  im guessiing the through put would be much less then?
0
malraffAuthor Commented:
also, iv never used diskpart before, should i be able to do as you suggest if i read a web articale or 2?
0
BigSchmuhCommented:
iSCSI LUN should although manage a queue depth

Regarding DiskPart.exe, read http://support.microsoft.com/kb/929491/fr
0
malraffAuthor Commented:
what would you recomend for queue depth, is this a matter of raising slowly and testing?
0
malraffAuthor Commented:
used diskpart and IO request blocks seem to have went down again?
on diskpart i entered
DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label="SQL" nowait


Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 15, Number of times IO throttled = 920, IO request blocks = 58 CLogicalFile::OutputSummary e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\fileio.cpp 529
 04/15/10 16:24:58 3968 Display Monitor Reads = 12096, Scatter Reads = 28798, Writes = 509, Gather Writes = 25502, Total IO Time (ms) = 3876896
Accept and Award Points Accept as Solution
0
BigSchmuhCommented:
I think 64KB NTFS cluster size is a bit too large for a MSSQL which use its normal 8KB page size...
Can you give another try to diskpart using "unit=8K" and "align=2048" (It's 2048 sectors of 512 bytes for a 1MB boundary)

The main problem stays with the queue depth...start at 32
0
malraffAuthor Commented:
Ok I will try again 2 morow . I do appreciate you coming back to me.  Also the queue depth is already at 32 I'm told. What is a fair increment to try
0
BigSchmuhCommented:
A 32 queue depth should offer a lot more iops on your F 6 hdd array than that...
You should give a try to some io benchmark like IOmeter on it.
0
malraffAuthor Commented:
after doing > "unit=8K" and "align=2048" , the writes are twice as big, but  IO request blocks are still low

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 29, Number of times IO throttled = 785, IO request blocks = 37
 Reads = 10841, Scatter Reads = 26907, Writes = 955, Gather Writes = 24853, Total IO Time (ms) = 3134464
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.