SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28

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:

PAGELATCH_DT
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.

PAGELATCH_EX
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.

PAGELATCH_KP
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.

PAGELATCH_SH
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.

PAGELATCH_UP
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.

PAGELATCH_X Explanation:

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 (http://blog.SQLAuthority.com)

 

13 thoughts on “SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28

  1. Your explanation of the purpose of latches is incorrect. 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. Your description of locks vs latches is also incorrect.

    Like

  2. hi dave,

    i working as a dba, i am very enthusiastic in learning performance tuning. recently in one of my servers i am seeing a lot of pageiolatch_sh wait_type. i am not sure how i could resolve this issue. it would be great if you could help me in solving the issue.

    Like

  3. Hi Pinal,

    Please publish the things only when you are sure about them. In this case Paul read that otherwise we will be in dark. Please remember that many users follow this blog and trust on you. I hope i pointed the correct isseu here.

    Like

    • Hi Pinal,

      SQL2000 server the application restarted frequently, the sysprocess i have 80 session in pageLatch_UP and waitresource in tempdb(2:2:100) and SQL target memory is 1.5 GB. kindly provide the solution.

      Like

  4. I am experiencing PAGELATCH_EX waits for concurrent INSERTS that are blocking each other. The inserts are running the same SP that inserts into just the one table. The Waitresource is not from tempdb but in the user database. Does this mean there is some contention at the database or database file level. I checked that all files are having more than 10% free space. Any suggestions or ideas ? DO you think turning on update_stats_asynchronous could help.

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s