How much is crash recovery parallelized? In which order are databases recovered?

How much is crash recovery parallelized? In which order are databases recovered?

Rate This
  • Comments 4

Following is the answer I provided to the interesting question asked by my colleague Pat Martin about the insides of crash recovery in SQL Server.

 

The iterator used to scan the catalog for the databases that need startup and recovery returns them sorted by database id. So, if you want a database to start its recovery before any other, you need to make sure its database ID is smaller.

Now, avoiding any subtle condition that may vary this general behavior, it goes like this. SQL Server first calculates a maximum number of threads it will dedicate to startup DBs in parallel. To do so, it follows this algorithm:

1.- If TF 3408 is enabled, then single threaded recovery is forced.

2.- If the number of active server sessions (SPIDs) configured in that instance at that point (let’s call this number ASS) is less than eight times the number of processors seen by SQLOS in that instance, it limits to ASS/2 (half of ASS) for parallel database startup. ASS, at the point where database recovery starts, will typically be 2048, unless the DBA has set a lower number than that using sp_configure ‘user connections’ in which case that will be the resulting number.

3.- If none of the two previous conditions are met, then it dedicates a maximum of 4 * # processors seen by that instance for parallel startup.

Once it has come with that maximum theoretical number of threads to parallelize DBs startup, if the total number of databases to recover is less than that number, it caps down the number of threads to match the number of databases.

And finally, it tells SQLOS that it wants to enqueue all that work, and that it wants it to be completed by the resulting number of worker threads from the previous calculation. If the invoked SQLOS’ API fails to accept the queuing command (it typically is an indication that it was unable to create as many threads as we wanted), then we retry in a loop with half the number of threads passed in the previous attempt, until we reach a minimum of one.

To illustrate it, I’ll use three sample cases:

First case:

· Running on a machine with 8 cores, all of them visible by that instance of SQL.

· sp_configure ‘user connections’ hasn’t been used, so the value is configured by default as 0. The maximum number of sessions that will be permitted in this instance is 32767, but at the point the server attempts to start and recover the user databases, it has configured the structures needed to keep track of active sessions for as many as 2048.

· 8 databases whose Database ID is greater than MODEL’s, which is 3.

· TF 3408 is not enabled.

Given all those facts, point one above is out of the game. As for point two, let’s see. ASS == 2048. CPUs visible by the instance multiplied by eight gives 64. Our ASS is not smaller than 64, so point two doesn’t affect us here. This means it will dedicate a maximum of 32 (4 * 8) threads for parallel DB startup. Since the number of databases that need to be started and recovered is only 8, that is the number of threads it will request from SQLOS to complete the eight tasks.

Second case (highlighting what has changed from previous scenario):

· Running on a machine with 8 cores, all of them visible by that instance of SQL.

· sp_configure ‘user connections’ hasn’t been used, so the value is configured by default as 0. The maximum number of sessions that will be permitted in this instance is 32767, but at the point the server attempts to start and recover the user databases, it has configured the structures needed to keep track of active sessions for as many as 2048.

· 500 databases whose Database ID is greater than MODEL’s, which is 3.

· TF 3408 is not enabled.

Given all those facts, point one above is out of the game. As for point two, let’s see. ASS == 2048. CPUs visible by the instance multiplied by eight gives 64. Our ASS is not smaller than 64, so point two doesn’t affect us here. This means it will dedicate a maximum of 32 (4 * 8) threads for parallel DB startup. Since the number of databases that need to be started and recovered is more than 32. It will stick to that number and will ask SQLOS to complete the 500 recoveries using 32 threads.

Third case (highlighting what has changed from previous scenario):

· Running on a machine with 8 cores, all of them visible by that instance of SQL.

· sp_configure ‘user connections’ has been run to configure the setting to 7. The maximum number of sessions that will be permitted in this instance is 7.

· 500 databases whose Database ID is greater than MODEL’s, which is 3.

· TF 3408 is not enabled.

Given all those facts, point one above is out of the game. As for point two, let’s see. ASS == 58. The 58 comes from adding up the 7 configure by the DBA plus the 51 reserved by SQL Server itself for server processes. That results in 58 sessions. CPUs visible by the instance multiplied by eight gives 64. Our ASS is smaller than 64, so condition defined in point two is met and therefore the number of threads will be half of ASS, which is 29. Since the number of databases that need to be started and recovered is more than 29. It will stick to that number and will ask SQLOS to complete the 500 recoveries using 29 threads.

Everything explained here is based on the implementation made for SQL11 RTM.

 

An additional note I would like to add to my original answer has to do with what databases are considered for startup. If SQL Server is running the Express edition of the product it will not recover any user database up until the point where it is used. Also, any database whose AUTO_CLOSE setting is enabled won’t be recovered and therefore won’t add to the count of databases that need to be recovered (unless trace flag 3660 is globally enabled). When TF 3660 is globally enabled, SQL Server recovers user databases even if their AUTO_CLOSE setting is enabled or even if the SKU of SQL Server would recover them lazily, only upon usage.

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • Interesting, thanks for passing this on.

  • Hi Nacho Alonso Portillo

    I would like to confirm something in the post, is 3408 a new traceflag or should that have been 3608 - Prevents Instance from automatically starting-recovering any database except master. Databases will be started and recovered when accessed.

    Clive

  • Hi Clive,

    3408 is used to "force single threaded recovery". It prevents parallelizing the recovery of databases. It isn't new but, indeed, it's different and for a whole different purpose than 3608.

    Thanks,

    Nacho

  • Hello Nacho,

    Thanks for the interesting post! One quick question:

    Does your statement "The iterator used to scan the catalog for the databases that need startup and recovery" imply that before recovery is run on any DBs, the boot pages for all DBs are opened to see if they were cleanly shutdown, or need recovery? For example, if a SQL instance has, say, 30 DBs (database IDs 5 through 34), and in this example 5 of them were cleanly shutdown (for example, IDs 18 through 22), will the cleanly-shutdown ones be online right away?

    Or will the cleanly-shutdown ones have to wait until "their turn" (in database ID order) until SQL sees that they were cleanly shutdown and can come ONLINE without any recovery?

    Thank you!

    Aaron

Page 1 of 1 (4 items)