SQL SERVER – LCK_M_XXX – Wait Type – Day 15 of 28

Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data, at the same time. In simpler words, it maintains the integrity of data by protecting (or preventing) access to the database object.

From Book On-Line:

LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock.

LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.

LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.

LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.

LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.

LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

LCK_M_U
Occurs when a task is waiting to acquire an Update lock.

LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.

LCK_M_XXX Explanation:

I think the explanation of this wait type is the simplest. When any task is waiting to acquire lock on any resource, this particular wait type occurs. The common reason for the task to be waiting to put lock on the resource is that the resource is already locked and some other operations may be going on within it. This wait also indicates that resources are not available or are occupied at the moment due to some reasons. There is a good chance that the waiting queries start to time out if this wait type is very high. Client application may degrade the performance as well.

You can use various methods to find blocking queries:

Reducing LCK_M_XXX wait:

  • Check the Explicit Transactions. If transactions are very long, this wait type can start building up because of other waiting transactions. Keep the transactions small.
  • Serialization Isolation can build up this wait type. If that is an acceptable isolation for your business, this wait type may be natural. The default isolation of SQL Server is ‘Read Committed’.
  • One of my clients has changed their isolation to “Read Uncommitted”. I strongly discourage the use of this because this will probably lead to having lots of dirty data in the database.
  • Identify blocking queries mentioned using various methods described above, and then optimize them.
  • Partition can be one of the options to consider because this will allow transactions to execute concurrently on different partitions.
  • If there are runaway queries, use timeout. (Please discuss this solution with your database architect first as timeout can work against you).
  • Check if there is no memory and IO-related issue using the following counters:

Checking Memory Related Perfmon Counters

    • SQLServer: Memory Manager\Memory Grants Pending (Consistent higher value than 0-2)
    • SQLServer: Memory Manager\Memory Grants Outstanding (Consistent higher value, Benchmark)
    • SQLServer: Buffer Manager\Buffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)
    • SQLServer: Buffer Manager\Page Life Expectancy (Consistent lower value than 300 seconds)
    • Memory: Available Mbytes (Information only)
    • Memory: Page Faults/sec (Benchmark only)
    • Memory: Pages/sec (Benchmark only)
  • Checking Disk Related Perfmon Counters
    • Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
    • Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
    • Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

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 discussion of Wait Stats in this blog is generic and varies 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)

5 thoughts on “SQL SERVER – LCK_M_XXX – Wait Type – Day 15 of 28

  1. Hi Pinal,

    In our SSIS package we use DELETE command to clean a table before load it with fresh data. While running the step to clean table i.e DELETE all records, it goes under LCK_M_U wait type for indefinite time.

    What could be the possible reasons?

    Thanks in advance.

    With Regards,
    Prashant

    Like

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

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