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.