23 January,2013 by Tom Collins
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?
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?
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |