SQL Backups cause user slowness

Question

SQL Backups cause user slowness

lulu_lutz

SSCrazy

Points: 2155
More actions
July 25, 2012 at 10:56 am

#264684

I'm receiving complaints that users experience slowness when the sql backup runs. The database is 2TB and it is used for housing images. I know...poor design, but the design was before my time here. What can I watch for to determine why it is causing user slowness?
SQL Server 2008 SP1 64 bit
26 processors
32 GB RAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's to be expected. Backups drive a lot of IO, if the IO subsystem can't handle the load it'll slow everything down, backups and user queries.

Are you compressing the backups? Have you considered a 3rd party backup tool?

How many filegroups does the DB have?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lulu_lutz SSCrazy Points: 2155 More actions · Answer 2

Are you compressing the backups? Have you considered a 3rd party backup tool?
How many filegroups does the DB have?

That's what I expected i/o. We do have a third party tool that compresses the backups, but it still causes latency. They just use the primary filegroup. I apologize I didn't include that this is a shared database server. about 50 of these databases are on this one server. This is just the big one that's causing the latency when it runs. With that said, how does it cause latency only for the database that is being backed up? Wouldn't it cause it for everything? Or maybe the users of the other databases just aren't complaining.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If it's IO related it'll potentially slow down all databases on the same storage. It's a fair bit more complex though, depends on whether data is in cache or disk, how much is being requested, etc.

Is this a 24x7 system?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lulu_lutz SSCrazy Points: 2155 More actions · Answer 4

Yes unfortunetly it's 24x7. I'm putting in my recommendation to burn lefthand! Thank you for your help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Best thing I can suggest is schedule the backup for the lowest usage time, you'll have to monitor to see when that is.

Also, if you're using a SAN, speak to your SAN vendor about snapshot backup technology.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 6

The significant thing to consider is that Third party tools may use mutithreading ( they generally do) and add to the CPU overhead. Also Third party backup use compression which may further use additional CPU cycles.

Another thing to consider is that most third party tool use extended stored procedure(s) which use memory outside SQL Server memory space, therefore you may have to consider that impact as well.

The Third party tool that you use will possibly have a option of lowering its own priority in relation to other processes, therefore you can use that option to lessen the impact on SQL server \ application activity.

Try collecting the waittype stats using query available at Glenn Berry's blog when the backup is in progress, this information will give you a good idea as to what the other processes are waiting on when the backup is in progress. In all probability you will see all waittype(s) related to I/O.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 7

Vikrant S Patil (7/25/2012)
The significant thing to consider is that Third party tools may use mutithreading ( they generally do) and add to the CPU overhead. Also Third party backup use compression which may further use additional CPU cycles.
Another thing to consider is that most third party tool use extended stored procedure(s) which use memory outside SQL Server memory space, therefore you may have to consider that impact as well.
The Third party tool that you use will possibly have a option of lowering its own priority in relation to other processes, therefore you can use that option to lessen the impact on SQL server \ application activity.
Try collecting the waittype stats using query available at Glenn Berry's blog when the backup is in progress, this information will give you a good idea as to what the other processes are waiting on when the backup is in progress. In all probability you will see all waittype(s) related to I/O.

Excellent points. SQL Backup Pro (from Red Gate) can be limited on threads, so you can set it to use less than you have.

Disclosure: I work for Red Gate

SQL Show SSCertifiable Points: 6143 More actions · Answer 8

Post here wait types of your backup session. Also, I hope there will be an option (BufferCount, MaxTransferSize) in third party tool. Try to reduce it to some extent which may give resources to other processes.

lulu_lutz SSCrazy Points: 2155 More actions · Answer 9

The wait types during the backup slowness are Async_IO_Completion, Backupbuffer, and Backupio. And I've tried a third party tool and sql backups. Both result in slowness, and the same wait times even if I change the cpu usage, threads, etc.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

You're IO bound. Fiddling with CPU and memory won't help, the IO subsystem is the bottleneck.

Can you write the backups to a different destination? One that's faster preferably.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 11

if nothing else works you can maybe work on a weekly once Full backup + Daily differential backup + Transaction log backup strategy.ofcourse this strategy goes out of the window if a major part of data changes for things like batch loads.

But if the data is getting changed gradually over the week then differential backup can help you in the backing up only the changes ( as opposed to the entire database) . Please note though that differential backup is cumulative in nature.

or you can also consider a filgroup strategy but this strategy is usually has to be planned in the application design \ development stage where tables that can be stored together have to be identified earlier on.

and one additional thing may be you can also consider stripping the backup across different drives to take advantage of I/O distribution.

lulu_lutz SSCrazy Points: 2155 More actions · Answer 12

GilaMonster (7/26/2012)
You're IO bound. Fiddling with CPU and memory won't help, the IO subsystem is the bottleneck.
Can you write the backups to a different destination? One that's faster preferably.

After spending days trying to prove it wasn't sql, it turned out to be the storage causing the bottleneck in IO as you thought, Gila. I had them attached a different storage node, and it works fine again. I appreciate all of your help.