SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup taking MUCH longer on prod HELP:)


Backup taking MUCH longer on prod HELP:)

Author
Message
Bill Talada
Bill Talada
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1208 Visits: 2001
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.
Edward Dortland
Edward Dortland
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 196
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
micber
micber
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2836 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3464 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Edward Dortland
Edward Dortland
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 196
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
micber
micber
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 193
working with SAN guys now to dig deeper.

Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5918 Visits: 11771
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.
micber
micber
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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
micber
micber
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search