Do you know the Maximum number of databases that can be mirrored?

Recently I came across an interesting scenario where the SQL Server was frequently generating Deadlock Scheduler mini dumps on the mirrored SQL Server instance. In this post I put together various pieces of information, so thought of sharing with you.

Scenario: Suppose you are using SQL Server 2008 R2 Enterprise x64 edition installed on windows Server 2008 SP1. You are using SQL Server database mirroring feature and has setup database mirroring on 104 databases. You are frequently getting Deadlock Scheduler dump (Error 17884) on the mirror site, even though you restart SQL Server instance on the mirror site, still the Error 17884 deadlock scheduler appears within few minutes on the mirror site.

After analyzing the Deadlock Scheduler mini dump, generated on the very first occurrence of error 17884.

Important to note, SQL Server generate mini memory dump only on the first occurrence of Error 17884 and for the subsequent occurrences, it simply job an entry in the SQL Server error log something like

"New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 360 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the ""max worker threads"" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 10%%. System Idle: 88%%."

I found that the following:

Ø  There were 464 workers performing Parallel operation (i.e. Query Parallelism).

Ø  No lock blocking seen at the Server.

Ø  There were 372 workers performing DBM redo operation.

Total workers: 710

Workers in RUNNABLE state: 0

As per SQL Server configuration settings

name	minimum	maximum	config_value	run_value
max worker threads	128	32767	704	704

Before I proceed further Lets check the Mirror Server configuration

Intel(R) Xeon(R) CPU           L5630  @ 2.13GHz. Bios Version is HP  - 2

16 x64 level 8664, 2 Mhz processor (s).

Total Physical Memory = 49141 MB           
Available Physical Memory = 45098 MB       

For 64 bit operating system:

If total # of available logical CPU’s > 4 :   

Max worker threads = 512 + ((logical CPUS’s - 4) * 16

In our case max worker threads = 512 + ((16 - 4) * 16=704  which is default no. of worker thread SQL Server will spawn.

You must be wondering Max Worker Threads setting is correctly set as per above calculation

Before I comment on this, let’s talk about Database mirroring requirements for worker threads

·        Principal server:

1 global thread and 2 threads per each of the mirrored databases.

·        Mirror server: 64 bit architectures:

1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.

In short, the number of thread needed on mirror server are greater than those on the primary, so you need to use the mirror server system configuration settings to calculate the no. of databases that can be mirrored.

Number of threads required per mirrored database = (2+16/4) = 6 threads per database. Where 16 is the number of logical CPU cores on the mirror server

Maximum number of databases that can be mirrored = 704/6 = 117

Now you must be wondering, in our scenario, we are just mirroring 104 databases (which is less than 117 i.e. Max databases that can be mirrored), then what could be the cause error 17884 (Deadlock Scheduler) occurring too frequently on the mirror server.

In Summary, not all the 704 workers would be available to perform mirroring activities as some threads are assigned to core database activity on the server. Hence you have to set the value of max server thread to 1024 and you will be able to bring your mirror server back online and operational.

Additionally, you can run the below query to determine the number of worker threads available on your SQL Server.

select count(*) from sys.dm_os_threads

In my case it returned 759