How It Works: How many databases can be backed up simultaneously?
Previously I have posted details on backup/restore designs and how to tell what backup/restore is doing.
- https://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources.aspx
- https://blogs.msdn.com/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx
- https://blogs.msdn.com/psssql/archive/2008/01/23/how-it-works-what-is-restore-backup-doing.aspx
This post attempts to bring the concepts together to answer the question: "How many databases can be backed up simultaneously?"
The figure should look familiar as a combination of the previous posts. The reason I have repeated it is to remind us all of the entities involved.
Entity: Worker Pool
The controller and database worker come from the worker pool. So if your max worker thread is configured for 255 the answer would be a max of 255 / 2 = 127 databases. You probably won't get to 127 with this configuration as you have system workers (lazy writer, checkpoint, log writer, and others) that already are using workers.
Entity: Memory
The backup buffers are allocated outside the buffer pool memory so the remaining virtual address space must be considered. For example on a 32 bit system you get into the 'memory to leave' discussion. On 64 bit it is not a 'memory to leave' discussion but how much you shrink the buffer pool memory to support allocations outside the buffer pool. 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. See previous posts to determine the selected buffer sizes.
- When you add VDI into the picture the VDI providers memory is also allocated outside the buffer pool. The VDI provider can tell you the memory requirements.
The answer to the question becomes an exercise in looking at both of these key entities to determine how many of operations you can accommodate on the specific system.
Bob Dorr - Principal SQL Server Escalation Engineer