SQL Server Extended Events使用2：查询使用锁最多的对象

当我们怀疑可能存在高度争用的资源，并希望了解这些对象占用多少锁定。一旦识别锁定频率最高的对象，便可采取一些措施来优化对争用对象的访问。使用SQL Server Extended Events可以帮助我们获得对象使用的锁资源。

 

--先删除LockCounts Event Session

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 the

--sys.dm_xe_session_targets dynamic management view in

-- XML format.

-- The followingquery joins the bucketizing target output with

-- sys.objectsto obtain the object names.

--

SELECT name, object_id, lock_count FROM

(SELECT objstats.value('.','bigint')AS lobject_id,

objstats.value('@count','bigint') AS lock_count

FROM (

SELECT CAST(xest.target_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.target_name= 'synchronous_bucketizer'ANDxes.name = 'LockCounts'

) Locks

CROSS APPLY LockData.nodes('//BucketizerTarget/Slot')AS 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

--

结果：

 

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