Latching and latch contention in SQL Server

 What is latching?

Why it is taken and what kind of types are there I'll also talk about how latch contention happens and what factors impact LATCH contention.

We know that data rows in SQL Server are saved in 8 kilobyte object called pages these pages are saved in MDF data files in disk.

Now imagine a client is making an update query as soon as this request come to SQL  Server as thread is allocated in available CPU to open a session and serve this request.

what does this thread do is first it finds a rows via update command is referencing,  then find the page where this row is located.  then it checks whether this page is in buffer pool or not.  If the page is not in the buffer pool it registered latch called PAGEIOLATCH on this page and load this page to buffer pool.  

So here we can say latch is just a lock on Data Page.  SQL Server is taking latch so that any other simultaneous thread in the system does not load the same page from disk.

We can say just it is preventing the duplicate IO work.  So PAGEIOLATCH we can conclude as a latch type used while loading the page from disk to buffer with asynchronous IO.

So, our page is in the buffer pool.  next step is change data in this page after loading the page to the buffer pool. SQL Server change the latch type from PAGEIOLATCH to PAGELATCH and update the corresponding data is a page following the update command.  

So, page latch here is being used to log the page in memory to keep Integrity of the data in the page.  due to this log are the threads trying to access the same page at the same time cannot cause data correction.

so, after changing the data thread releases the latch quickly and sends back acknowledgment to client.

there is also latch type called just a LATCH.  the usage of this latch type is the same just to keep Integrity of that  data in the non-buffer memory.  

We were talking about buffer memory LATCH types.  LATCH it is just the same with the same function only for non-buffer memory part.  So we discuss about page latch PAGEIOLATCH and LATCH types and I showed how they keep Integrity of the pages in memory.

now let's discuss quickly about LATCH modes.

PAGELATCH_KP this LATCH type used to prevent others simultaneous threads from destroying the same page.  If this latch is taken for a page, other threads(e.g., lazy writers) cannot delete this page from memory.

PAGELATCH_SH This latch is taken when reading a data page.  important point is multiple threads can simultaneously intensely access a resource for reading under a shared latch.

PAGELATCH_UB Update latch is taken when updating a data page.  This is compatible with SH and KP

PAGELATCH_EX This is very much exclusive latch blocks other threads from writing to or reading from the referenced structure. not compatible with any type and this kind of latch.

example page is completely being updated like for example data rows or metadata in the page is being updated so we take this kind of latch in those cases.

PAGELATCH_DT Destroy latch DT and this must be acquired before destroying the contents of references structure, I mean data pages.