patternsqlModerate
Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?
Viewed 0 times
internalswhatserversqlandrunninglockingperformancewhenhappens
Problem
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).
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).
Solution
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
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
- SQL Server 2008 Microsoft Certified Master (MCM) Readiness videos especially Backup Internals.
- A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - By: Jonathan Kehayias
- A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 2)- By: Jonathan Kehayias
Context
StackExchange Database Administrators Q#104460, answer score: 10
Revisions (0)
No revisions yet.