How SQL Server Backup Works
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.
SQL Server Backup Buffer Exchange
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
- 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.
- 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).
How Many Concurrent Backup Operations is Supported
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.
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:tempbackup1.bak', DISK = 'C:tempbackup2.bak', DISK = 'C:tempbackup3.bak',DISK = 'C:tempbackup4.bak' WITH Init, Format, Buffercount =8 , Maxtransfersize=65536;
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.
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
Based on understanding the SQL Server Backup Internal operations and how it works you can optimize the database backups.