Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
uuthanh
Starting Member
3 Posts |
 Posted - 2012-09-17 : 01:14:57
|
| Hi experts,I need some directions on what sort of things should I look into why backup and restore take way too long, e.g. 130GB db take 4 hrs to backup and 4hrs to restore.We have 2 brand new physical servers with identital hw (12-core, 288GB ram). We use VMware to create 1 virtual machine on each server and give 8-core and 260GB ram to each machine. From these 2 virtual machines, I just setup 2-node active/active failover cluster on windows 2008R2 and SQL Server 2012 Enterprise. Everything goes smoothly with installation and setup. I test out failover and stuff and things work great. I configure the windows/sql server:- enable "Instant File Inititalzation"- set sql server 100GB max mem- 4 tempdb filesOur plan is to replace the current single sql 2005 with this 2012 failover cluster.When I start poking around with backup/restore on this cluster, i notice it take way too long, e.g. 130GB db take 4 hrs to backup and 4hrs restore to the SAN. First I blame the SAN for IO latency, but when I try to back to 'nul' device, it take around the same time too. These 2 new servers are on the same VLAN as the old server. The old server is 4-core 32GB ram only takes 10ms per read/write. These 2 new servers take around 80s per read/write. These 2 new servers connect to SAN via fibre channel with 1Gbs connection. I also poke around with backup/restore options such as BufferCount, MaxTransferSize, BlockSize, compression, etc... but it does not make any big difference. Of course from sql error log, i got lots of warning about 'IO take more than 15s'. I even try to backup/restore to/from multiple files and still does not help. I notice the restore speed only 8MB/sec (e.g. RESTORE DATABASE successfully processed 14701610 pages in 14676.626 seconds (7.825 MB/sec))Just a note, when I copy the 130GB file(s) from SAN to the server, take me around 20 min. BTW, all disks storage from server are allocated from the SAN, including C: drive.Another note, I've applied CU3 to sql server 2012 clusterWhat else should I look into? please help.Thanks very much,Thanh |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-17 : 01:41:24
|
| 1) Are your drives sorted out in separate IO channels?2) Has the backup\restore always taken so long? or is it a recent development?3) Is every drive linked to a different SCSI adapter?4) Have you spoken to the SAN administrator about any SAN replication delays?Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
uuthanh
Starting Member
3 Posts |
Posted - 2012-09-17 : 10:44:46
|
| The backup/restore only slow with this new particular cluster.Our SAN administrator monitors the NetApp SAN while I'm doing the db restore and he sees the IO latency is around 10-20ms which is normal, but he sees very little activity hitting the SAN and only sees the write speed around 8-9MB/s which is consistent with what I see from the sql server side. I've used the following tools to measure IO performance from the windows/sql side:- Windows perfmon: Avg disk sec/write, Avg disk sec/read, CPU, mem- sqliosim- sql server error logall those 3 gives me very similar IO result which is around 80-100 sec per read/write.Yes. I understand this can be million things can go wrong with this issue. I just need some general guideline/direction on where else should i look into.Thanks,Thanh |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-17 : 14:25:49
|
| Did you ask about the point 4)? . It sounds like from your description it's the write speed. Are you also getting very slow read speed. Are there enough spindles - i.e is the read and write happening from the same narrow disk?Do you have other servers which are you using the same disk array ? How are they performing?Some of my notes from previous situations where the IO greater than 15 seconds appears:http://www.sqlserver-dba.com/2010/11/io-requests-taking-longer-than-15-seconds-to-complete.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-18 : 01:29:09
|
| Accompany the NUL test with :Perfmon - Logical Disk Read Bytes/sec on relevant disk to obtain read throughputJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
sql 2012 take long to backup and restore