sqlskills-logo-2015-white.png

Should you ignore DBMIRROR_DBM_MUTEX?

The DBMIRROR_DBM_MUTEX wait type is undocumented. Or rather, it is documented in books online as being “Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.” Paul Randal had some additional information on this wait type in his “Wait statistics, or please tell me where it hurts” post, defining the wait type as “… contention for the send buffer that database mirroring shares between all the mirroring sessions on a server. It could indicate that you’ve got too many mirroring sessions.”

I saw one forum entry where someone said “you can ignore” the DBMIRROR_DBM_MUTEX wait type because, basically, it is undocumented. This assumes that Microsoft only documents wait types that we should care about – but this really isn’t the case.

A few months ago I had a discussion with a DBA about whether or not this wait type was associated with mirroring contention issues as Paul described or whether this really just represented a background process that naturally accumulated time (idle/spin waits). The DBA said this was an idle wait type that can be ignored. I said that it was indeed associated with activity and coupled with other statistics, may point to mirroring related contention.

As with anything undocumented, while you cannot point to official documentation, there is nothing to stop you from setting up a test scenario and evaluating what happens under specific conditions. The “show, don’t just tell” method is always something I prefer, even when I’m confident that a stated fact is correct.

Question 1: Does DBMIRROR_DBM_MUTEX accumulate during inactive periods?

I set up synchronous mirroring with automatic failover for this test (Credit database) and executing the following simple before/after WAITFOR capture of overall accumulated statistics to see which database mirroring related wait stats incremented during inactive (user inactive) periods. SQL Server version was 10.50.2500. The script runs in SQLCMD mode against CAESAR (principal) and AUGUSTUS (mirror)):

— Principal database instance

:CONNECTCAESAR

SELECT wait_type, wait_time_ms, signal_wait_time_ms

INTO tempdb.dbo.before_waits

FROM sys.dm_os_wait_stats

WHERE wait_type LIKE ‘DBM%’;

GO

— Mirror database instance

:CONNECTAUGUSTUS

SELECT wait_type, wait_time_ms, signal_wait_time_ms

INTO tempdb.dbo.before_waits

FROM sys.dm_os_wait_stats

WHERE wait_type LIKE ‘DBM%’;

GO

:CONNECTCAESAR

USE Credit;

WAITFOR DELAY ’00:00:30′

GO

— Principal

:CONNECTCAESAR

SELECT wait_type, wait_time_ms, signal_wait_time_ms

INTO tempdb.dbo.after_waits

FROM sys.dm_os_wait_stats;

SELECTb.wait_type,

a.wait_time_ms b.wait_time_ms wait_time_ms,

a.signal_wait_time_ms b.signal_wait_time_ms signal_time_ms

FROM tempdb.dbo.before_waits b

INNER JOIN tempdb.dbo.after_waits a ON

b.wait_type = a.wait_type

WHERE a.wait_time_ms > b.wait_time_ms

ORDER BY a.wait_time_ms b.wait_time_ms DESC;

DROP TABLE tempdb.dbo.before_waits;

DROP TABLE tempdb.dbo.after_waits;

GO

:CONNECTAUGUSTUS

— Secondary replica

SELECT wait_type, wait_time_ms, signal_wait_time_ms

INTO tempdb.dbo.after_waits

FROM sys.dm_os_wait_stats;

SELECTb.wait_type,

a.wait_time_ms b.wait_time_ms wait_time_ms,

a.signal_wait_time_ms b.signal_wait_time_ms signal_time_ms

FROM tempdb.dbo.before_waits b

INNER JOIN tempdb.dbo.after_waits a ON

b.wait_type = a.wait_type

WHERE a.wait_time_ms > b.wait_time_ms

ORDER BY a.wait_time_ms b.wait_time_ms DESC;

DROP TABLE tempdb.dbo.before_waits;

DROP TABLE tempdb.dbo.after_waits;

GO

Over a 30 second inactive period, the following DBM related wait stats accumulated on the principal:

wait_type wait_time_ms signal_time_ms
DBMIRROR_EVENTS_QUEUE 30043 5

And here is what accumulated on the mirror:

wait_type wait_time_ms signal_time_ms
DBMIRROR_EVENTS_QUEUE 30966 6

No DBMIRROR_DBM_MUTEX to be found.

Question 2: Will DBMIRROR_DBM_MUTEX accumulate during a large data load in synchronous mode?

In my second test, I inserted 1,600,000 rows into the dbo.charge table. Granted – we have all kinds of reasons why there could be contention in a mirroring session including network considerations, synchronous versus asynchronous settings, disk contention for the databases (principal/mirror), memory constraints, 32-bit/64-bit considerations, performance overhead of non-mirrored databases on the same instance or server, number of concurrent mirroring sessions and more.

But in a simple test environment with minimal concurrent activity, 4 scheduler and 2 GB of RAM for the principal and the same for the mirror, can I get DBMIRROR_DBM_MUTEX to appear? I ran the following test:

:CONNECTCAESAR

USE Credit;

INSERT dbo.charge

(member_no, provider_no, category_no, charge_dt, charge_amt, statement_no, charge_code)

SELECT member_no, provider_no, category_no, charge_dt, charge_amt, statement_no, charge_code

FROM dbo.charge;

GO

These are the database mirroring related wait statistics that accumulated on the principal SQL Server instance:

wait_type wait_time_ms signal_time_ms
DBMIRRORING_CMD 253578 2083
DBMIRROR_EVENTS_QUEUE 83949 23775
DBMIRROR_SEND 5017 25
DBMIRROR_DBM_EVENT 12 0

And here is what accumulated on the mirror:

wait_type wait_time_ms signal_time_ms
DBMIRROR_DBM_MUTEX 482105 222
DBMIRROR_SEND 258917 5338
DBMIRROR_EVENTS_QUEUE 84676 3848

Changing to the mirroring session to asynchronous mode instead of synchronous, I saw the following on the principal:

wait_type wait_time_ms signal_time_ms
DBMIRROR_EVENTS_QUEUE 75359 22974
DBMIRRORING_CMD 64219 1655
DBMIRROR_SEND 8740 60

And I saw the following on the mirror:

wait_type wait_time_ms signal_time_ms
DBMIRROR_DBM_MUTEX 113730 105
DBMIRROR_EVENTS_QUEUE 78699 3051
DBMIRROR_SEND 69729 4057

So I saw the DBMIRROR_DBM_MUTEX wait type appear (on the mirror side) for both the synchronous and asynchronous modes during an INSERT of 1,600,000 rows. There are plenty of other tests I could have executed – but I wanted to show just a couple of examples to demonstrate viewing what gets accumulated over a specific period of time (and differentiating between idle waits versus activity-related waits).

Does this necessarily point to a database mirroring performance issue? As I’ve mentioned before, I use wait statistics as an initial pointer so that I’m prioritizing my efforts appropriately. We don’t have enough information to define root cause at this point just based on this data, but what we can derive through testing and observation is which wait types may be seen in conjunction with user activity in a mirrored partnership.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.