Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
How often are you doing tlog backups? –  Joel Coehoorn Mar 20 at 20:44
    
@Joel - They're backed up nightly with the databases. –  Sean Perkins Mar 21 at 16:10
add comment

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer
    
Thank you for the insight Kin, this has opened my eyes to other possible issues to fix. Now I just need to figure out how to use Ola's stuff and I should be off to a good start. –  Sean Perkins Mar 20 at 18:29
add comment

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.

share|improve this answer
    
We have two ways of performing backups...we keep 3 days worth on disc and then use CommVault to backup to tape. Also, most all our systems, including the aforementioned, are VMWare VMs that connect to a SAN.My SAN admin tells me that there isn't anything out of the ordinary for high I/O latency issues that he can see. If there is a problem, I have to find a way to quantify that and show him. –  Sean Perkins Mar 20 at 15:10
    
You might not be imposing a large impact on the SAN overall, but that doesn't necessarily mean I/O contention is not your problem. I'm assuming that you have a gbit link to the SAN (at 100mbit your backups/restores/checkDBs will be very slow and will badly affect anything else on your VM(s)) but even at gbit there will be latency issues and massive processes like a large backup or checkDB will slow down other processes that try to access the data files. Increasing the RAM allocation for your VM may help as this means it will need to hit the SAN less in normal operation. –  David Spillett Mar 20 at 16:10
    
Thanks for the info guys, I appreciate your time and effort. There is a change request to add another 8GB of RAM coming up soon. I know this will help a lot of things, but I also realize there is more I can do to the system to make better use of it's current resources. As far as you comment about the NIC, it is Gbit. –  Sean Perkins Mar 20 at 18:27
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.