Very high I/O on database during a backup maintenance plan

Question

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.

Answer 1

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

Answer 2

Write the backups to a different drive (or array, set of spindles, controller, whatever), or to the network.

To back up to the network you'll need acceptable bandwidth, and run SQL Server as a domain account.

Edit: With your new information, I'm guessing you have poor-performing I/O on the server. As a stopgap measure, try to get the backups to complete during hours when the database is not busy (if possible).

You can use the "Data File I/O" pane of Activity Monitor (assuming SQL 2008 and newer) to see response time for each data and log file. Anything more than 25ms isn't good. Over 50ms is terrible.

If you see bad numbers, you'll have to take this to your storage guy.

Answer 3

You should make transaction log backups much more often than just nightly. One Microsoft Certified Master (Brent Ozar) recommends you take them one per minute:

http://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/

I'm not sure I'd go that far, but once per day is not anywhere close to often enough. IIRC, there's a template maintenance job that does this every fifteen minutes. Start there, and see if you can get even more frequent.