SQL Server Retrieve Locked Objects and Statements

I’ve been fighting deep locking issues lately, and my normal tools of sysprocesses, resource monitor and the standard dmv’s just have not been enough to provide on the spot analysis. The biggest issue is seeing what objects are involved and what the statements are actually doing when the locking is occurring. One of the obstacles is in the dmv dm_tran_locks where the resources being locked can range from tables to rids, and there is no definitive join that fits the solution. To make matters worse there is a disconnect between server wide views and database DMV’s. What I mean here is that that your locks dmv is at the server level, but your object and index dmv’s are at the database level. Through a series of temp tables and cursors I’ve written a script that put’s this all together. It’s not pretty, but returns the information needed to troubleshoot.



/*
drop table #ServerValues
drop table #FinalResults
*/


Create table #FinalResults
(resource_type varchar(100),
ObjectName varchar(100),
IndexName varchar(100),
DBName varchar(100),
resource_description varchar(30),
resource_associated_entity_id bigint,
request_mode varchar(10),
request_type varchar(15),
wait_duration_ms bigint,
request_status varchar(20),
session_id int,
host_name varchar(50),
login_name varchar(100),
blocking_session_id int,
text nVarchar(max))

--Prep Server Wide Values
Create table #ServerValues
(RowNum int identity(1,1),
resource_type varchar(100),
Resource_database_id int,
resource_description varchar(30),
resource_associated_entity_id bigint,
request_mode varchar(10),
request_type varchar(15),
wait_duration_ms bigint,
request_status varchar(20),
session_id int,
host_name varchar(50),
login_name varchar(100),
blocking_session_id int,
text nVarchar(max))

insert into #ServerValues
select
t.resource_type,
t.Resource_database_id,
t.resource_description, --Fun to have
t.resource_associated_entity_id,
t.request_mode,
t.request_type,
wt.wait_duration_ms,
t.request_status,
c.session_id,
s.host_name,
s.login_name,
blocking_session_id,
st.text
from sys.dm_tran_locks t
inner join sys.dm_exec_connections c
on t.request_session_id = c.session_id
inner join sys.dm_exec_sessions s
on s.session_id = c.session_id
left join sys.dm_os_waiting_tasks wt
on wt.Session_id = s.session_id
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) st
where resource_database_id != 2 -- Don't want the noise


--Global used in each Cursor
Declare @Stmt varchar(max)
Declare @RowNum int
Declare @DBName Varchar(50)

--Get Object Locks
Declare Object_cursor cursor for
select RowNum, db_name(Resource_database_id) from #ServerValues where resource_type = 'object'
Open Object_Cursor
FETCH NEXT FROM Object_Cursor
into @RowNum, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
set @Stmt = 'Insert into #FinalResults
Select
resource_type,
o.name,' +
'''' + 'ObjectLock' + '''' + ' as IndexName,
db_name(Resource_database_id) as DBName,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
wait_duration_ms,
request_status,
session_id,--int,
host_name,
login_name,
blocking_session_id,
text from #ServerValues inner join ' + @dbname + '.sys.objects o on
resource_associated_entity_id = object_id where RowNum = ' + convert(varchar(5), @rownum)
exec(@Stmt)
print @Stmt
FETCH NEXT FROM object_Cursor;
END;
CLOSE object_Cursor;
DEALLOCATE object_Cursor;


--Get Index-Rid Locks
Declare Index_cursor cursor for
select RowNum, db_name(Resource_database_id) from #ServerValues where resource_type
In('key', 'Rid','HOBT','Page')

Open Index_Cursor
FETCH NEXT FROM Index_Cursor
into @RowNum, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
set @Stmt = 'Insert into #FinalResults
Select
resource_type,
o.name,
i.name,
db_name(Resource_database_id) as DBName,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
wait_duration_ms,
request_status,
session_id,--int,
host_name,
login_name,
blocking_session_id,
text from #ServerValues v inner join ' + @DBName +
'.sys.partitions p
on p.hobt_id = v.resource_associated_entity_id
inner join ' + @DBName +'.sys.objects o
on o.object_id = p.object_id
inner join ' + @DBName + '.sys.indexes i
on p.Object_id = i.object_id
and p.index_id = i.index_id'

exec(@Stmt)
FETCH NEXT FROM index_Cursor;
END;
CLOSE index_Cursor;
DEALLOCATE index_Cursor;

insert into #FinalResults
Select
resource_type,
'NA' as ObjectName,
'No Object' as IndexName,
db_name(Resource_database_id) as DBName,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
wait_duration_ms,
request_status,
session_id,--int,
host_name,
login_name,
blocking_session_id,
text from #ServerValues
where resource_type not In('key', 'Rid','HOBT','Page','object')




Select * from #FinalResults