Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

Question

For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems.

To my understanding this does not apply for the Microsoft SQL Server, but how does the SQL Server handles this? Is there some internal freeze to keep the db consistent?

Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file. Also assuming you have a multicore machine, for example 16 cores, or at least a significant larger number than one.

From my personal experience I never had issues when taking backups, neither locking nor overhead issues, but my experience is limited. That's why I always recommend turning backup compression on in the server properties.

So what happens when a backup job is running? And also are there significant differences for the different versions? for example 2008,2012 and 2014 (not the licenses).

Answer 1

Your all points are covered in backup myths - by Paul Randal

30-01) backup operations cause blocking

No. Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might look like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.

Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file.

A backup when done to a single file or device will use 1 writer thread. So if you are backing up to Multiple files /devices (be that multiple .bak files) will have one writer thread per file/device.

The easiest way to improve backup performance is to allow the backup operation to parallelize, which is known as backup striping. By default, there’s a single data reader thread for each drive letter or mount point being read from and a single data writer thread for each backup device being written to.

Check

1. SQL Server 2008 Microsoft Certified Master (MCM) Readiness videos especially Backup Internals.
2. A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - By: Jonathan Kehayias
3. A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 2)- By: Jonathan Kehayias

Answer 2

The article written by Paul regarding backup internals is excellent one and you must read it. Adding to what others have said and emphasizing on specific part of your question

Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file. Also assuming you have a multicore machine, for example 16 cores, or at least a significant larger number than one.

Backup operation can use parallelism but remember this is Not the parallelism driven by Optimizer in SQL Server its driven by number of disks involved from where backup has to read the data file and where backup writes the data file and amount of backup files created.

You cannot use MAXDOP hint while taking SQL Server backup

You cannot generate execution plan in SSMS for simple TSQL backup operation.

The parallelism driven by query optimizer in SQL Server is basically for operators involved ( actually its more complex but for sake of simplicity you can take this ) since backup operation does not involves any operator as such it cannot use parallelism driven by optimizer.

I wrote an article on Technet Wiki about Backup and parallelism where I used simple examples to explain parallelism during SQL Server backup. Following is the conclusion

1. If database files are on multiple disks backup operation would initiate on thread per device drive to read the data. In same way if restore is done on multiple drives/mount points backup operation would initiate one thread per drive/mount point

2. Even if you are dumping multiple copies of backup on same drive we would have one thread per backup file dumped.

3. The parallelism associated with backup is related to the stripes. Each stripe gets its own worker thread and that is really the only part of backup/restore that one should consider as parallel operations.

4. The max degree of parallelism has no affect on backup operation.

I got some expert opinion on this from Paul and Bob Dorr.

So what happens when a backup job is running? And also are there significant differences for the different versions? for example 2008,2012 and 2014 (not the licenses).

I would suggest you to read this blog.msdn article by Bob Dorr. Some important points he emphasized is

1. When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The target is commonly 4MB for each buffer resulting in approximately 4 to 8 buffers. Details about the calculation are located in: http://support.microsoft.com/kb/904804/en-us

2. The buffers are transitioned between the free and data queues. The reader pulls a free buffer, fills it with data and places it on the data queue. The writer(s) pull filled data buffers from the data queue, process the buffer and return it to the free list.

3. You get a writer per backup device, each retrieving from the data queue. So a a backup command with four(4) to disk specifications will have four writers and a reader. The reader uses async I/O so it can keep up with the writers.

You can enable trace flags 3213 and 3605, both are undocumented so please use it on test environment, and see what interesting message is dumped in SQL Server errorlog. Something Like below would appear

Memory limit: 249MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:            7
Media Buffer size:          1024KB

I am not aware about any significant changes in backup code for various versions, such things are not documented. I only know about the enhancement introduced in SQL Server 2012 SP1 Cumulative Update 2, enable backup and restore from the Windows Azure Blob storage service from SQL Server using TSQL or SMO. Read here

Answer 3

Basically, SQL Server does a dirty copy of all pages on disk. Those pages are likely inconsistent if there is concurrent activity or if there previously way non-checkpointed activity.

Then, SQL Server also copies the necessary part of the transaction log that is needed to bring the out of date pages to the latest version and make everything consistent on restore.

I can't speak to the multi-threadedness of the backup operation. I expect it to be parallelized. How else could you back up a 10TB database on a 10GB/sec IO subsystem?