September 1, 2015 at 7:23 am
Hi All,
Last Sunday on our Primary server I saw some blocking and the First spid that blocked everything was waiting on LogBuffer wait. At that time the server was not running hot and our DB is on SSD's. There was no memory pressure(1 TB of memory on the server) It is strange why we should get LogBuffer wait when we are running on the fastest disks possible and there was not much action going on the DB.
-Roy
September 1, 2015 at 12:08 pm
Could be just a timing issue with the log backups. It could be a single large transaction that had to wait for the buffers to clear in order to keep going. If you're not seeing it regularly, especially if you're not also seeing log write waits, it may not be an issue at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2015 at 12:15 pm
It wasnt log back up for sure. This happened at 11:44. Our Log back up runs at 11:55. The batch request per second never went more than 1600 per sec during that time. We usually run at 3k per second. Hopefully it wont happen again. Thx
-Roy
September 1, 2015 at 12:26 pm
What is the transaction log setup like? EG each txlog on its own LUN, or do any of the txlogs share with other files?
There are 128 transaction log buffers per database. But there is a lower limit of simultaneous writes allowed to the transaction log. If each of the txlog writes are at the full 60kb allowed, only 32 writes can be inflight at a time. That leaves 96 in-memory txlog buffers. If there is 5 minutes (or even less) of fairly high write latency, first the concurrent write slots will fill up, then the in-memory log buffers will fill - then log buffer waits.
Log buffer waits can occur even without a high transaction/second rate or high batch/sec rate. For example, index rebuilds of large indexes in full recovery mode (for AGs or otherwise) will have fairly low transaction and batch rates, but they'll fill the 60k individual transaction log write limit pretty easily. Run large rebuilds at high concurrency, or at high parallelism, and even a slight bump in write latency to the transaction log LUN(s) can result in log buffer waits.
September 1, 2015 at 12:41 pm
Since all the disks are SSDs we did not think of separating the TxLog into a separate LUN. We have seen ASYNC Network IO Waits but never Disk IO. That is what baffled me. We have had great performance on our DB till we failed over to the secondary.
The secondary is a replica of the Primary HW wise. I did see a bit of parallelism during that time. More than usual.
Jimmy May asked me to look at Grooming and garbage collection. I am going to read up on that as well.
-Roy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply