7

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)

results

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/

1 Answer 1

10

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.