All we need is an easy explanation of the problem, so here it is.
We have a large physical server with more than 50 dbs on it; some are quite busy, others very quiet.
When having multiple DBs in an AG, only some of them multiple redo threads. We wanted the databases with those threads to be the busy ones. During a recent migration, we decided to be careful of the order we restored the dbs in, as it was our understanding that this was decided based on databases creation date. However, checking post migration, this isn’t true.
SELECT databases.database_id, databases.create_date, dm_hadr_db_threads.name, dm_hadr_db_threads.num_redo_threads, dm_hadr_db_threads.num_parallel_redo_threads FROM sys.dm_hadr_db_threads INNER JOIN sys.databases ON dm_hadr_db_threads.name = databases.name ORDER BY dm_hadr_db_threads.num_redo_threads DESC OPTION (RECOMPILE)
It isn’t create date or database id. It’s not alphabetical. It isn’t order added to the ag. Does anyone know what decides this?
This is SQL server 2019; the query is from the secondary.
Here is where we found it was based on database_id: https://www.brentozar.com/archive/2018/06/first-responder-kit-release-just-when-you-think-theres-nothing-new-left-to-do/
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Parallel redo threads are assigned in database recovery order, which does follow the
sys.databases creation date.
That said, the separate parallel database recovery feature means each database recovery task can be assigned to a different SOS scheduler (when there are a large enough number of schedulers available).
Say you had 8 databases and 32 processors. The 8 separate recovery tasks would likely be assigned to 8 different schedulers (the SOS abstraction of a CPU). Creation of the 8 tasks (in create date order) and scheduler assignments can happen very quickly.
How soon (and in which order) each scheduler starts executing its assigned recovery task depends on what other work each scheduler has at the time (its runnable queue), and how far through the current quantum any other currently-active task is.
Parallel redo threads are assigned (up to the global limit) soon after each independent recovery task starts executing on its assigned scheduler. This is non-deterministic due to the issue described just above.
Microsoft support have some undocumented trace flags that can help promote good distribution of parallel redo threads in complex scenarios. You should contact them about your situation.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂