Solved

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

Posted on 2011-09-09
5
951 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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