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 8:59 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
I have two environments:

DEV:
VM
4 virtual cores
16 GB
Mydatabase 137242.19 MB
Attached SAN storage
SQL 2008 R2 standard

PROD:
Physical
32 Core 2 Processor
64 GB RAM
Mydatabase 134892.19 MB
Attached SAN storage (LUN not shared with any other server)
SQL 2008 R2 standard


I monitor prod with Idera Diagnostic Manager and have stats during the backup time.
CPU/IO/Memory paging etc.. all low.

backup script
BACKUP DATABASE [MyDatabase] TO DISK = N'F:\TestBackup\MyDatabase_SQL_Compress.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'MyDatabase-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO


Backup on VM takes 17 min
backup on Prod takes 88 min

I have no idea why. thoughts?


Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349180
Posted Thursday, August 23, 2012 9:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
Load on prod? I/O path to the backup drive? Compression requires CPU. Is the prod server busier?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349230
Posted Thursday, August 23, 2012 10:32 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
I was the only process running at that time. and CPU was < 5 % utilized

Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349250
Posted Thursday, August 23, 2012 10:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:52 AM
Points: 723, Visits: 350
check For IO of disk.
Post #1349253
Posted Thursday, August 23, 2012 10:51 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
max during that run:

for 10 min Physical IO was 440 page writes per second.. otherwise is was 40 max over the other the 77 min
average disk ms/write was 120 for 5 min of the 88 min. average was 60


Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349257
Posted Thursday, August 23, 2012 11: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
anyone? This is baffling to me!

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
Is the SAN shared with other servers? I've experienced issues like this running backups to a shared SAN because everyone else was backing up their databases at the same time and flooding the SAN throughput. I moved the backup time from midnight to 4 AM and the problem disappeared.

Is there anything other than backups using the F drive? Databases files? Replication snapshot folder or distribution database files? Backup of other databases running simultaneously? Log backups?




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 #1349291
Posted Thursday, August 23, 2012 12:38 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
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 #1349301
Posted Thursday, August 23, 2012 12:42 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
Edward Dortland (8/23/2012)
what does this say:

SELECT db.name,
er.percent_complete,
er.total_elapsed_time/60000 AS ElapsedMinutes,
er.estimated_completion_time/60000 AS remaining_minutes
FROM
sys.sysdatabases db
INNER JOIN sys.dm_exec_requests er
ON db.DBID=er.database_id
AND er.command LIKE '%BACKUP%'

and does the output change over time?

Edward


I get nothing.. since it is not running at this time of day.
The output doesnt really change as far as the size of the backups.


Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
Post #1349302
Posted Thursday, August 23, 2012 12:45 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
Robert Davis (8/23/2012)
Is the SAN shared with other servers? I've experienced issues like this running backups to a shared SAN because everyone else was backing up their databases at the same time and flooding the SAN throughput. I moved the backup time from midnight to 4 AM and the problem disappeared.

Is there anything other than backups using the F drive? Databases files? Replication snapshot folder or distribution database files? Backup of other databases running simultaneously? Log backups?


I have been told that this LUN is dedicated to this server. I am sure other servers share the whole SAN though. Most of my backups occur on a different LUN or completely diff SAN I believe. I will confirm this..
nothing else is on this volumues/drive except backups. no replication.
I watched it last night to ensure nothing else was running on that server.


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

Add to briefcase 12345»»»

Permissions Expand / Collapse