| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2007-09-05 : 16:38:57
|
| 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.txtThe param.txt is as followsc:\sqlio_test.dat 4 0x0 100 (default)If my database files are on the SAN say Drive R how do i change the scriptR:\mytestdatabase.mdf i do not have dat files. the 4 is the CPU and 0x0 is defaultThe 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 MDFThanks Thanks |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-05 : 18:05:00
|
| You can change param.txt to something liker:\sqlio_test.dat 4 0x0 10000this will create a 10GB test file name sqlio_test.dat in the root of the R: drive.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
 |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-05 : 18:33:57
|
| Oh is that what it does it creates the 10 gig file in the disk where the san disk are Rr:\sqlio_test.dat 4 0x0 10000I thought it was one of the SQL Databases i.e mdf So the file would be empty and then as it performs the IO Tests it will keep on increasing.So it does not have to be a SQL Database |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-05 : 19:00:19
|
| It doesn't use a SQL Server file. It creates it's own file. Please don't point it at an existing file as it might over write it. It creates the full file first and then does the performance testing.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-05 : 19:29:09
|
| Thanks i have searching all day to figure out the dat file.I came across these scripts just nowsqlio -kW -s360 -frandom -o8 -b8 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -frandom -o8 -b64 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -frandom -o8 -b128 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -frandom -o8 -b256 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -fsequential -o8 -b8 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -fsequential -o8 -b64 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -fsequential -o8 -b128 -LS -Fparam.txttimeout /T 60sqlio -kW -s360 -fsequential -o8 -b256 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -frandom -o8 -b8 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -frandom -o8 -b64 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -frandom -o8 -b128 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -frandom -o8 -b256 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -fsequential -o8 -b8 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -fsequential -o8 -b64 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -fsequential -o8 -b128 -LS -Fparam.txttimeout /T 60sqlio -kR -s360 -fsequential -o8 -b256 -LS -Fparam.txt[SQLIO PARAM]f:\testfile.dat 4 0x0 8192(As my san files are R)I do R:\testfile.data 2 (cpu) 0x0 8192What is the best scripts to run.Just to start of should i do the 8192 then gradually go up in size.and change the 8192 to 10000 (10 gig)Bear with me first time doing IO Testing.Is there a way to capture the results to a text file rather than to dos command so i do not have to run each one separately but just leave running and check in morning....Will it create the sqlio_test.dat for 8192 then the next command empty this and recreate it. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-05 : 23:22:29
|
| I usually focus on 8KB random reads and 8KB random writes. I find those are the best predictor of real-world performance. You'll have to pipe output to a text file if you want to save it. I usually just write down the number of I/O's per second and the average duration.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-06 : 08:17:17
|
| Thank you i will do some testing today on our new SAN and see what i get.Thank you for your time |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-06 : 08:30:23
|
| Could you pass me a copy of your script that you use to run so i have a good exampleThanks |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-06 : 11:32:40
|
| I actually thought of that. Unfortunately I'm traveling and won't have access to it for a while. Sorry!=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-06 : 13:47:27
|
| No worries i play with what i have and post some things back...This is all good learning.... |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-07 : 21:36:14
|
| hi there i done some tests see http://www.stretchunlimited.com/io.xlsWould you have a moment to help me digest.The figures for IO seem low on raid 1 When you look in profiler at IO is this the amount of 8kb (trying to figure out how much Data we are pushing to the disk on an average day)...Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-08 : 00:34:32
|
| Sure since raid 1 has less spindle. Use raid 10 if you need faster i/o. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-08 : 06:31:17
|
| We are re building RAID 10 but how do you know from what is being done on SQL if you exhausted the SAN like how much volume....I can say this is what the SAN can do (but from SQL on a daily basic i cannot determine this)... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-08 : 12:11:17
|
| What do you mean 'exhausted the SAN like how much volume'? |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-08 : 18:43:00
|
| Bear with me here no SAN expert..Could you help through some light IO Per Sec and MB Sec what is this telling me.On one of the SQLIO testsRan call sqlio -kW -s360 -frandom -o8 -b8 -LS -Fparam.txt 8192 test file.TEST SERVER (F Disk) IOPS 355.64 MG / SEC 2.77 R (RAID 1) 766.27 5.98 M (RAID 5) 266.42 66.6 The numbers seem low. Is it that it performed 355.64 IO transactions at a rate of 2.77 Mg a second.When i view some of my results from Quest Software the IO can be 717,715.00 (1 hour period)Trying to tie the two together and determine how long 717,715.00 would take or whether this is just too much for SAN.Any additional information would be appreciated. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-09-08 : 19:07:29
|
| I would focus more on IO operations per second (IOPS) than MB/sec. 717,715 IO's in 1 hour = 200 IOPS. Quest probably isn't reporting the capacity of the disks but the actual IO. So their numbers are reasonable.355 IOPS for a single drive is certainly reasonable. That's right around what I'd expect to see for a SATA drive.766 IOPS in RAID probably means faster drives -- some type of SCSI. Certainly reasonable numbers.266 IOPS for RAID 5 doesn't suprise me. I'm continually amazed at how horrible RAID 5 is for database performance. I try to never put any database files on RAID 5 if I can avoid it.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-09-09 : 06:31:17
|
| 200 divide by 3600 = 200 so it does thats great so when looking at the Disks and i see the SAN of 355 IOPS thats more than adequate but on the RAID 5 thats a pretty close to max out on disk.Thanks for clarifying.....Im not SAN expert but it good to know what volume im sending the Disks and if it is capable.When testing new Servers and San this is a good test. Then review previous quest IO transactions.Any other tests i should do. I have a new SAN coming and New servers ready for SQL 2005. |
|
|
|
|
sqlio.exe tool