Improving Backup Performance using SQL native methodology

Question

I am trying to gather some stats on comparison of run time vs performance between Litespeed backups and sql native backups. As we are no longer going to use Lite Speed [even though i personally loved that SW], have to gather metrics how SQL will perform using native compression backup]

So i am looking for a way to reduce CPU usage to X % while taking native compression backups. In Litespeed we just specified this option in advanced tabs and saw everything running fine. I am aware of resource governor, but due to some constraints not able to use it. Will need to work on that. But is there a better and smarter way to do this. Please suggest

Edit.. Have to add, as per security and company policies we cannot use third party or external SP's to be created. I saw this can be achieved using OLa 's script as well, but we wont get approvals to introduce this :(

• Jonathan Fite: Throttle in Ola's scripts is only valid if you are using LITESPEED anyways.
• SqlWorldWide: I blogged about this topic here.
• BeginnerDBA: @SqlWorldWide, thanks for the article, but i am looking on how to throttle CPU value because other process fails when backup uses all 100 % cpu
• Kin Shah: How about you use compression along with buffercount and maxtransfersize .. do a trial and error approach and go with the numbers that look more promising in your env.
• BeginnerDBA: @Kin, thanks . i am definitely testing that as well. However the problem is such that we have to throttle CPU as this being one of the busiest system we have with TB's of database. We have over 512 GB's of RAM and more than 128 Logical processors but apps are such that if backup eats up 100 5 of CPU they tend to slow down. so was looking a native way of throttling this down

Answer 1

Resource Governor can control the resource consumption of a backup.

By default, backing up using compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations. Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited byResource Governor when CPU contention occurs. This topic presents a scenario that classifies the sessions of a particular SQL Server user by mapping them to a Resource Governor workload group that limits CPU usage in such cases.

Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

Answer 2

I know this is third party but it's free and powerful. I would check out Http://Dbatools.io

In addition you can specify buffer size, number of files (Striping). Etc. definitely worth a look.

• alroc: Almost every parameter/option offered by Backup-DbaDatabase in dbatools is available via the native Backup database T-SQL. Using dbatools here isn't really necessary, it'll just be acting as a different front-end. I'm a huge advocate for dbatools but it's not needed to address the OP's optimization goals.