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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
A very low query depth can explain that by itself.
ASKER
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?
********** 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")
ASKER
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?
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 !
...and an Intel X25-E 64GB SSD delivers about 13x 15k HDD IOPS !
ASKER
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
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)
-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)
ASKER
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?
where we actually have iSCSI! im guessiing the through put would be much less then?
ASKER
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
Regarding DiskPart.exe, read http://support.microsoft.com/kb/929491/fr
ASKER
what would you recomend for queue depth, is this a matter of raising slowly and testing?
ASKER
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::OutputSummar
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
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::OutputSummar
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
You should give a try to some io benchmark like IOmeter on it.
ASKER
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
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
ASKER
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?