查找被加锁最多的对象

数据库管理员通常需要识别阻碍数据库性能的锁源。

例如，你要监视生产服务器是否存在任何可能的瓶颈。 你怀疑可能有竞争激烈的资源，并想知道这些对象上被加了多少锁。 确定最常被锁定的对象后，可以采取措施来优化对争用对象的访问。

为此，请在 SQL Server Management Studio 中使用查询编辑器。

查找具有最多锁的对象

1. 在查询编辑器中，发出以下语句。

   -- Find objects in a particular database that have the most  
   -- lock acquired. This sample uses AdventureWorksDW2012.  
   -- Create the session and add an event and target.  
   --   
   IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')  
   DROP EVENT session LockCounts ON SERVER  
   GO  
   DECLARE @dbid int  
   
   SELECT @dbid = db_id('AdventureWorksDW2012')  
   
   DECLARE @sql nvarchar(1024)  
   SET @sql = '  
   CREATE event session LockCounts ON SERVER  
   ADD EVENT sqlserver.lock_acquired (WHERE database_id =' + CAST(@dbid AS nvarchar) +')  
   ADD TARGET package0.histogram(   
   SET filtering_event_name=''sqlserver.lock_acquired'', source_type=0, source=''resource_0'')'  
   
   EXEC (@sql)  
   GO  
   ALTER EVENT session LockCounts ON SERVER   
   STATE=start  
   GO  
   --   
   -- Create a simple workload that takes locks.  
   --   
   USE AdventureWorksDW2012  
   GO  
   SELECT TOP 1 * FROM dbo.vAssocSeqLineItems  
   GO  
   -- The histogram target output is available from the   
   -- sys.dm_xe_session_targets dynamic management view in  
   -- XML format.  
   -- The following query joins the bucketizing target output with  
   -- sys.objects to 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_data AS XML)  
   LockData  
   FROM sys.dm_xe_session_targets xest  
   JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address  
   JOIN sys.server_event_sessions ses ON xes.name = ses.name  
   WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'  
   ) Locks  
   CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)  
    ) LockedObjects   
   INNER JOIN sys.objects o  
   ON LockedObjects.lobject_id = o.object_id  
   WHERE o.type != 'S' AND o.type = 'U'  
   ORDER BY lock_count desc  
   GO  
   --   
   -- Stop the event session.  
   --   
   ALTER EVENT SESSION LockCounts ON SERVER  
   state=stop  
   GO  
   
   

在此过程中的语句执行完毕后，查询编辑器的结果选项卡将显示以下列：

• 姓名

• 对象标识符

• 锁计数 (lock_count)

另请参阅

CREATE EVENT SESSION （Transact-SQL）
ALTER EVENT SESSION （Transact-SQL）
sys.dm_xe_session_targets（Transact-SQL）
sys.dm_xe_sessions（Transact-SQL）
sys.server_event_sessions（Transact-SQL）