SQL Server extended events use 2: Query objects with the most locks

When we suspect that there may be highly competing resources and want to know how many locks these objects occupy. Once you identify the objects with the highest lock frequency, you can take some measures to optimize access to the competing objects. Using SQL Server extended events can help us obtain the lock resources used by objects.

 

-- Delete lockcounts Event session first

If exists (select *
From SYS. server_event_sessionswherename = 'lockcounts ')

Drop event
Sessionlockcounts on
Server

Go

Declare @ dbid int

 

Select @ dbid = db_id ('adventureworks ')

 

Declare @ SQL nvarchar (1024)

Set @ SQL =
'

Create eventsession lockcounts on server

Add eventsqlserver. lock_acquired (where database_id = '+ Cast (@ dbidas
Nvarchar) + ')

Add targetpackage0.synchronous _ bucketizer (

Setfiltering_event_name = ''sqlserver. lock_acquired'', source_type = 0, source = ''resource _ 0 '')'

 

Exec (@ SQL)

Go

Alter event
Sessionlockcounts on
Server

State = start

Go

--

-- Create asimple workload that takes locks.

--

Useadventureworks

Go

Select top 1
* From HumanResources. Department

Go

-- Bucketizingtarget output is available from

-- SYS. dm_xe_session_targets dynamic management view in

-- XML format.

-- The followingquery joins the bucketizing target output

-- SYS. objectsto obtain the object names.

--

Select name,
Object_id, lock_count
From

(Select objstats. Value ('.', 'bigint')
Lobject_id,

Objstats. Value ('@ count', 'bigint ')
As lock_count

From (

Select cast(xest.tar get_dataas
XML)

Lockdata

From SYS. dm_xe_session_targets xest

Join SYS. dm_xe_sessions xesonxes. Address =
Xest. event_session_address

Join SYS. server_event_sessions sesonxes. Name
= SES. Name

Where xest.tar get_name =
'Synchronous _ bucketizer 'andxes. Name
= 'Lockcounts'

) Locks

Cross apply lockdata. nodes ('// bucketizertarget/slot')
T (objstats)

) Lockedobjects

Inner join
SYS. Objects o

Onlockedobjects. lobject_id = O. object_id

Where o. type! =
'S 'and O. type =
'U'

Order by lock_countdesc

Go

--

Result:

 

Name object_id lock_count

-------------------------------

Department 805577908 2

 

(1 row (s) affected)

 

-- Stop theevent session.

--

Alter event
Sessionlockcounts on
Server

State = stop

Go

 

-- Drop the eventsession

Drop event
Sessionlockcounts on
Server