SQL Server Backup & Performance Counters

Background

Experiencing  longer than expected Production to Development environment backup and restore cycles.

Wanted to capture performance metrics.

Outline

Here are metrics we will consider:

  1. Ongoing Monitoring
    • MS Windows Performance Monitor ( perfmon )
  2. Summary
    • SQL Server Statement

Performance Monitor ( perfmon )

Metrics

Items

  1. SQL Server / Backup Device
    • Device Throughput Bytes/sec
  2. SQL Server, Databases
    • Backup/Restore Throughput/sec

Tabulate

Object Counter
SQL Server, Backup Device Object
Device Throughput Bytes/sec Throughput of read and write operations (in bytes per second) for a backup device used when backing up or restoring databases. This counter exists only while the backup or restore operation is executing.
SQL Server, Databases Object
Backup/Restore Throughput/sec Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.

Select Metrics

Selections

Selection – SQLServer:Databases

performanceCounter.choose.SQLServer.Databases.20180914.1202PM.PNG

Selection – SQLServer:Backup Device

performanceCounter.choose.backupDevice.20180914.1045AM.PNG

Captured Metrics

Captured Metrics

Image – 01

performanceCounter.20180914.1137AM.PNG

Image – 02

performanceCounter.20180914.1130AM.PNG

Image – 03

performanceCounter.20180914.1128AM.PNG

Backup Statement Output

Metrics

Output #1

backupDB.20180914.1215PM

 

Summary

  1. Physical Disk/Disk Writes/sec divided ( / ) by Physical Disk/Avg. Disk Bytes/sec
    • Disk Writes/sec
  2. SQLServer:databases Backup/Resource throughput/sec Versus Physical Disk/Disk Writes/sec
    • Likely SQLServer:databases Backup/resource Throughput/sec will be a multiplier of the Physical Disk/Disk Writes/sec.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s