Way, way, *way* back when I was serving as a junior DBA I learned two very key facts:
- I did not need to stop the database engine service in order to take a database backup
- Database backups did not block other transactions
Within the first month on the job I was approached by a customer who informed me (quite knowingly) that the backups were causing performance issues for their nightly batch jobs and I would need to change the time the backups were being run. Now, I was still fairly new to this whole DBA thing, and I did the wrong thing first: I told this person they were wrong, that database backups did not cause problems. Eventually I learned that it isn’t enough to be right, you need to make it right. And I also learned other things along the way. For example, I learned that database backups do, indeed, cause problems.
Yeah, I said it. That just happened. So, what do I mean?
Quick question: are you compressing your backups (either natively or with a third-party piece of software)? Guess what? You are using more CPU than if you were not compressing your backups. Guess what else? If you box is already operating at, say, 65% CPU utilization on average and you come in and decide to enable backup compression then your CPU will spike and most likely cause a performance hit. Which means your end user will come to you and say something like “the backups are causing us problems”.
And when that happens you need to stay calm. They aren’t looking for you to be right, they want you to make it right.
The situation I mentioned above that I was hit with back in my younger days was caused by disk contention. Apparently the server had been built with one big disk that contained data, logs, tempdb, and backup files. So whenever the backups ran it choked the disk to the point that nothing else could run. So when I told that customer that “backups don’t cause problems”, I was wrong. Well, not completely, as I know that backups don’t cause blocking, but that didn’t matter at that moment.
The user was right, every time the backups ran the server had performance problems. I wasn’t good enough to recognize the potential disk or CPU contention at the time and just assumed that the user didn’t know what they were talking about.
MORAL: Database backups can cause problems, even though we know they don’t cause blocking.
Nice post. There is a difference between causing blocking and causing problems and it is something that we simply don’t think about sometimes as DBAs. I had one where the timing of the database backup conflicted with the tape backups and caused the tape backup process to report an error. I mentioned it on my blog, http://wiseman-wiseguy.blogspot.com/2010/03/timing-matters.html
It wasn’t a big deal, but, to the backup administrator it was a problem.
What you describe Tom sounds more like a scalability, infrastructure spec (or lack thereof) issue than backups per se. In your CPU bottleneck scenario you can look at processor affinity to provide some relief (both native and some third parties).
Chuck,
Sure, and I could also use the Resource Governor as well. The point here is simple: running a backup can cause an issue. They shouldn’t, but they can. And we shouldn’t be so quick to dismiss someone who observes an issue as it happens. All too often I think we put blinders on with regards to certain things. It’s natural, I think, and we just need a reminder to take the blinders off.
It’s a nice post.
I came across a stuation though where FTI rebuild job will not populate the index and user will see an empty page at the specific time it runs. When I checked I saw only backup job schedule conflicts with the FTI job schedule and changing the backup job schedule did resolve the issue.