Authors: Sanjay Mishra, Prem Mehra
Technical Reviewers: Lubor Kollar, Michael Thomassy, Lindsey Allen, Mike Ruthruff, Kaloian Manassiev, Kevin Cox, Thomas Kejser, Mike Weiner, Mark Souza, Joseph Sack, Glenn Berry (SQL Server MVP)
A frequently asked question regarding database mirroring is: How many databases can be mirrored in a single instance of Microsoft® SQL Server®? This question is often raised by customers who are consolidating many databases into fewer instances and the high availability or disaster recovery service-level agreement (SLA) requires deployment of database mirroring.
Sometimes the question is raised when readers misconstrue the restriction documented in the SQL Server Books Online stating that “On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.”
It is important to mention two significant points:
This article explains the number of worker threads required for database mirroring for each database and illustrates the observed performance of an application with many databases. System administrators and database administrators may find this information useful as they examine, test, and deploy systems in the production environment.
The number of threads used on a server for database mirroring depends upon:
The following table summarizes the number of database mirroring threads used.
Role of server
Number of threads for the specific function
Database mirroring communications
1 per instance
1 per mirrored database
FLOOR ((number of logical processors +3) / 4)
In summary, the number of database mirroring threads:
For example: If your principal and mirror servers have 8 logical processors each, and you are mirroring 20 databases, the total number of database mirroring threads used on the principal will be 41, and the total number of database mirroring threads used on the mirror will be 101.
To view the threads used for database mirroring, query the DMV sys.dm_exec_requests.
SELECT SESSION_ID, STATUS, COMMAND, WAIT_TYPE
FROM SYS.DM_EXEC_REQUESTS WHERE COMMAND = 'DB MIRROR'
Here is sample output from this query on the mirror server with 8 logical processors, and with one mirrored database.
To illustrate database mirroring in a consolidated environment, we ran tests with a workload that continuously inserted data into a number of databases. The databases were deployed with synchronous mirroring between two identical servers (for more information, see “Test Hardware and Software” in the Appendix). The application opened 20 connections to each database, and each connection continuously inserted 6,000 rows (one by one) into the database. The insertion rate of the workload scales linearly as we added more databases.
The focus of our tests was to demonstrate the ability to mirror multiple databases in a consolidated database environment. As an aside, we also observed linear scalability of the workload throughput. For more information about database mirroring performance topics, see Database Mirroring Best Practices and Performance Considerations.
Number of databases mirrored
Transaction throughput (batch requests/sec)
Number of all threads (principal)
Number of DBM threads (principal)
Number of all threads (mirror)
Number of DBM threads (mirror)
The line “Number of Threads (Mirror)” shows the total number of threads used on the mirror server. This includes the number of threads used by database mirroring represented by the line “Number of DBM Threads (Mirror).”
There are two additional considerations – one on application throughput, and another on ping traffic.
As an aside, note that the overall system performance may be impacted by the number of threads available for processing the workload:
For the database mirroring session for each database, the servers send and receive a ping from each other. When several databases are mirrored, the servers will send each other many ping messages within a short interval. To keep the database mirroring connection open, a server must receive and send a ping on that connection within the timeout period.
If the database mirroring partner timeout interval has been set too low, false failovers may occur (if a witness is enabled), or the database mirroring session may switch between disconnected and synchronizing states (if there is no witness).
We recommend that you set the timeout interval to a value of 10 seconds (default) or higher, and if you see unexpected timeouts, increase it to a higher value.
With proper planning, it is possible to mirror hundreds of databases in an instance. Use the information provided in this article for planning, and perform thorough testing with your workload to ensure successful deployment.
All the tests were performed in the following hardware and software environment.
Two Dell PE6950 servers (used as principal and mirror), each with:
One Dell R805 server (used as client), with:
One 3PAR SAN, with:
Pingback from Mirroring a Large Number of Databases in a Single SQL Server … Mobile
Pingback from Mirroring a Large Number of Databases in a Single SQL Server … | Drakz Free Online Service
Nice job Sanjay and Prem. Glad to see this published.
Microsoft SQLCAT team members Sanjay Mishra and Prem Mehra have published a new Technical Note called
This post was mentioned on Twitter by jameslean: How many DBs can you mirror on a single #sqlserver instance? Probably more than you think, assuming 64 bit: http://bit.ly/dpJkEM
This is an excellent write up on mirroring. Planning the cost of mirroring can be hard and often overlooked until it is a problem. With these types of articles out there is will help a lot of people questioning the performance and planning needs before they throw mirroring into an environment.
Great job Sanjay and Prem!
Pingback from Well Defined Structural Drawings ??? Improve Your Building's Quality | Structural Engineering Addict
Pingback from MikHo Software » Blog Archive » SQL Server 2005 Mirroring Limitation – Maximum Mirrors?
What would I need to consider for SQL 2005?
I don't like the suggestion of increasing max worker threads. People are taking this to mean that they can mirror any number of databases simply by increasing the # of threads.
Ultimately, the system has to get all of those threads down to a single thread per logical CPU. There's no work-around for that. Adding more worker threads just increases the number of tasks that can be waiting for a CPU thread. It doesn't actually increase the capabilities of the server. Under a light load, it will work fine, but if the server is busy, it's going to cause a lot of waits.
Lately I’ve noticed an increasing number of people recommend increasing worker threads. The reasons for the recommendations include things as simple as a troubleshooting step for reports of general system slowness or as complex as a means to mirror very
I expanded on my opinion about the worker threads statement above on my blog. I think it makes a nice companion piece to this paper.
The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads: www.sqlservercentral.com/.../The-Toilet-Analogy-_2620_-or-Why-I-Never-Recommend-Increasing-Worker-Threads.aspx
Response to Various Comments by Robert Davis:
The bottomline is this:
1) Understand how things work. Know how many threads are used for what purpose.
2) Test your configuration with your workload. Every configuration and every workload is unique.
At this point, it is important to warn the readers to NOT follow the guidance on how many databases you can mirror that is based on the assumption that mirroring each database requires 25.6 threads (incorrect interpolation based on a 32-bit recommendation). I have seen a blog floating around that follows this wrong assumption and wrong recommendation. That is misleading the readers.
I agree completely Sanjay. I think this paper here is a great paper with a lot of valuable information. I have directed others to this paper to help them understand the cost of planning out large mirroring deployments.
How many mirrrored databases can I have on a server? 10 mirrored databases per server is the recommendation for a 32 bit computer WITH 4 processors or less. 64 bit computers have twice as many threads which means, in theory, that you can have twice as