Author |
Topic |
sks198117
Starting Member
46 Posts |
Posted - 2010-07-29 : 03:06:22
|
Hi,I have my DB server with following configurations.OS Name: Microsoftr Windows Serverr 2008 Enterprise OS Version: 6.0.6002 Service Pack 2 Build 6002OS Configuration: Member ServerOS Build Type: Multiprocessor FreeSystem Manufacturer: HPSystem Model: ProLiant DL580 G5System Type: x64-based PCProcessor(s): 4 Processor(s) Installed. [01]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz [02]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz [03]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 Mhz [04]: Intel64 Family 6 Model 29 Stepping 1 GenuineIntel ~2400 MhzTotal Physical Memory: 32,764 MBPage File: Max Size: 68,661 MBand SQL SERVER version isMicrosoft SQL Server 2008 (SP1) - 10.0.2714.0 (X64) May 14 2009 16:08:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) I have a DB of size 225 GB when i started to backed the DB it took 10 hrs 7 mins to get complete.Which is too much i guess in comparision to DB size.Can anyone please guide me what could be possible reason and how i can improve performance?Thanks |
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2010-07-29 : 04:14:56
|
Need more information:1. How are you doing the backup? E.g. Native BACKUP, 3rd party tool2. Where are you writing the backup file(*.BAK) to? E.g. local disk, SAN disk, NAS, tape, VTL3. When are you performing the backup? Any other activity at the same time4. What is the point of contention? Have you monitored the usual suspects - CPU, memory, IO, network? |
|
|
sks198117
Starting Member
46 Posts |
Posted - 2010-07-29 : 04:49:18
|
thanks YelloBug for replyHere is the info1. I am doing native backup using 'backup database' command2. I am writing the backup file on local disk3. There was no any other activities was performing when i started backup but earlier one SSIS package was running and memory utilization was around 31 GB out of 32 GB but when package executed the memory utilization doesn't goes down and it was still showing 31 GB and process which was taking high memory was msdsrv.exe.4. CPU utilization was normal(10-20%) during this period.Hope this will help you to figure out the reasonLet me know if u need more infoThanks |
|
|
sks198117
Starting Member
46 Posts |
Posted - 2010-07-29 : 04:52:59
|
Sorry about point 2. it's not Local disk I am writing the backup file in SAN disk |
|
|
sks198117
Starting Member
46 Posts |
Posted - 2010-07-30 : 02:54:30
|
I have restored the backup file on same server in another DB and it took more than 10 hrs to get complete..It seems some unrealistic.Can anyone please help out ?Thanks |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-07-30 : 03:36:43
|
you need to benchmark some of your activities.Can you take SQL server out of the picture and copy a file to your SAN disk and see if writing to that location is the bottleneck? i.e test if your environment is kosher before digging into SQL server itself.use a smaller (but realistic) size file to get an elapsed time that you can scale up to mimic your live situation. you could also have some TCP/IP, network routing problems - can you measure your latency of messages passing around your internal network? I'm not expert in this arena, but I've seen similar questions posted hre by otehrs....just a matter of using the right search words. The top 5 members would have a lot of experience in this arena. |
|
|
sks198117
Starting Member
46 Posts |
Posted - 2010-07-30 : 04:23:41
|
Thanks Adndrew,I have tried to copy a file of size 970 MB to SAN disk from our local disk and its took around less than minute to copy it to SAN.Is that ok? i am not very mmuch sure about these things but i really need some help on above issue.Thanks |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-07-30 : 07:13:08
|
Going by those numbers then the base line time to copy a 235GB file to SAN would be approx 240mins - 4 hours. You're now looking to explain the difference between this and the measured 10hours.One approach to take is to investigate other Backup software backages (ie. maybe @ 235GB SQL is delivering as good as what it can) - www.red-gate.com have a package which seems to have a good reputation, incorporating seamless inegration with SQL Server and also compression abilities. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-30 : 08:20:44
|
... or using the Backup COMPRESSION feature in SQL 2008? |
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2010-07-31 : 20:15:49
|
Your backup throughput would be around 6 MB/sec, which is extremely poor. Try the suggestions here, which should be able to tell you if it's a problem with the reading phase or the writing phase that's the bottleneck during the backup process ([url]http://www.sqlbackuprestore.com/speedingupbackups.htm[/url]).Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily |
|
|
|