Home / server / Questions / 305328
Accepted
Scott Chamberlain
Asked: 2011-08-27 08:04:51 +0800 CST

Lower the I/O priority for backups

  • 772

I have a job that nightly runs a backup of all attached databases however it can bog the server down if someone is connected during that time. I have TONS of CPU overhead to work with (I am barely above 5% in use, 8 cores total the highest is maybe 30% the rest are at idle), my issue is I am pegging my I/O. Looking at resource monitor I noticed that the I/O priority in Resource Monitor of the reads to the mdf and to the .bak file have a priority of Normal.

Is there a way to make my backups run at Background priority?

sql-server sql-server-2005 backup sql

4 Answers

  1. Best Answer

    You can backup databases to a UNC path (manually or by creating a backup device) so you could try that, but you'll be trading disk I/O for network I/O.

    • 3

  2. This doesn't actually answer the question (how to tweak I/O priority for a backup operation) but may help significantly speed up backups and reduce their affect on other activity:

    If you can write your backups to drives that are not holding the database's data and log files. With spinning disk based drives this improve performance considerably because the backup operation is not constantly causing the heads to flip between the area holding the live data (to read pages) to the area the backup is being written to (to store those pages).

    This may mean adding an extra physical drive (or multiple drives so you can use RAID for redundancy) to the machine for the backups to go to, particularly if you only have one drive or array that holds everything. If you have your data and log files on different disks/arrays but don't have a third drive/array to write the backups to and are unable to add one, writing the backups to the drive with the logs is usually faster than writing them to the drive with the data files (as the active pages in the log files are far fewer except in unusual cases).

    • 2

  3. No, there is no supported way of changing one thread to a different priority level. You can try using something like LiteSpeed and/or HyperBac to compress the backups which will reduce the write IO requirements.

    • 0

  4. Upgrade to SQL Server 2008 Enterprise or SQL Server 2008 R2 Standard or higher, make use of native backup compression - and trade some of those CPU cycles in for a potentially much reduced backup time.

    • 0