SQL server backup message

Question

Could you please give little clarification about SQL server backup message

For example :

BACKUP DATABASE successfully processed 19696388 pages in 1945.648 seconds (79.088 MB/sec).

For example we have disk with database and we have separate disk for backup , does it mean that 79 MB/sec is that one disk reads data for backup with speed of 40 mb/sec and another disk writes data with speed 40 mb and as sum it gives us speed of 79 mb/sec , is it correct ?

Answer 1

Lets first get the concept of reader / writer threads out of the way

During a backup, SQL Server creates one reader thread for each volume that the database files reside on. The reader thread simply reads the contents of the files. Each time that it reads a portion of the file, it stores them in a buffer. There are multiple buffers in use, so the reader thread will keep on reading as long as there are free buffers to write to. SQL Server also creates one writer thread for each backup device to write the contents of the buffers out to disk or tape. The writer thread writes the data from the buffer to the disk or tape. Once the data has been written, the buffer can be reused by the reader thread.

Source

Answer

So yes, while the reader threads are reading the pages and the writer threads are writing the pages to the separate disk, the time to process the pages will be impacted by both.

But, this does not mean that the79.088 MB/sec will be divisible by 2.

It means that the read operation was running at ~79.088MB/sec and the write operation was also running at ~79.088MB/sec. The speed is determined by the slowest of these two operations since both operations are reading and writing from multiple buffers (as long as BUFFERCOUNT is set correctly / not added to the command).

As a result, these buffers filling up too slow / not being cleared fast enough is the bottlenecking in action.

Another way to validate this is by calculating the MB/sec * backup duration. This will match your database size minus the space available.

---

Testing how fast you can read

If you want to know the amount of data & how fast it can be read from disk, you can take a backup to 'NUL'

BACKUP DATABASE DBName TO DISK = 'NUL' WITH COPY_ONLY;

In my case I ended up with 122.406 MB/sec

---

Testing how fast you can write

To know how fast you can write, you could use Crystaldiskmark.

Use Seq Q32T1 to mimic the backup operation writes.

Source

This is what pops up on my slow target drive, where I want to back up to.

---

Putting two and two together

If I then run a backup command of my database that resides on the D (data) & L (log) drive to the disk I previously checked with CrystalDiskMark (E).

BACKUP DATABASE DBName TO DISK = 'E:\Folder\DbName.BAK';


Processed 703088 pages for database 'DbName', file 'Database' on file 1.
Processed 2 pages for database 'DbName', file 'Database' on file 1.
BACKUP DATABASE successfully processed 703090 pages in 50.198 seconds (109.424 MB/sec).

We see that the read part of the backup operation is the bottleneck on my system.

Answer 2

BACKUP DATABASE successfully processed 19696388 pages in 1945.648 seconds (79.088 MB/sec).

The speed you see here is a result of simple division of the whole backup duration per volume of data processed.

In your case backup duration is 1945.648 s, data volume processed is 19696388 pages * 8Kb / 1024 = 153.878,03125 Mb

The speed = 153.878,03125 Mb / 1945.648 s = 79,08831980399332 Mb/s and this is what the message is talking about.

In the example of Randi Vertongen:

BACKUP DATABASE successfully processed 703090 pages in 50.198 seconds (109.424 MB/sec).

Data volume = 703090 pages * 8Kb / 1024 = 5.492,890625 Mb

The speed = 5.492,890625 Mb / 50.198 s = 109,4244915136061 Mb/s

This only means that your backup speed is 79,088 and nothing else, there could be 19 seconds to read + 60 to write, but you cannot deduce it from the whole operation time and volume processed.