August 23, 2012 at 8:59 am
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
August 23, 2012 at 9:58 am
Load on prod? I/O path to the backup drive? Compression requires CPU. Is the prod server busier?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 23, 2012 at 10:32 am
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
August 23, 2012 at 10:44 am
check For IO of disk.
August 23, 2012 at 10:51 am
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
August 23, 2012 at 11:36 am
anyone? This is baffling to me!
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
August 23, 2012 at 12:24 pm
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?
August 23, 2012 at 12:38 pm
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
August 23, 2012 at 12:42 pm
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
August 23, 2012 at 12:45 pm
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
August 23, 2012 at 12:48 pm
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.
August 23, 2012 at 12:54 pm
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:
Edward
August 23, 2012 at 12:57 pm
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
August 23, 2012 at 1:05 pm
Do you have access to the muti-pathing software? Can you see the config to check the number of paths for each LUN?
August 23, 2012 at 1:21 pm
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
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply