January 16, 2012 at 3:36 pm
Has anyone seen this specific lock contributing to a significant wait/blocking time? What could be the cause/solution?
The description here is not very helpful: "Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL)."
http://msdn.microsoft.com/en-us/library/ms179984.aspx
Is it possible that multiple threads trying to insert records in the same table at the same time end up blocking each other with this lock?
Thanks
January 17, 2012 at 5:57 am
you usually find more than one instance when locks are a problem - you should be able to find the object/key/page involved and drill in for more detail.
If it's multiple threads you should be able to see them if you capture info at the point in time.
Don't know if that helps or not?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 25, 2013 at 5:21 am
I'm experiencing the same problem, too. Still looking for a solution.
August 5, 2013 at 3:27 am
Answer to original poster is yes, LCK_M_RIn_NL probably is exactly that.
August 5, 2013 at 3:29 am
Try this purloined bit of SQL and see what your numbers look like.
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('BACKUPBUFFER','BACKUPIO','CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'--'LCK_M_IX'
))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 97.5;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply