I'm trying to understand why there is a discrepancy in lock count in
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. */ UPDATE STATISTICS dbo.HighQuestionScores WITH ROWCOUNT = 99999999999999; DBCC FREEPROCCACHE WITH NO_INFOMSGS;
Then I open a transaction and update
Score for Reputation, say
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.
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
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?