January 26, 2015 at 9:12 am
This is a general question regarding a backup strategy coming from a non-DBA and looking to hear from the community if there is anything you guys do in your companies to eliminate the impact backup have in the systems.
We currently run SQL 2008 R2 and we use SQL LiteSpeed for backups, we do differential backups every day and full backups once a week. Every time backup runs it impacts the performance of the systems causing timeouts and errors. We of course do this off hours, but we are a 24/7 business so no time is "good time"
I'm hoping there is a solution to this, I'm open to ANY suggestions, including architectural considerations, software options, etc.
Thanks in advance for your help
January 26, 2015 at 9:23 am
Starting with the basics. Backups do not take locks, so the timeouts can't be from there.
Before looking for a solution, I'd suggest identify the problem. Why do queries time out? It won't be due to locks, so what is making them take longer? What wait types do you see during a backup and in at what % of overall? What wait types do you see normally (when backups aren't running) and at what % overall?
With that info, you should be able to start targeting the specifics of the problem and planning ways to mitigate those specifics.
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
January 26, 2015 at 9:38 am
Thank you Gail, does that apply to all versions of SQL, we run Standard, does that make a difference as far as possible locks?
I'll take your advice and look closer to the problem, my first assumption was that the errors we were getting were backup related because it always happened during backup windows, but the fact is that there are other nightly processes running at the same time.
In your experience, do backups present ANY performance degradation to your systems? If you don't have any system impact with your backups, any considerations you take to ensure that's the case?
Thanks again
January 26, 2015 at 9:48 am
Backups do not take locks. Full stop. End of story.
Yes, in my experience backups do usually have some impact on the system. To be honest that's pretty unavoidable. It is, however, possible for the impact they have not to cause problems with the application.
Get some benchmarks for your systems. What do things look like when the app's running fine? What do things look like during backups? What do things look like when other nightly processes are running? What do things look like when there's these timeout problems. The more data the better (to a point). Once you know what normal looks like and what abnormal looks like you can start digging into what causes the differences.
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
January 27, 2015 at 1:16 am
Since the days of SQL Server 7.0, if I've seen backups impacting a system in a way that affected performance, it was because there were already a bunch of other issues with the system that needed to be addressed. Backups by themselves, I've never seen them take a system down. I'd strongly suggest addressing all the other issues that are likely to be found in the system. Use wait statistics and gather query performance metrics to understand what is happening on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2015 at 6:01 am
As the others have mentioned your wait stats will help identify the exact issue.
Without that detail and going on the symptoms it sounds like you most likely have IO contention. Causing queries to take longer to return the results.
As a typical kind of example:
1. Procedure A takes on average 20s to complete when backups are not running.
2. Procedure A takes on Average 30 seconds to complete when backups are running.
By default your application probably has the typical 30 seconds timeout period for database calls, which means when procedure A takes longer than 30 seconds to complete the application will return a time out error as it has given up waiting on the results.
Is your database backup(s) being written to the same physical drive(s) as the database log \ data devices.
MCITP SQL 2005, MCSA SQL 2012
January 27, 2015 at 10:01 am
Thanks everyone!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply