Few days back one of the DBA in our company setup mirroring for one of our production database hosted in SQL Server 2008 R2. After setting mirroring up, our peak business hour started and we have started facing heavy blocking issues. Thanks to customized monitoring application we have developed which shows me that mirroring was in “Synchronizing” status not “Synchronized” and there were so many wait type “DBMIRROR_DBM_EVENT” which was blocking many sessions. Apart from that I have found that mirroring was having “High Safety” mode.
In highly transactional database, I prefer to have asynchronous mirroring “High Performance”. As soon as I changed the mirror safety mode and kill some heavy blocking process everything was up and running fine.
As per MSDN, High-performance operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.
So, High-performance comes at the cost. If it would be “High Safety” it wouldn’t release until data successfully reached to mirror but it take lot of resources so I used to keep “High Performance” for one of our highly transaction database only.
BTW, for checking mirroring safety mode and status, I have already written one script earlier in my blog post which is give here again:
SELECT DB_NAME(database_id) As DatabaseName, CASE WHEN mirroring_guid IS NOT NULL THEN 'Mirroring is On' ELSE 'No mirror configured' END AS IsMirrorOn, mirroring_state_desc, CASE WHEN mirroring_safety_level=1 THEN 'High Performance' WHEN mirroring_safety_level=2 THEN 'High Safety' ELSE NULL END AS MirrorSafety, mirroring_role_desc, mirroring_partner_instance AS MirrorServer FROM sys.database_mirroring GO
For wait_type, you can execute following DMV, which I have already explained in my blog post “CXPACKET wait stats in SQL Server”.
select * from sys.dm_os_wait_stats where wait_type='DBMIRROR_DBM_EVENT' GO
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.