Multiple Basic Availability Groups and Performance - SQL Server

Question

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 -ServerInstance "vm-sqllab1" 
Backup-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab1" -BackupAction 'Log' 
Restore-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance "vm-sqllab2" -NoRecovery 
Restore-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance "vm-sqllab2" -RestoreAction 'Log' -NORECOVERY
$PrimaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab1" -EndpointUrl "TCP://vm-sqllab1.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "vm-sqllab2" -EndpointUrl "TCP://vm-sqllab2.domain:5022" -FailoverMode "Manual" -AvailabilityMode "AsynchronousCommit" -AsTemplate -Version 13
$AgName = "vm-ag-" + $DatabaseName
New-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\vm-sqllab1\Default\" -Name $AgName -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica) -BasicAvailabilityGroup -Database @($DatabaseName)
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\vm-sqllab2\Default -Name $AgName
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\vm-sqllab2\Default\AvailabilityGroups\$AgName -Database $DatabaseName

Answer 1

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

• AlexLostDba: Ultimately we ended up pulling all the availability groups and setting up a cluster. Setting up the AGs using T-SQL did seem alleviate some of our performance issues, but in the end it felt like using that many AGs was still adding overhead. We decided that if we really needed the uptime of availability groups we would be better off purchasing an enterprise license, but for this project we didn't think it was necessary.

Answer 2

Are you creating one Availability Groups per database? Why don't you create just one to control all databases in the same AG?
I believe the resource issue is related with Backup/Restore between both servers. Have you checked the network during this process?