11

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.

  • 1
    Looking at an issue very similar to this right this moment.. – Dave Lawrence Feb 23 '15 at 14:36
  • Have you looked into possibly high VLFs causing problems ? – Kin Shah Feb 23 '15 at 16:14
  • @Kin do you mean in terms of a slow log flush to disk holding open a PAGELATCH_EX latch and causing latch contention? – Pixelated Feb 23 '15 at 16:23
  • There is no reason an implementation of SQL Server must generate the log record under a page latch. Why would they do it this way? Implausible. Also, if log writes were done under patchlatch you would pretty much never see WRITELOG waits. There can only be one wait type taken at a time. – usr Feb 25 '15 at 12:26
1

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.

  • Hi @Shanky, based on sys.dm_os_waiting_tasks.resource_description coupled with DBCC PAGE and the type of PAGELATCH_* I have ruled out tempDB. Based on the chain of events by Bob Dorr it looks like the "Generate log record" step is completed within the latching mechanism? – Pixelated Feb 23 '15 at 16:41
  • 3
    Both are mutually exclusive events the writing in log file has nothing to do with latching its just SQL Server following WAL protocol to write information in log before actually committing the transaction – Shanky Feb 23 '15 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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