SQL Server Performance Tuning Script - Backup Throughput Archives

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) &gt; 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) &gt; 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) &gt; 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) &gt; 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/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.

<ol>
<li><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></li>
<li><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></li>
<li><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></li>
<li><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></li>
<li><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></li>

</ol>

Posts