Solved

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

Posted on 2011-09-09
5
945 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
  • 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 39

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now