Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Backup taking MUCH longer on prod HELP:) Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 12:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:30 PM
Points: 140, Visits: 900
I was backing up a customer's database in preparation for upgrading them when their backup job kicked in and dramatically slowed things down for both of us. Instead of an expected 10 minutes it took an hour.
Post #1349306
Posted Thursday, August 23, 2012 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 12:51 AM
Points: 151, Visits: 165
I editted my post but I took a bit long because I was doing other stuff as well. meanwhile thetopic continued.

so for clarity, I'll repost
sorry, misread your post. forget about what I just wrote.

Instead:

can you look at the waitstats during your backup and post the results.

you can find a good waitstat query here:

http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx




Edward
Post #1349309
Posted Thursday, August 23, 2012 12:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28, Visits: 193
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
PAGEIOLATCH_SH 93277.24 93264.14 13.10 3999995 16.97 0.0233 0.0233 0.0000
MSQL_XP 68382.95 68382.95 0.00 317797 12.44 0.2152 0.2152 0.0000
PREEMPTIVE_OS_GETPROCADDRESS 68380.77 68380.77 0.00 317797 12.44 0.2152 0.2152 0.0000
BACKUPIO 64827.20 64814.35 12.85 1156271 11.80 0.0561 0.0561 0.0000
ASYNC_IO_COMPLETION 62775.38 62775.37 0.01 188 11.42 333.9116 333.9116 0.0000
BACKUPBUFFER 61542.37 61414.42 127.95 2368895 11.20 0.0260 0.0259 0.0001
PAGEIOLATCH_EX 43050.09 43045.39 4.70 1856576 7.83 0.0232 0.0232 0.0000
LCK_M_S 36037.16 36036.83 0.33 2871 6.56 12.5521 12.5520 0.0001
LCK_M_U 17102.27 17102.27 0.01 55 3.11 310.9504 310.9503 0.0001
WRITELOG 12215.12 12188.00 27.13 646740 2.22 0.0189 0.0188 0.0000


Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349313
Posted Thursday, August 23, 2012 1:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548
Do you have access to the muti-pathing software? Can you see the config to check the number of paths for each LUN?



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1349320
Posted Thursday, August 23, 2012 1:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:09 AM
Points: 2,049, Visits: 3,585
Also - you didn't answer Robert's earlier question - "Backup of other databases running simultaneously?" - or I missed the answer. Can you also tell us how the drive is configured, i.e. RAID level?

Thanks.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1349327
Posted Thursday, August 23, 2012 2:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 12:51 AM
Points: 151, Visits: 165
michael.berry 22479 (8/23/2012)
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
PAGEIOLATCH_SH 93277.24 93264.14 13.10 3999995 16.97 0.0233 0.0233 0.0000
MSQL_XP 68382.95 68382.95 0.00 317797 12.44 0.2152 0.2152 0.0000
PREEMPTIVE_OS_GETPROCADDRESS 68380.77 68380.77 0.00 317797 12.44 0.2152 0.2152 0.0000
BACKUPIO 64827.20 64814.35 12.85 1156271 11.80 0.0561 0.0561 0.0000
ASYNC_IO_COMPLETION 62775.38 62775.37 0.01 188 11.42 333.9116 333.9116 0.0000
BACKUPBUFFER 61542.37 61414.42 127.95 2368895 11.20 0.0260 0.0259 0.0001
PAGEIOLATCH_EX 43050.09 43045.39 4.70 1856576 7.83 0.0232 0.0232 0.0000
LCK_M_S 36037.16 36036.83 0.33 2871 6.56 12.5521 12.5520 0.0001
LCK_M_U 17102.27 17102.27 0.01 55 3.11 310.9504 310.9503 0.0001
WRITELOG 12215.12 12188.00 27.13 646740 2.22 0.0189 0.0188 0.0000


Okay let's see.

The above waitstats show you what SQL has been waiting for since it's last restart or since you last cleared the waitstats. On top of the list is pageIOLATCH_SH which is associated with reading datapages from the database files. Can't say to much about that right now, your average wait is 23ms per read. Which is, without nowing further info of your system neither good or bad at this point.

then Msql_XP and Preeemptyive_OS_GetProcAddress are associated with using extended stored procs. Now since the wait times are so similar with the backup wait times, I'm making the assumption that you are backing up with a 3rd party tool that is hooking into XPs.

BACKUPIO, ASYNC_IO_COMPLETION (during backups) and Backupbuffer are all related with creating backups. Together with the assumption that you use the 3rd party tool that makes up for the xp waits We can see that 55% of the time that sql is waiting is for backups.

Now, I don't no what your normal load has been on this server. If you haven't done much apart from the backups, it could be perfectly normal 55% of the time that sql has been waiting is backup related.

I would suggest that you do the following:
1) run the waitstat query on dev server and store the results for later reference
2) clear the waitstat on the dev server
3) start the backup on the dev server and wait till it finishes
4) run waitstats query again and post th results

do the same 4 steps on production.

that way we can see the waitstats just assosiated with the backup process and have a good compare between the dev and prod waits.

Also, ask if their are any differences between the LUN for the dev and the LUN for production.

Different amount disk spindles, different SANs? , different raid? different fabric, different paths to SAN?

regards,
Edward
Post #1349352
Posted Friday, August 24, 2012 9:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28, Visits: 193
working with SAN guys now to dig deeper.

Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349766
Posted Friday, August 24, 2012 10:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:09 PM
Points: 3,108, Visits: 11,502
You should run a copy_only backup to the nul device to see if the problem is reading the database data or writing the backup file to disk.

backup database [MyDatabase] to disk = N'NUL' with copy_only, stats = 10

This will tell you how long it takes to read the data from the disk and if that is the problem.

If it is fast, then the problem is more than likely writing the backup to your backup file.

Make sure that you use the COPY_ONLY option so that SQL Server does not see that as part of the backup chain.







Post #1349795
Posted Friday, August 24, 2012 10:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28, Visits: 193
Michael Valentine Jones (8/24/2012)
You should run a copy_only backup to the nul device to see if the problem is reading the database data or writing the backup file to disk.

backup database [MyDatabase] to disk = N'NUL' with copy_only, stats = 10

This will tell you how long it takes to read the data from the disk and if that is the problem.

If it is fast, then the problem is more than likely writing the backup to your backup file.

Make sure that you use the COPY_ONLY option so that SQL Server does not see that as part of the backup chain.






Now that is a really good suggestion!!
I will do so. My test that I mention at the beginning has copy_only so I am good with that.


Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349800
Posted Friday, August 24, 2012 10:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:48 AM
Points: 28, Visits: 193
OK. it is taking a long time on prod...
and here are the wait stats while it is running

type wait totalwait
BACKUPIO 988.943242 5746.281352
BACKUPBUFFER 990.484487 5674.775883
BACKUPTHREAD 0 8.825387371
BACKUP 0 0


Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349811
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse