Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Find the Objects That Have the Most Locks Taken on Them

Database administrators often need to identify the source of locks that are hindering database performance.

For example, you are monitoring your production server for any possible bottlenecks. You suspect that there might be highly contested resources, and would like to know how many locks are taken on those objects. Once the most frequently locked objects are identified, steps can be taken to optimize access to the contended objects.

To do this, use Query Editor in SQL Server Management Studio.

To find the objects that have the most locks

  • In Query Editor, issue the following statements.

    -- 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
    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)
    ALTER EVENT session LockCounts ON SERVER 
    -- Create a simple workload that takes locks.
    USE AdventureWorksDW2012
    SELECT TOP 1 * FROM dbo.vAssocSeqLineItems
    -- 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)
    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
    -- Stop the event session.

After the statements in this procedure finish, the Results tab of Query Editor displays the following columns:

  • name

  • object_id

  • lock_count

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft