This contents is based on the response I gave to Pat Martin, a Principal Premier Field Engineer and good colleague of mine, who asked just this question a while back. This is what I found:

The iterator used to scan the catalog for the databases that need startup and recovery returns them sorted by database id.

Now, avoiding any subtle condition that may vary this general behavior, it goes like this. It 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.