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?
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:SELECTdb.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
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_S
PAGEIOLATCH_SH93277.2493264.1413.10399999516.970.02330.02330.0000
MSQL_XP68382.9568382.950.0031779712.440.21520.21520.0000
PREEMPTIVE_OS_GETPROCADDRESS68380.7768380.770.0031779712.440.21520.21520.0000
BACKUPIO64827.2064814.3512.85115627111.800.05610.05610.0000
ASYNC_IO_COMPLETION62775.3862775.370.0118811.42333.9116333.91160.0000
BACKUPBUFFER61542.3761414.42127.95236889511.200.02600.02590.0001
PAGEIOLATCH_EX43050.0943045.394.7018565767.830.02320.02320.0000
LCK_M_S36037.1636036.830.3328716.5612.552112.55200.0001
LCK_M_U17102.2717102.270.01553.11310.9504310.95030.0001
WRITELOG12215.1212188.0027.136467402.220.01890.01880.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