SQLIOSIM results on a SAN to be used for SQL

malraff
malraff used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

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?
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?
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")

Author

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?
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 !

Author

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
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)

Author

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?

Author

Commented:
also, iv never used diskpart before, should i be able to do as you suggest if i read a web articale or 2?
iSCSI LUN should although manage a queue depth

Regarding DiskPart.exe, read http://support.microsoft.com/kb/929491/fr

Author

Commented:
what would you recomend for queue depth, is this a matter of raising slowly and testing?

Author

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

Author

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial