Sql-server – High PAGELATCH_* and WRITELOG waits. Are they related

database-internalssql serversql server 2014sql-server-2008-r2sql-server-2012

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion rate into a busy clustered primary key defined with an IDENTITY value. I understand that this phenomenon is known as last page insert latch contention.

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX as detailed https://www.microsoft.com/en-ie/download/details.aspx?id=26665 Page 24. Or does it write the record to the transaction log first before taking the PAGELATCH_EX as detailed "Resolving PAGELATCH Contention on Highly Concurrent "INSERT Workloads – Background information SQLCAT's Guide to: Relational Engine

If the record is written to log outside of the latching mechanism then I can rule out slow writes to disk as a cause of high PAGELATCH waits. But if the latch is held until the record is hardened to log then I should probably take WRITELOG into consideration.

Also would having multiple non-clustered indexes cause the PAGELATCH_* latch to be held for longer i.e if a table has a clustered and multiple non clustered indexes are latches added and released to each of the index buffer pages concurrently?

Update 1
After reading confio-sql-server-writelog-wait slide two and general WAL architecture. I am now of the understanding that the "Record a log entry that the row has been modified" step detailed in both white papers is referring to SQL Server logging a change in the transaction log cache, not disk. Once the transaction is complete or buffer full all records are immediately flushed to disk.

Best Answer

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX

You must note that latch only protects physical integrity of page while it is in memory so latch would be taken when page is in memory. Suppose a record is being inserted and for that page needs to be fetched. First, page would be locked and brought into memory then it would be latched and information would be written. The process after this would be

  • Generate log record

  • Update page LSN to match that of the log record

  • Change Data (dirty the page)

  • Release Latch

  • Commit Transaction Starting

  • FlushToLSN of Commit

  • Release locks

  • Commit Transaction Complete

For more details and explanation about above steps please read Bob Dorr's I/O presentation blog

Pagelatch* waits are non I/O waits and I have seen most of the time these wait are prominent because of allocation contention. My hunch is that it has to do something with how tempdb is configured. So how is your tempdb configured ?, how much tempdb data files are present ? Make sure they have same autogrowth and same size. When new page is created system pages like GAM,SGAM and PFS pages needs to be updated or are accessed and when SQL Server finds contention in accessing these pages such wait come into picture.

Related Question