2

I recently came across the article Locking and Performance. It has following statements

If we manually monitor locking activity while this query executes, we see that without the page locking granularity hint, SQL Server acquires and releases over half a million row level locks while seeking the clustered index.

The performance of this query can be improved to 320 ms simply by adding a page granularity locking hint to the Sales table

I am using SQL Server 2012. For identifying the locks, I referred MSDN: How to: Find the Objects That Have the Most Locks Taken on Them. And I used a workload query that joins two tables and selects 1001000 rows. Still it is not showing any locks in the output. It is hard to believe that there is no lock happening.

  1. Why is it not showing any locks? How to correct this?
  2. Is there a better way to identify the type and count of locks?

References - Updated:

  1. Which Locks Count Toward Lock Escalation? - Kendra Little

  2. Use Extended Events to capture Deadlocks in SQL Server

  3. Tracking SQL Server Database Usage - Jonathan Kehayias

  4. Locking and Blocking (2) – Shared Locks and Lock Resource

  5. View locks acquired during query execution (SQL Server) and beta_lockinfo

  6. Using Extended Events to Find Blocks in SQL Server 2012

  7. Determine Which Queries Are Holding Locks

Extended Event with my Workload

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('My_DW')

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.synchronous_bucketizer ( 
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 My_DW
GO

SELECT TOP 1001000 *
FROM Fact_Appointment F
INNER JOIN Dim_AppointmentType D
    ON  F.AppointmentType_Key = D.AppointmentType_Key

GO

-------------------------------------------------------------------------------------------------------------------------
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 = 'synchronous_bucketizer' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//BucketizerTarget/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

closed as off-topic by Erik Darling, McNets, Marco, Colin 't Hart, Marian Jun 2 '17 at 14:33

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, McNets, Marco, Marian
If this question can be reworded to fit the rules in the help center, please edit the question.

3

You mentioned that you are testing in SQL 2012 I suggest you change you query for SQL 2012. You are using the version for 2008R2.

Pay attention to these lines.

WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)

Full test code that is working for me.

-- Find objects in a particular database that have the most
-- lock acquired. This sample uses WideWorldImporters .
-- 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('WideWorldImporters')

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 WideWorldImporters
GO
SELECT TOP 1 * FROM sales.Orders
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

I also tested this as target to a file. You can then save it in a database table and query/aggregate to your need.

CREATE EVENT SESSION [CaptureLocks] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.database_id,
           sqlserver.database_name,
           sqlserver.server_instance_name,
           sqlserver.session_id)
    WHERE ([package0].[equal_uint64]([database_id],(5)) 
           AND [object_id]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\CaptureLocks.xel')
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=OFF)
GO

Sample query to look at the data saved in table.

SELECT OBJECT_NAME([object_id]) AS [objectName], 
       resource_type,
       mode,
       Count(0) AS [LockCounts]
FROM [WideWorldImporters].[dbo].[CollectLocks]
GROUP BY [object_id], resource_type, mode

Adding this section based on the question asked in comment section. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-acquired-event-class

Mode column will give you lock type. See above link for details.

I captured another trace with paglock and rowlock hint. resource_type column will capture the resource type (table/page/row).

Exact query I ran was:

SELECT TOP (1) * FROM sales.orders WITH (PAGLOCK)
GO
SELECT TOP (1) * FROM sales.orders WITH (PAGLOCK)
GO

Sample form live data window.

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.