Solved

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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