SQL Server Backup – Address Very High I/O During a Backup Maintenance Plan

backupmaintenance-planssql server

I've recently been having some hellacious problems with a specific maintenance plan. I found that part of the problem was that I had Shrink and Rebuild Index in my nightly plans, those have since been removed. All that is left is Tlog backups, Integrity check, and database backups in that order.

One of my database developers came in this morning and logged in as usual and found that our ServiceDesk software wouldn't load via webpage. Upon looking in Spotlight, the Max I/O Wait on the ServiceDesk database was almost 55k ms. I went in SSMS and cancelled the job manually and everything returned to "normal" and we were able to log in.

Now to my quesions….

  1. Is this normal?
  2. If it is normal, should I start my backups much earlier?
  3. Should I break up the maintenance plan into smaller chunks and then stagger?

I'm an accidental DBA who is on the homegrown path of being a production DBA, so I'm relatively new. I'm finding a passion for this stuff as late and heard this community rocks, so anything you can suggest I'd appreciate greatly. Also, if I've missed anything or you need more detail, I'll be happy to clarify.

Best Answer

Depending on your database size, checkdb can take hours to finish. It is heavy on the disk subsystem as well incurring lots of I/O's.

Check out Aaron's excellent article on : Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts

Backups are also I/O intensive operation.

I would suggest to look for a maintenance window - when activity is low or minimal on the server.

Now if you want to dig deeper(prove it), then you can use sys.dm_io_virtual_file_stats. It will show you where the hot-spots are and you can ask your SAN admin to move them away.

 ---Ref: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

SELECT
    --virtual file latency
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    --avg bytes per IOP
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [io_stall_write_ms] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    --[vfs].*,
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

As a side note, highly recommend you to look into Ola's : SQL Server Maintenance Solution - backup and index maintenance

Related Question