Backup taking MUCH longer on prod HELP:)

  • 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

  • Load on prod? I/O path to the backup drive? Compression requires CPU. Is the prod server busier?

  • 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

  • check For IO of disk.

  • 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

  • anyone? This is baffling to me!

    Michael B
    Data Architect
    MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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

  • 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

  • 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

  • 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.

  • 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%5B/quote%5D

    Edward

  • 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

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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