1

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?

Performance Monitor

Improve this question
4
  • What kind of storage is backing these servers? Are you doing index maintenance or other types of bulk operations in these databases?
    – Jon Seigel
    Commented Jun 17, 2014 at 14:23
  • @JonSeigel, we're not doing any index maintenance or other bulk operations at this time. Around 1am there is a lot of data synced from external services, however there are very few users online at that time. The servers have 2x 300GB 15k SAS drives, RAID 1, split into two logical disks. Windows is on one logical disk and both the MDF and LDB files are on the other logical disk.
    – Marcus
    Commented Jun 18, 2014 at 9:16
  • @Marcus Start running sp_WhoIsActive to a physical table - at frequent intervals to find out what is causing slowness.
    – Kin Shah
    Commented Oct 9, 2014 at 19:29
  • did this just start occurring or has it been happening for a while and has just got worse?
    – armitage
    Commented Oct 9, 2014 at 20:25

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.