This is another common wait type. However, I still frequently see people getting confused with PAGEIOLATCH_X and PAGELATCH_X wait types. Actually, there is a big difference between the two. PAGEIOLATCH is related to IO issues, while PAGELATCH is not related to IO issues but is oftentimes linked to a buffer issue. Before we delve deeper in this interesting topic, first let us understand what Latch is.
Latches are internal SQL Server locks which can be described as very lightweight and short-term synchronization objects. Latches are not primarily to protect pages being read from disk into memory. It’s a synchronization object for any in-memory access to any portion of a log or data file.[Updated based on comment of Paul Randal]
Latches and Locks are two key synchronization objects that can affect system waits and overall performance. They are quite complex in nature and quite a lot of efforts are needed to understand them fully. For the sake of simpler understanding, I would term latches as light-weight internal locks that are usually applied on in-memory buffer pages. Locks are for ensuring transactional integrity which you can control to a large extend (using transaction isolation levels, query and table hints etc) where as latches are used internally by the relational and storage engines to synchronize access to in-memory pages.
Now, let us understand the wait stat type related to latches.
From Book On-Line:
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.
When there is a contention of access of the in-memory pages, this wait type shows up. It is quite possible that some of the pages in the memory are of very high demand. For the SQL Server to access them and put a latch on the pages, it will have to wait. This wait type is usually created at the same time. Additionally, it is commonly visible when the TempDB has higher contention as well. If there are indexes that are heavily used, contention can be created as well, leading to this wait type.
Reducing PAGELATCH_X wait:
The following counters are useful to understand the status of the PAGELATCH:
- Average Latch Wait Time (ms): The wait time for latch requests that have to wait.
- Latch Waits/sec: This is the number of latch requests that could not be granted immediately.
- Total Latch Wait Time (ms): This is the total latch wait time for latch requests in the last second.
If there is TempDB contention, I suggest that you read the blog post of Robert Davis right away. He has written an excellent blog post regarding how to find out TempDB contention. The same blog post explains the terms in the allocation of GAM, SGAM and PFS. If there was a TempDB contention, Paul Randal explains the optimal settings for the TempDB in his misconceptions series. Trace Flag 1118 can be useful but use it very carefully.
I totally understand that this blog post is not as clear as my other blog posts. I suggest if this wait stats is on one of your higher wait type. Do leave a comment or send me an email and I will get back to you with my solution for your situation. May the looking at all other wait stats and types together become effective as this wait type can help suggest proper bottleneck in your system.
Read all the post in the Wait Types and Queue series.
Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussions of Wait Stats in this blog are generic and vary from system to system. It is recommended that you test this on a development server before implementing it to a production server.
Reference: Pinal Dave (https://blog.sqlauthority.com)