January 26, 2009 at 12:56 pm
Hi All
I am just trying to work out a problem I have with a new SQL 2005 cluster using an iSCSI SAN. The problem is that the new drive setup is working very well in my IO tests so far, using SQLIO to give a rough idea of performance for my data and log and temp drives etc.. is nice and fast considering that I am using iSCSI and not fibre. But for some reason that I can not work out, when I come to do a test in the management studio by doing a test backup to my data drive the MB/sec is VERY slow!!!
This is the command I am using so I can see how fast it is going....
BACKUP DATABASE TO DISK ='NUL;'
The results I get from this are sometimes as low as 17MB/sec! I am not sure why this is as I was expecting to get around 100MB or more. The plot does thicken though as when I first ran this test a week or so ago it was about 99MB/sec but now has changed. I have been mucking around a bit with the disc setup as my write cache was a bit unbalanced and mucked about a bit to get my 8K writes a bit faster which has worked well. I have undone my change but has not made any difference.
I have put everything I can think of back to the settings I had it when I was getting 99MB but still not luck! Can anyone help me??
Does anyone think this will have a big impact when I launch the new SQL Server into the production system (which right now I am worried about doing!) I dont know why this is like it as my disc IO is very fast.
My rough setup is 1GB iSCSI, and my Data Drive is made up of 14 Drives RAID 10, LOG is 8 Drives RAID 10 and Temp is 6 Drives RAID 10.
they are all 15K as well.
I am sure people will have more questions, but if anyone has any answers it would be apprecaited! I dont know what to do 🙁
Many Thanks
January 26, 2009 at 3:24 pm
Why are you using NUL; for your backup device?
Do you have have your iSCSI traffic isolated from your LAN by using a physically separate switch or VLAN?
I think the first place I'd start is with your SAN vendor for any recommended configurations.
January 27, 2009 at 2:45 am
Hi SSC Eights!
Thanks for replying, yes I have got the iSCSI traffic sperate on it's own switch, and that does appear to not be having any problems.
I have not contacted my vendor yet but will do if I can not get any further progress. What is confusing me is that the problem with slow IO and also slow network traffic (because it is using iSCSI) is only happening when using Management Studio and not when I do any direct disk access in Windows.
I used SQLIO tool to try and get an idea of how my disks will perform as I know the SQLIO tries to write to the discs as close as SQL does. Is there any other IO or throughput tests that I can do in SQL itself to see how the system will perform before I let this in the production environment???
Oh sorry I nearly forgot, the NUL was there for no main reason, I did also script out a proper backup of my DB and ran that and got a simliar result!
January 27, 2009 at 5:16 pm
When you used SQLIO how large was the test data file you used? How large was the database? Maybe the SQLIO test file was small enough to fit in the SAN's cache and didn't have to go to disk?
You might want to look at SQLIOSim if you want to simulate SQL data and log file activity.
How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
January 28, 2009 at 6:33 am
Who is the manufacturer of your storage device(s)? And have you verified that your storage device's network is currently connected at 1000/FULL? Last question (sorry a bunch of them came to mind 😀 ) When you did your initial speed test was there any other activity on the SQL server and is there other activity on the sql server when doing the backup?
-Joseph
January 28, 2009 at 10:11 am
You said you are writing the Backup file to the "Data drive".
I am guessing that your Recovery Model is set to Full.
This is what I have done/would do in your shoes to nail down the issue.
1. Stop writing Backups to the same Discs sets your Datafiles are on.
This alone will slow the write speed of the backup file to
25% or less of your Max write IO.
2. Empty and shrink the Transaction logs prior to the DB backup.
Putting the Tran logs on the same disc sets as data,
running Full recovery, and not doing hourly Tran log backups will
cause your Tran log to grow larger than the DB and slow backups
to a crawl like you have mentioned.
Having a sperate disc sets for the Tran, Temp, and Data files will triple (or better) your IO performance and allow you to allocate Bandwidth to certain volumes for Reads or Writes as needed.
A good maintenance plan will increase your SQL server IO and keep you from having task that take 2 minutes today, and 2 hours in a month.
Good Luck!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply