Optimizing Backup Performance Using Data Science Techniques
ABSTRACT
One of the most important tasks for a database administrator is taking (and testing!) backups. As databases get larger and larger, the amount of time it takes to perform a backup can grow as well, to the point where your backups take longer than your available backup window. There are several settings we can use to optimize backup performance, such as buffer counts, maximum transfer size, and the number of files, but trying every combination of settings on a single production-sized database could take weeks or even months. In this talk, we will apply data science techniques to the problem of backup settings optimization and look at different models for approaching the problem and analyzing data. Some statistics background would be helpful, but is not required; the big requirement is a desire to speed up backups.ADDITIONAL MEDIA
Presentation
I presented this for the Microsoft Data Platform Business Continuity Virtual Group on 2023-04-11.
SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation.
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
- Solomon Rutzky has a DBA Stack Exchange question and a series of notes covering the various backup settings.
- Max Vernon gets into the weeds on tracking backup performance.
- Kaloyan Kosev points out some of the many ways you can reduce your backup window, many of which are "prior to" the backup settings I look at.
- Kin Shah gives us some tips around reasonable settings for backups. Another place where this shows up is a different Stack Exchange post looking at the topic, with some great back-and-forth discussion.
- Parikshit Savjani walks us through what the SQL Server team did to make backups faster in SQL Server 2017.