I just migrated my primary's synchronous mirror to a new server and I've noticed that there are 150 to 230 waiting tasks on the mirrored instance (based on the activity monitor.) I don't recall if the prior mirror had a similar number, however it seem high to be considering the primary only has 9 waiting tasks total and all of them are related to the mirroring process.
I've tried the
sp_BlitzWho and the
sp_whoisactive scripts to try and identify what is executing and waiting however they return nothing for the default execution. When I run
sp_whoisactive @show_system_spids = 1 I see mirroring commands with wait info attached but only around 16 and they are all attached to the master database. Using the plain old
sp_who which returns on average 250 results with a good 2/3 of them being the command
DB MIRROR. They don't seem to be attached to any database and all of the mirrored databases say they are synchronized.
Why would the mirror have so many waiting tasks to synchronize mirror 5 databases with very little data changes happening on the primary? Is this normal behavior?
I've rebooted the mirror and the wait tasks come right back. I've reviewed the disk IO and some of the files will have a 8 to 10 ms wait time on them which doesn't make a lot of sense to me as very little is being transferred from the primary < 100KB per sec on average. Nothing seems impacted by the waiting tasks on the primary or secondary.
- 64 Core / 488 GB
- 2 NVMe drives for tempdb data and log
- Nitro Hypervisor
- 128 Core / 1.9 TB
- (2) SSD drives for tempdb data and log
Xen Hypervisor Common -
VM Windows 2012 R2
- SQL Server EE 2008 R2 10.50.6000.34
- (2) 1.4 TB Drives with 3000 Dedicated IOPS (Data)
- (2) 300 GB Drives with 800 Dedicated IOPS (Log) Only one log per DB
- Power Mode High Performance
- 25 Gb/s NIC
- MAXDOP Set to 8
- Parallelism Threshold set to 50
- Instant Disk Initialization granted to service account
- Sync Mirroring High Availability no witness
- Mirroring 5 databases
All of the waits seem to be focused on DBMIRROR_SEND wait type. I just applied a security update for SQL Server (KB4035316) but that made no improvement on the waiting tasks.
Update 3 sp_Blitz output
- ANSI NULL Default Enabled - Agent Jobs Without Failure Emails - Agent XPs - Backups Not Performed Recently - Database Mail XPs - Database Owner <> SA - Extended Events Hyperextension - Extended Stored Procedures in Master - Forced Parameterization Enabled - Forced Parameterization On - Inactive Tables Without Clustered Indexes - Jobs Owned By Users - Last good DBCC CHECKDB over 2 weeks old - No failsafe operator configured - Possibly Broken Log Shipping - Recursive Triggers Enabled - Server restarted in last 24 hours - Stats Updated Asynchronously - Sysadmins - System Database on C Drive - Tables in the Master Database - TempDB Has >16 Data Files - Transaction Log Larger than Data File - User Databases on C Drive - User-Created Statistics In Place - clr enabled - cost threshold for parallelism - max degree of parallelism - max server memory (MB) - remote admin connections - show advanced options