LCK_M_IS sql wait type - how to reduce

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