Our web application suffers DB query timeouts a few times a day at random times. Can you help to work out the cause, e.g. deadlocks, mirroring (disk or network), etc?
We have two identical database servers (and a third witness server) with MSSQL 2008 R2 mirroring in high safety mode with automatic failover. Two DBs are mirrored and each server is principal in one so in the event of a failure, both DBs will run from one server. As far as I can see, the problem only occurs on one of these two databases.
Using performance monitor, I can see the "Log Harden Time (ms)" on the principal server spikes to 212,301.206ms at the exact time a timeout occurred (07:30 today). The average from a 24hr period is 22.712ms.
What else should I be looking at to determine the cause of this slowdown?
Best Answer
Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database). Database mirroring applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes.
I know we have two of the three issues listed above, but if it cannot communicate with the mirror it just holds the transactions in the transaction log until they are able to be applied. It doesn't use any form of locking or modification of the data since it is being read from the log file.
So look at Log generation rate, network throughput, and I/O throughput, influence the deployment of database mirroring.
References: http://msdn.microsoft.com/en-us/library/ms189852.aspx