2. This script provides: detailed lock info for current connections, and provide filters
3. Use this when locking issues arise such as timeouts and/or deadlocks.
4. This sproc can be deployed to any sql server.
5. Sproc uses an optional table "select * from master..sp_LockDetailResults" to save results, although not required. Table can be used to track lock trends over time. E,g: Exec sp_LockDetail every 5 seconds.
7. Usage Examples:
-- view locks for db
exec [sp_LockDetail] @dbname='master', @insert = 1
-- view locks for db and save to table
exec [sp_LockDetail] @dbname='master', @insert = 1
-- view results
select * from master..sp_LockDetailResults
-- view locks for db and and lock mode
exec [sp_LockDetail] @dbname='master', @lockMode='exclusive'
/****************** CODE *****************/
USE MASTER
GO
alter PROCEDURE [dbo].[sp_LockDetail]
(
@dbname varchar(255)=null
,@lockMode varchar(255) = null
,@lockType varchar(255) = null
,@insert bit = null
)
AS
/*********************************
NAME: [sp_LockDetail]
PURPOSE: View detailed lock info for current connections, and provide filters
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 20141101 JRD 1. Created this script.
2.0 20141111 JRD 1. Mod use table save to option
USES:
sys
master..sp_LockDetailResults TABLE
see sp_LockDetailResults.sql
USED BY:
DBA
UNIT TESTS
-- view locks for db
exec [sp_LockDetail] @dbname='master', @insert = 1
-- view locks for db and save to table
exec [sp_LockDetail] @dbname='master', @insert = 1
-- view results
select * from master..sp_LockDetailResults
-- view locks for db and and lock mode
exec [sp_LockDetail] @dbname='master', @lockMode='exclusive'
*/
BEGIN
select
distinct * into #results
from
(
select
CASE
WHEN x.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN x.resource_type
WHEN x.resource_type = 'OBJECT' THEN OBJECT_NAME(x.resource_associated_entity_id, x.[dbid])
WHEN x.resource_type IN ('KEY', 'PAGE', 'RID') THEN
(
SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id =
x.resource_associated_entity_id
)
ELSE 'Unidentified'
END AS requested_object_name,
'N/A' ObjectName
,(CASE x.REQUEST_MODE
WHEN 'SCH-S' THEN 'SCHEMA STABILITY'
WHEN 'SCH-M' THEN 'SCHEMA MODIFICATION'
WHEN 'S' THEN 'SHARED'
WHEN 'U' THEN 'UPDATE'
WHEN 'X' THEN 'EXCLUSIVE'
WHEN 'IS' THEN 'Intent Shared'
WHEN 'IU' THEN 'Intent Update'
WHEN 'IX' THEN 'Intent Exclusive'
WHEN 'SIU' THEN 'Shared Intent Update'
WHEN 'SIX' THEN 'Shared Intent Exclusive'
WHEN 'UIX' THEN 'Update Intent Exclusive'
WHEN 'BU' THEN 'Bulk Update'
WHEN 'RangeS_S' THEN 'Shared Key-Range and Shared Resourcelock'
WHEN 'RangeS_U' THEN 'Shared Key-Range and Update Resource lock'
WHEN 'RangeI_N' THEN 'Insert Key-Range and Null Resourcelock'
WHEN 'RangeI_S' THEN 'Key-Range Conversion lock, created by an overlap of RangeI_N and S locks'
WHEN 'RangeI_U' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and U locks'
WHEN 'RangeI_X' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and X locks'
WHEN 'RangeX_S' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_S locks'
WHEN 'RangeX_U' THEN 'Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_U locks'
WHEN 'RangeX_X' THEN 'Exclusive Key-Range and ExclusiveResource lock'
ELSE NULL
END) AS REQUEST_LOCK_MODE
,x.*
from
(
SELECT
SessionID = s.Session_id,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id,
a.text ,
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
resource_associated_entity_id
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN
(
SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
WHERE
s.session_id > 50
and
resource_database_id > 1
) x
where
DatabaseName not in ('master', 'model', 'msdb', 'tempdb','distribution')
and
(@dbname is null or DatabaseName = @dbname)
) y
where
(@lockMode is null or REQUEST_LOCK_MODE = @lockMode)
and
(@lockType is null or resource_type = @lockType)
declare @dt datetime
select @dt = GETDATE()
if (@insert = 1)
begin
--print 'ins'
INSERT INTO [master].[dbo].[sp_LockDetailResults]
([ObjectName]
,[REQUEST_LOCK_MODE]
,[SessionID]
,[resource_type]
,[DatabaseName]
,[request_status]
,[request_owner_type]
,[objectid]
,[dbid]
,[number]
,[encrypted]
,[blocking_session_id]
,[text]
,[request_mode]
,[request_type]
,[login_time]
,[host_name]
,[program_name]
,[client_interface_name]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[status]
,[last_request_start_time]
,[last_request_end_time]
,[logical_reads]
,[reads]
,[DateTimeCreated])
SELECT [ObjectName]
,[REQUEST_LOCK_MODE]
,[SessionID]
,[resource_type]
,[DatabaseName]
,[request_status]
,[request_owner_type]
,[objectid]
,[dbid]
,[number]
,[encrypted]
,[blocking_session_id]
,[text]
,[request_mode]
,[request_type]
,[login_time]
,[host_name]
,[program_name]
,[client_interface_name]
,[login_name]
,[nt_domain]
,[nt_user_name]
,[status]
,[last_request_start_time]
,[last_request_end_time]
,[logical_reads]
,[reads]
,@dt
FROM #results
end
select * from #results order by [databasename]
drop table #results
END
