SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – LCK_M_IS and how to reduce it - sql wait type

23 January,2013 by Jack Vamvas

According to SQL Server Books Online  the wait type LCK_M_IS  “Occurs when a task is waiting to acquire an Intent Shared (IS) lock”

Use the sys.dm_tran_locks view to these  Locks. Use sys.dm_tran_locks to find requests - SQL Server DBA

The Intent Shared (IS) is a two part lock. The Intent Shared (IS) lock notifies  the intention of a transaction to read a portion of  resources lower in the hierarchy by placing S locks on those individual resources. In theory ,performance should improve as SQL Server does not have to examine every lock on every row to place a Lock , only examining  Intent Locks at the table level.

 An example: If a transaction places an Intent Lock at the table level , then the intention is to place some Shared Locks on pages\rows in the table.  Placing the Intent Lock at the table level blocks another transactions intent to place an Exclusive Lock on the table.

 How to troubleshoot LCK_M_IS

1) Is there an exclusive lock already on the object , which is causing the LCK_M_IS to appear?

2) Investigate IO delays. If IO is delayed the  exclusive lock is held for longer than expected. Some hints are obtained by checking for other waits – such as IO_COMPLETION and PAGEIOLATCH_XX

3) Analyse the transaction management around queries .  Make transactions requiring an Exclusive Lock shorter. Read on SQL Server - SQL open transactions and how to find

4) Analyse for lock escalation

5) Read through code executed on the SQL Server. Are there commands which could be avoided? Can the query be written in such a way to avoid Exclusive Locks?

 Read More

SQL Server – Find sql server Locks - SQL Server DBA

SQL Server – LCK_M_X and how to reduce it - SQL Server DBA

Concurrency Control – Optimistic and Pessimistic - SQL Server DBA

SQL Lock Escalation FAQ


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer