Multiple Basic Availability Groups and Performance - SQL Server

I'm migrating some of my company's SQL Server boxes to a new SQL 2016 setup. We're currently using database mirroring on SQL 2012. One of the instances I'm migrating currently has about 60 databases and despite the occasional hiccup, mirroring is working fine for us. And for various reasons this instance is running SQL Standard edition. As mirroring is being deprecated, I'm keen to try Basic Availability Groups.

However, I'm running into real performance issues putting together a lab with only 20 databases. During the initial setup, simply creating a new availability group will exhaust the 472 thread limit for Availability Groups (Max Threads - 40). On the primary server I'll see something like this:

time   active_workers idle_workers worker_limit worker_start_success 
--------------------- ------------ ------------ --------------------
12:01  473            0            472          true
12:02  473            0            472          false
... multiple rows elided
12:05  472            451          472          false
12:06  22             16           472          true

Simply trying to view the dashboard for one of the availability groups will peg the CPU for several minutes. This isn't a super powerful VM, but VMWare tells me it has allocated 3014 MHz to it.

This is also without instrumenting any database operations or I/O on the database side.

Are there performance considerations I'm missing? At this point, I feel like Basic Availability Groups won't work for my situation, but I want to be sure I'm not overlooking something. I've leaned heavily on these articles/blog posts:

• Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption

• AlwaysOn Availability Groups – What Not to do when Adding Databases

Finally, this is the PowerShell script I've been using to automate the creation of availability groups. I may be doing something incorrect here:

```
Backup-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile

There are two parts to this. First, make sure you install CU1. Notably it contains a fix for

• Performance issues occur when using the sys.dm_hadr_availability_replica_states DMV on an AlwaysOn Availability Group that contains many databases in SQL Server 2016

The second part is to use T-SQL instead of PowerShell. When I do it this way, the availability groups get created much, much faster and I'm not seeing any of the CPU/thread issues I complained about above. This is the code I'm using to create the Availability Groups with direct seeding. I've adapted it from this blog post:

• SQL Server 2016: Availability Groups, Direct Seeding, and You.

On the primary:

CREATE AVAILABILITY GROUP [vm-ag-TestDb21] 
WITH (BASIC,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)  
FOR DATABASE [TestDb21]
REPLICA ON 
N'vm-sqllab1' WITH (ENDPOINT_URL = N'TCP://vm-sqllab1.domain:5022',  
    FAILOVER_MODE = MANUAL,  
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC),   
N'vm-sqllab2' WITH (ENDPOINT_URL = N'TCP://vm-sqllab2.domain:5022',   
    FAILOVER_MODE = MANUAL,   
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC)   

GO

And on the replica:

ALTER AVAILABILITY GROUP [vm-ag-TestDb21] JOIN
GO
ALTER AVAILABILITY GROUP [vm-ag-TestDb21] GRANT CREATE ANY DATABASE
GO

StackExchange Database Administrators Q#148388, answer score: 3