lundi 23 février 2015

High PAGELATCH_* and WRITELOG waits. Are they related?


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 recrod 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 exlucisve PAGELATCH_EX as detailed http://ift.tt/1zzw5hf Page 24. Or does it write the recrod 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 the log first, 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 synchronously?





Aucun commentaire:

Enregistrer un commentaire