I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups.
The application controls latency sensitive industrial machinery and we are finding that when the system has a high transaction volume occurring, the application has queries that run too long (>5 seconds for a brief period of time). These queries only run slow at the same time when the transaction log backups are happening so it appears to be related to the transaction log backups. When the system is performing without problems, our transaction log backups take 2-4 seconds and when we are under a heavier load and we have problems they are taking 6-7 seconds. That apparently is just enough to cause some FIFO message dispatch queues to fill up on the application.
First of all I was under the impression that transaction logs should be pretty transparent to the application, with no locking or anything else going on. Does this point to some kind of IO contention as being invoved if we are seeing database latency when we are doing the transaction log backup. What are the pros and cons of moving to something like a 5-minute transaction log backup cadence instead of 15-minute?
Disk backend is a NetApp FAS2220 with a bunch of 600 GB 10k SAS drives. The DBA is convinced that this is an application problem and not a database problem so I need to know how to troubleshoot this to either being a problem with the application or the database.
TLDR: Database or application latency seen under heavy load during transaction log backup. How to troubleshoot and resolve?