Help: SQL Server

This is one of the common question asked. This blog has list of resources which can be useful in getting answer. First lets look at books online.

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)
http://msdn.microsoft.com/en-us/library/ff878487.aspx

Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine. Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times for AlwaysOn system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.

In general, the more databases that are replicated and the more secondary replicas that exist – the more worker threads and more memory that will be consumed just to have the AlwaysOn infrastructure.  As the text above indicates, there is no enforced limit, but the more you have the more worker threads and memory will be needed.   If there are insufficient worker threads you will probably see error messages in the SQL Error log similar to:

“The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads.  This may degrade AlwaysOn Availability Groups performance.  Use the "max worker threads" configuration option to increase number of allowable threads.”

If starved for memory, you could see many different error messages – that may or may not look like they relate to AlwaysOn. One possible message could be:

“Could not start the AlwaysOn Availability Groups transport manager. This failure probably occurred because a low memory condition existed when the message dispatcher started up. If so, other internal tasks might also have experienced errors. Check the SQL Server error log and the Windows error log for additional error messages. If a low memory condition exists, investigate and correct its cause.”

Here are other blogs which explain the number of threads in worker pool to support availability group.

AlwaysOn – HADRON Learning Series:  Worker Pool Usage for HADRON enabled databases
http://blogs.msdn.com/b/psssql/archive/2012/05/17/alwayson-hadron-learning-series-worker-pool-usage-for-hadron-enabled-databases.aspx

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/07/15/monitoring-sql-server-2012-alwayson-availability-groups-worker-thread-consumption.aspx

Hope this helps.