SQL Server Database Backup Internals

The estimated reading time for this post is 4 minutes

How SQL Server Backup Works

bckup1

Once the ‘Backup Database’ command executed, SQL Server automatically does few ‘Checkpoint’ to reduce the recovery time and also it makes sure that at point of command execution there is no dirty pages in the buffer pool. After that SQL Server creates at least three workers as ‘Controller’, ‘Stream Reader’ and ‘Stream Writer’ to read and buffer the data asynchronously into the buffer area (Out of buffer pool) and write the buffers into the backup device.

Be aware that ‘Stream Reader’ thread is one per volume and ‘Stream Writer’ thread is one per backup device.

SQL Server Backup Buffer Exchange

backup2

As mentioned before, SQL Server backup process creates buffer for backup purposes. The ‘Stream Reader’ thread loads the data into buffer area asynchronously and the ‘Stream Writer’ thread writes the data into the backup device, once the buffer data been written into the backup device the buffer goes under ‘Stream Reader’ queue to be filled up and later on goes to ‘Stream Writer’ queue to be emptied. The count of buffer are vary and the buffer size can be from 64kb to 4MB.

For example, based on the above figure our backup buffer can be either minimum 384KB or maximum 24MB.

SQL Server Backup Options

  • BufferCount
    • Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.
  • MaxTransferSize
    • Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
To get the buffer count value, you can use the following formula:

BufferCount = #ofBackupDevices x 3 + #ofBackupDevices + (2 x #ofVolumeInvolved)

How Many Concurrent Backup Operations is Supported

backup3

remaining virtual address space must be considered for concurrent backup operations.   For example on a 32 bit system you get into the ‘memory to leave’ discussion but on 64 bit it is not a ‘memory to leave’ discussion, therefore how much you shrink the buffer pool memory to support allocations outside the buffer pool is considered.  The direct memory consumers to consider are: The stream threads (one per output target) are threads so the stacks are allocated by the operating system outside the buffer pool.  (x86 = .5MB, x86 WOW = .75MB, x64 = 2MB, …) The backup buffers are larger than 8K so they are also allocated outside the buffer pool.

So to find how many concurrent backup can work together, we need to do some calculation as following:-

NumOfTotalWorkers: #ofSchedulers x 255 – 200

NumOfBackupWorkers: 2 + #OfVolumeInvolved + #OfBackupDevices

Concurrent Backups = NumOfTotalWorkers / NumOfBackupWorkers

For example:

NumOfTotalWorkers = 4 x 255 – 200 = 820

NumOfBackupWorkers = 2 + 2 + 4 = 8

Concurrent Backups = 820 / 8 = 102

SQL Server Backup Workers and Tasks

With use of the following script you can capture the backup internal threads and their last wait type as well.

WHILE (1=1)
BEGIN
INSERT INTO TasksTB
SELECT R.command, T.task_address, T.session_id,T.pending_io_byte_average,T.pending_io_count,T.task_state,W.memory_object_address,
W.last_wait_type,W.status,W.STATE,
TH.stack_bytes_committed,TH.status [thread status],sysdatetime() AS [TIMESTAMP] --into taskstb
FROM sys.dm_os_tasks T
INNER Join sys.dm_exec_requests R ON R.session_id = T.session_id AND R.command = 'BACKUP DATABASE'
INNER Join sys.dm_os_workers W ON W.worker_address = T.worker_address
INNER Join Sys.dm_os_threads TH ON TH.worker_address = W.worker_address
END

 

Run the above script in a session and open another session and run the following script once the backup operation is done.

SELECT DISTINCT [command],[task_address]
      ,[session_id]
      ,[last_wait_type],memory_object_address,TIMESTAMP
  FROM [TempData].[dbo].[TasksTB] WHERE TIMESTAMP = '2016-04-16 16:48:52.2217219' ORDER BY last_wait_type

 

Make sure that you filter it by one of the timestamp values to see how many threads were running during backup operation.

Based on the following backup command, and use of above scripts, we found the number of backup worker threads as the following figure:-

BACKUP DATABASE Tempdata TO DISK = 'C:\temp\backup1.bak', DISK = 'C:\temp\backup2.bak',
DISK = 'C:\temp\backup3.bak',DISK = 'C:\temp\backup4.bak' 
WITH Init, Format, Buffercount =8 , Maxtransfersize=65536;

 

backup4

SQL Server Backup Operation Phase Breakdown

The following is SQL Server error log output using trace flag 3004 to collect extended information.   Trace flag 3605 is also required to force the output into the error log.

I copied the normal messages to show the phase breakdowns better.   Looking at the detailed messages you can see what is going on.

This trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.

Database TempData
Starting up database 'TempData'.
Backup: Media open
Backup: Media ready to backup
Backup: Clearing differential bitmaps
Backup: Bitmaps cleared
BackupDatabase: Checkpoint done
BackupManager::SyncWithLog: SEReplLSN : 0 (0x00000000:00000000:0000)
BackupManager::SyncWithLog: Start LSN : 282000000023200037 (0x0000011a:000000e8:0025)
Backup: Scanning allocation bitmaps
Backup: Done with allocation bitmaps
BackupDatabase: Work estimates done
Backup: Leading metadata section done
Backup:Copying data
Backup: DBReaderCount = 2
…..
BackupDatabase: Database files done
Backup:Copying log
BackupDatabase : Log files done
Backup: Trailing config done
Backup: MBC done
BackupDatabase: Writing history records
Database backed up. Database: TempData, creation date(time): 2015/10/20(20:05:41), pages dumped: 1553, first LSN: …
Writing backup history records
BACKUP DATABASE successfully processed 338 pages in 0.087 seconds (30.267 MB/sec).
BackupDatabase: Finished

 

Conclusion

Based on understanding the SQL Server Backup Internal operations and how it works you can optimize the database backups.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz