Microsoft – SQL Server – Locks – Aggregated

Background

IT life is crazy. You put in 40 hours each week and then take your light rain Autumn weekend to stress test your Application even more.

We have a set of SQL Server Transact Code that takes a while to run and I know that it might be OK to run for a few hours, but it can not impede normal Business flow.

Business still has to run and this back-office code has to run, as well.

Fast Forward

How do we measure transaction locks and hopefully reach for the least amount of database locks at any one time.

Took to the Net

And, so I started trying to see how others have dealt with same problem.

Here are some worthy SQL Server Activity / Lock monitoring tools:

But, unfortunately Adam’s tool is a bit more geared towards a point in time view and not a time span.

On the other hand, Erland’s Beta_lockinfo offers more luminous output than I could easily digest.

Code Baseline

I found Ritesh Shan’s code to be a bit better for my current specific need:

Find Locked Table name with duration and count in SQL Server
http://blog.extreme-advice.com/2012/09/17/find-locked-table-name-with-duration-and-count-in-sql-server/

Code

Here is my redacted version.


set transaction isolation level read uncommitted;
;with cteSession
as
(

SELECT
tblES.login_name as [LoginName]

, tblL.request_session_id AS SessionID

, tblL.request_owner_type

-- , tblL.resource_type
, db_name(resource_database_id)
as [databaseName]

, schema_name(tblO.schema_id) + '.' + tblO.Name
as LockedObjectName

, tblTAT.Transaction_begin_time

, max(tblEC.most_recent_sql_handle)
as most_recent_sql_handle

, COUNT(*) AS NumberofLocks

FROM sys.dm_tran_locks tblL with (nolock)

JOIN sys.dm_exec_sessions tblES with (nolock)
ON tblL.request_session_id = tblES.session_id

JOIN SYS.DM_EXEC_CONNECTIONS tblEC with (nolock)
ON tblEC.SESSION_ID = tblES.SESSION_ID

JOIN sys.dm_tran_session_transactions tblTS with (nolock)
ON tblTS.session_id = tblES.session_id

JOIN sys.dm_tran_active_transactions tblTAT with (nolock)
ON tblTAT.transaction_id
= tblTS.transaction_id

LEFT OUTER JOIN sys.[Partitions] tblP with (nolock)
ON tblL.resource_associated_entity_id
= tblP.hobt_id

LEFT OUTER JOIN sys.objects tblO with (nolock)
ON tblP.object_id = tblO.object_id
and tblO.Type = 'U'

GROUP BY
tblTAT.Transaction_begin_time

,tblES.login_name
,tblEC.most_recent_sql_handle
,tblL.request_session_id
,tblL.request_owner_type
-- ,tblL.resource_type
,db_name(resource_database_id)
,tblO.schema_id
,tblO.Name
--,tblEC.most_recent_sql_handle

)

select top 100

tblS.loginName as [LoginName]

, tblS.SessionID

, tblS.request_owner_type

, tblS.[databaseName]

, tblS.LockedObjectName

, tblS.Transaction_begin_time

--, max(tblS.most_recent_sql_handle)
-- as most_recent_sql_handle

, max(tblQS.query_hash) as query_hash

, tblS.NumberofLocks

, case

when DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) > 60 then
cast(DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) / 60 as varchar)
+ ' hour(s)'
+ ' ' + cast( (DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) % 60 ) as varchar)
+ ' min(s)'

when DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) > 0 then
cast(DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) as varchar)
+ ' minute(s)'
+ ' ' + cast( (DATEDIFF(second, tblS.Transaction_begin_time, GETDATE()) % 60 ) as varchar)
+ ' sec(s)'

else
cast(DATEDIFF(second, tblS.Transaction_begin_time, GETDATE()) as varchar)
+ ' sec(s)'

end AS Duration

, SUBSTRING(
max(tblST.text)
, max(tblQS.statement_start_offset/2)+1
,
(
(
CASE max(tblQS.statement_end_offset)
WHEN -1 THEN DATALENGTH(max(tblST.[text])
)
ELSE max(tblQS.statement_end_offset)
END
- max(tblQS.statement_start_offset)
)/2
) + 1
) AS statement_text

from cteSession tblS

LEFT OUTER JOIN sys.dm_exec_query_stats tblQS with (nolock)

ON tblS.most_recent_sql_handle = tblQS.[sql_handle]

outer apply sys.dm_exec_sql_text(tblS.most_recent_sql_handle) tblST

GROUP BY

tblS.loginName

, tblS.SessionID

, tblS.request_owner_type

, tblS.[databaseName]

, tblS.LockedObjectName

, tblS.Transaction_begin_time

, tblS.NumberofLocks

order by

tblS.NumberofLocks desc
, tblS.[databaseName]

, tblS.LockedObjectName

 

Shared

GitHub – Gist

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s