鎖定計數的擴展事件不顯示鎖定

我最近看到了文章Locking and Performance。它有以下語句

如果我們在此查詢執行時手動監視鎖定活動，我們會看到在沒有頁面鎖定粒度提示的情況下，SQL Server 在查找聚集索引時會獲取並釋放超過 50 萬行級別的鎖。

只需向 Sales 表添加頁粒度鎖定提示，即可將此查詢的性能提高到 320 ms

我正在使用 SQL Server 2012。為了辨識鎖，我參考了MSDN：How to: Find the Objects That Have the Most Locks Taken on Them。我使用了一個工作負載查詢來連接兩個表並選擇 1001000 行。它仍然沒有在輸出中顯示任何鎖。很難相信沒有鎖發生。

1. 為什麼不顯示任何鎖？如何糾正這個？
2. 有沒有更好的方法來辨識鎖的類型和數量？

參考資料 - 更新：

1. 哪些鎖計入鎖升級？- 肯德拉小
2. 使用擴展事件擷取 SQL Server 中的死鎖
3. 跟踪 SQL Server 數據庫使用情況 - Jonathan Kehayias
4. 加鎖與阻塞（二）——共享鎖與鎖資源
5. 查看查詢執行期間獲取的鎖 (SQL Server)和 beta_lockinfo
6. 在 SQL Server 2012 中使用擴展事件查找塊
7. 確定哪些查詢持有鎖

我的工作負載的擴展事件

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

您提到您正在 SQL 2012 中進行測試，我建議您將查詢更改為 SQL 2012。您使用的是 2008R2 的版本。

注意這些線。

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

對我有用的完整測試程式碼。

-- 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

我還測試了它作為文件的目標。然後，您可以將其保存在數據庫表中並根據需要進行查詢/聚合。

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

用於查看保存在表中的數據的範例查詢。

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

根據評論部分中提出的問題添加此部分。參考：https ://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-acquired-event-class

模式列將為您提供鎖定類型。有關詳細資訊，請參閱上面的連結。

paglock我用和rowlock提示 擷取了另一個痕跡。resource_type列將擷取資源類型（表/頁/行）。

我執行的確切查詢是：

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

範例表單實時數據視窗。

引用自：https://dba.stackexchange.com/questions/174605