Reindexing Operations, Database Mirroring, and Slow Storage

When planning out the hardware and network configuration for a database mirroring configuration you need to plan for more than just the normal workload on the system. During the night you'll probably be running index maintenance on the database, and when you do this you'll have a massive amount of data which will need to be transferred from the primary system to the mirror system.

Related: Efficient index maintenance using database mirroring

If you haven't planned for these peaks of data traffic you'll end up seeing some performance problems.  When this happens if you are using synchronous mirroring you'll see the index maintenance taking longer than expected. When you are using asynchronous mirroring you'll see the index maintenance taking the normal amount of time, however the database mirroring could fall minutes behind of even hours behind depending on how slow the mirror servers hard drives are or how slow the network is.

If you see these problems when running your index maintenance look at the perfmon counters for the mirror to see if the storage is being overloaded. You'll also want to look at the network latency between the two servers to ensure that the network is up to the workload.

Related: SAN Replication vs. Cloustering vs. Mirroring

Denny

Discuss this Blog Entry 2

on Sep 30, 2013

Too decrease any performance hit or latency on the principal side of database mirroring, we pause database mirroring and then resume it once all required index rebuilds have been completed. We are willing to take the trade off of the mirrored side falling behind compared to time for index rebuilds to occur.

on Oct 1, 2013

Great tip—thank you for your input! Jayleen Heft, Editor

Please or Register to post comments.

What's Troubleshooting SQL Server Storage Problems?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Denny Cherry

Denny Cherry is the owner and principal consultant for Denny Cherry & Associates Consulting and has over a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V,...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×