August 29, 2011 at 10:28 pm
Hello
I had an issue with one of my production databases which caused problems with the application. I've looked at the standard performance monitor trace and found that the the sqlServer:locks\ average wait time counter spiked to around 3 minutes. I'm now trying to find the casue of this lock. Are there any DMV's or system tables that I can query to help me out?
August 29, 2011 at 10:45 pm
What kind of locks? Were you able to determine the head/lead blocker(s)? Do you have a profiler trace available?
If you don't have any of those things begin with the logs. You should also check to see if any SQL Agent jobs were running before/during the times you saw the excessive locking. Do any reporting procedures/developer have access to hit the production DB's?
There are tons of queries using DMV's that can help you pinpoint the cause of what's blocking but those are more real time...I am not aware of any historical ones.
Check out this link or or this one
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 29, 2011 at 10:47 pm
SELECT tl.resource_type ,
tl.resource_database_id ,
tl.resource_associated_entity_id ,
tl.request_mode ,
tl.request_session_id ,
wt.blocking_session_id ,
wt.wait_type ,
wt.wait_duration_ms
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC ;
You may try this to figure out the blocking session ids...
August 29, 2011 at 10:49 pm
--Identifying Top Objects Associated with Lock Contention
Use master
SELECT TOP 100
OBJECT_NAME(o.object_id, o.database_id) object_nm,
o.index_id,
partition_number,
page_lock_wait_count,
page_lock_wait_in_ms,
case when mid.database_id is null then 'N' else 'Y' end as missing_index_identified
FROM sys.dm_db_index_operational_stats (db_id('DBName'), NULL, NULL, NULL) o
LEFT OUTER JOIN (SELECT DISTINCT database_id, object_id
FROM sys.dm_db_missing_index_details) as mid
ON mid.database_id = o.database_id and mid.object_id = o.object_id
Where page_lock_wait_count<>0 --and Index_id <> 1
ORDER BY page_lock_wait_count DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply