Beware of DBMIRROR_SEND wait types with databases mirroring
Beware of DBMIRROR_SEND wait types with databases mirroring
I recently encountered a situation where synchronous mirroring had been set up and working fine for several months. However an application started to perform extremely poorly and the cause was not clear. A batch job that would normally take 50 seconds or more was now taking 15 minutes and timing out.
Upon investigation I could see the DBMIRROR_SEND wait predominantly against the job in question. An investigation of the network utilization between nodes revealed it was only using an additional 25% of its normal capacity. However the fact that it was using synchronous mirroring was having a very extreme impact on the processing of batch jobs.
Setting the Mirroring to Asynchronous successfully managed to bring the job run time down to 15 seconds again. In future I'm going to look at automating the change of the mirroring mode before starting and ending any batch jobs. However it does leave the database susceptible to data loss in the event of a disaster.