Can anyone please point me in the direction of an article that could help or please help in shedding some light on how Ola Hallengren Parallel Backup and DBCC processing work. I know that the scripts Queue.sql and QueueDatabase.sql create queue tables which the jobs use to enqueue the next database to perform, but how is the queue made up, or what is the sequence adopted for knowing the next database to process, is it size order, alphabetical order or how?
Thank you
I regularly use parallel execution for index and statistics maintenance but the DatabaseBackup and DatabaseIntegrityCheck can run also in parallel by creating queue tables (Queue, QueueDatabase, etc.).
Each worker job/session pulls the next available row from that queue table, does its work, then marks it done.
This is done with sp_getapplock/sp_releaseapplock around the dequeue to avoid two workers taking the same database.
How the queue is built
When the main procedure starts, it enumerates the list of databases that match your @Databases parameter (e.g. USER_DATABASES, SYSTEM_DATABASES, ALL_DATABASES, or a custom list).
For each candidate database, Ola’s code inserts one row per database into the queue table.
The queue contains metadata (database name, type of operation, options, etc.) that the worker jobs need.
The order of databases
The order is based on the parameter "@DatabaseOrder".
The default (in case of NULL parameter) is by name
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
ORDER BY [name] ASC
You can see this if you look at "IF @DatabaseOrder IS NULL" inside procedures (backup/checkdb/index maintenace). You fill find the temptable that contains databases and a column named [Order] updated based on the parameter "@DatabaseOrder".
If you supply an explicit list in @Databases = 'Db1, Db2, Db3', Ola preserves that order in the queue with the StartPosition column in the @tempDatabases Table.
How workers pick the next database
When a worker finishes, it asks “what’s the first row in the queue not yet claimed?”
That’s done with an UPDATE … OUTPUT pattern that marks one row as “in progress” and returns it to the worker.
So there’s no priority or weighting based on size; it’s just the next unclaimed row, in the insert order.
If you have (say) 4 jobs running in parallel and 20 databases in the queue, the first 4 will grab the first 4 databases in queue order, then keep picking up the next available until all 20 are processed.
That way you don’t have to split databases into separate jobs yourself.
