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