1

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?

Update

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.

Environment Details

Primary -

  • 64 Core / 488 GB
  • 2 NVMe drives for tempdb data and log
  • Nitro Hypervisor

Mirror -

  • 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

Mirroring Config

  • Sync Mirroring High Availability no witness
  • Mirroring 5 databases

Update 2 enter image description here

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
  • Can you give more information about new server, does it has same specification as the one before. Is the storage same ? – Shanky Aug 3 '18 at 10:49
  • Are you encountering any issues due to waiting tasks ? How many dbs are mirrored ? Is the power plan on new server set to high performance ? – Kin Shah Aug 3 '18 at 12:05
  • Updated the question with additional information. – Aaron Aug 3 '18 at 18:52
  • So your mirror is twice + more powerful than your primary :-). Is the disk Allocation size on primary and secondary the same ? Any errors you see in sql server or windows event logs ? – Kin Shah Aug 3 '18 at 19:23
  • @Kin Only errors I see are in the Windows Application Log for a perflib and perflibnet Event ID '-1073740816' in Source 'Perflib' cannot be found Event ID '-1073739820' in Source 'PerfNet' cannot be found Windows System - ** Registering a publisher to Channel Microsoft-Windows-Kernel-ShimEngine/Operational **SQL Log - Cannot use Large Page Extensions: lock memory privilege was not granted. – Aaron Aug 3 '18 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.