Arun Gopinath
(imported from SE)
I'm trying to understand why there is a discrepancy in lock count in `sys.dm_tran_locks` and `sqlserver.lock_acquired` extended event in certain cases. Here is my repro script, I'm using the `StackOverflow2013` database on SQL Server 2019 RTM, compat level 150.
/* Initial Setup */
IF OBJECT_ID('dbo.HighQuestionScores', 'U') IS NOT NULL
DROP TABLE dbo.HighQuestionScores;
CREATE TABLE dbo.HighQuestionScores
(
Id INT PRIMARY KEY CLUSTERED,
DisplayName NVARCHAR(40) NOT NULL,
Reputation BIGINT NOT NULL,
Score BIGINT
)
INSERT dbo.HighQuestionScores
(Id, DisplayName, Reputation, Score)
SELECT u.Id,
u.DisplayName,
u.Reputation,
NULL
FROM dbo.Users AS u;
CREATE INDEX ix_HighQuestionScores_Reputation ON dbo.HighQuestionScores (Reputation);
Next I update the table statistics with a large fake row count
/* Chaotic Evil. To reproduce the issue, this step is absolutely needed*/
UPDATE STATISTICS dbo.HighQuestionScores WITH ROWCOUNT = 99999999999999;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
Then I open a transaction and update `Score` for Reputation, say `56`
BEGIN TRAN;
UPDATE dbo.HighQuestionScores
SET Score = 1
WHERE Reputation = 56 /* 8066 records */
AND 1 = (SELECT 1);
/* Source: https://www.erikdarlingdata.com/sql-server/helpers-views-and-functions-i-use-in-presentations/ Thanks, Erik */
SELECT *
FROM dbo.WhatsUpLocks(@@SPID) AS wul
WHERE wul.locked_object = N'HighQuestionScores'
ROLLBACK;
I get a bunch of page locks (despite having an index on Reputation). I'm guessing the bad estimates really did a number on the optimizer there.
[![page_locks][1]][1]
I also double checked using `sp_whoisactive` and it too returns the same information.
<Object name="HighQuestionScores" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK__HighQues__3214EC072EE1ADBA" request_mode="X" request_status="GRANT" request_count="6159" />
</Locks>
</Object>
Meanwhile I also have an extended event running on `sqlserver.lock_acquired` separately. When I look at the grouped data I see **8066** page locks instead of initial **6159**
[![extended_event_lock_count][2]][2]
I definitely do not a see a lock escalation (verified using `sqlserver.lock_escalation` event), so I guess my question is why is the extended event showing a discrepancy with a higher number of lock count?
[1]: https://i.stack.imgur.com/OZPCj.png
[2]: https://i.stack.imgur.com/8rS0Q.png