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?
Best Answer
It sounds like your transaction log probably has a lot of VLFs on it which causes the SQL Server to take a hit when doing the transaction log backups. You can find this out by running
The important part to look at is the number of rows which are returned. If there are more than about 100 rows returned then you've got problems (please comment with the number of rows that are returned).
The fix is pretty easy. Make a note of the size of the transaction log. Shrink the transaction log down to as small as possible (the above command will return 2 rows). This is done using the DBCC SHRINKFILE command. You may need to run the shrink command a few times to get it small enough. Try waiting a minute between shrink commands for the logs to cycle and run a log backup just before running the shrinkfile command.
Then once the transaction log file is as small as possible grow the file in 8000 meg chunks using either the GUI in SQL Server Management Studio or by using the ALTER DATABASE command.
If you need the file to be larger than 8000 Megs do 8000 megs in size, then 16000 megs in size, then 24000 megs in size always growing 8000 megs at a time. Compared to the prior size of the transaction log grow the low to the 8000 meg mark past the size that the log was.
If this doesn't do the trick let me know.