Solved

Using SQL Server 2008 R2 Resource Governor to limit CPU tap from running a FUll DB Backup

Posted on 2011-09-09
5
954 Views
Last Modified: 2012-06-27
Hello -
We have a 1.1T DB that I need to run a backup of during the day but due to it's size, the CPU takes a hit and the end users report application performance issues.  This is unacceptable so I wish to use the Governor to limit how much the CPU is hit so I can run the backup.  

Is this possible?  I have reviewed BOL for R2 but not exactly seeing the help I need.

Suggestions please.

Thanks
0
Comment
Question by:SQLwatcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36512135
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36512158
Here's a good read on using the governor for limiting resource consumption for backups:
http://programming4.us/database/3078.aspx
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36512169
Is this what you are looking for?

How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)
http://msdn.microsoft.com/en-us/library/cc280384.aspx

Or maybe this can help -
How to: Configure Resource Governor Using a Template (SSMS)
http://msdn.microsoft.com/en-us/library/bb934150.aspx
0
 

Author Comment

by:SQLwatcher
ID: 36513353
Thank you for the input.  No, I am not using compression for the backups.  I have also reviewed MSDN pages offered regarding the templates etc and the article from programming4.us is similar in content to the MSDN sites.  

I have the template set up but wanted to know if any has had success with running a full DB backup using the governor to lower the performance impact on the end users.

I need to pull a file back up so the db will fit onto two secured drives as it is more that 1.1 T  I just did not want to have to stay up all night to do this task.  

Ideas welcome.

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 36513452
Unfortunately, I don't have hands-on experience w/the governor on an actual backup.  Take a look at the whitepaper referenced at the bottom of this, the Appendix B - Resource Governor Overhead.

http://www.dbta.com/Articles/Columns/SQL-Server-Drill-Down/DBAs---Take-Control-with-Resource-Governor--74545.aspx
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question