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

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 has more than a decade of experience managing SQL Server and is currently the senior DBA at Phreesia. He is a Microsoft MVP and holds several Microsoft certifications, including being an...
Blog Archive