SQL Server Performance Tuning Script – Backup Throughput
Originally published on DMMaxwell.WordPress.com.
Recently on one particular server, I noticed that backups were taking much longer than they should have. The databases or backups weren’t getting significantly larger, so I wanted to know if there was a problem with the backup storage, and to do that I wanted to look at backup throughput over time. Here’s the query I wrote to do that.
The query makes use of two tables in MSDB, namely [backupset] and [backupmediafamily]. From [backupset], we can get the database name, start and finish times, and the size of the backup. To get the file name, we need to go to [backupmediafamily], and join on the media_set_id. By calculating the difference in seconds between start and finish times, and converting the backup size from bytes to MB, we can get the backup throughput in MB/s.
Also, in this case, I’m filtering for backups that are either full or differential, and are taking longer than 10 minutes. This eliminates backups that only take a few seconds, since those backups will give atrificially low throughput numbers. (If I only backed up 1MB, and it took 1s, then my throughput is 1MB/s even if I could have done more.)
SQL Server Performance Tuning Script – Backup Throughput
SELECT bs.database_name AS DBName ,bs.backup_start_date AS DateStarted ,bs.backup_finish_date AS DateCompleted ,Duration = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ,bs.backup_size / 1048576.0 AS DataSizeMB ,[MB/sec] = (bs.backup_size / 1048576.0) / CASE WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ELSE 1 END ,bmf.physical_device_name AS BackupFile FROM msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id WHERE bs.type != 'L' AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 600 ;
That will give me the backup throughput for each Full or Differential backup running longer than 10 minutes that remains in the backup history. If you want to look at averages for all databases over time, you can group by parts of the datetimes. One way to do that would be like this:
WITH BackupThroughput AS ( SELECT bs.backup_finish_date AS DateCompleted ,[MB/sec] = (bs.backup_size / 1048576.0) / CASE WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ELSE 1 END FROM msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id WHERE bs.type != 'L' AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 600 ) SELECT BackupDate = MIN(DateCompleted) ,AverageThroughput = AVG([MB/sec]) FROM BackupThroughput GROUP BY DATEPART(YEAR,DateCompleted) ,DATEPART(MONTH,DateCompleted) ,DATEPART(DAY,DateCompleted) ORDER BY DATEPART(YEAR,DateCompleted) ,DATEPART(MONTH,DateCompleted) ,DATEPART(DAY,DateCompleted)
With that information in hand, I can check to see if there was an obvious drop in backup throughput, and then investigate that.
Hope that helps.
-David.
About the Author
David Maxwell
UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.
Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions. David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.
Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.
In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.
Learn more about David Maxwell at https://upsearch.com/david-maxwell/.
About UpSearch
UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.