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.
1) Is there an exclusive lock already on the object , which is causing the LCK_M_IS to appear?
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?