Sample query/DDL for producing page locks on a clustered index in SQL Server (without hints)

Question

We currently have some indexes where ALLOW_PAGELOCKS is set to off. This presumably was done in order to reduce deadlocks. However I doubt that it would really had an effect back then.

Now I am trying to understand WHEN SQL Server actually chooses to start locking pages rather than keys in a clustered index. I asked Jonathan Keyhaisas recently and he told me that this could happen if I am touching rows on several subsequent pages. However I didn't manage to get any exclusive page locks by updating rows in a clustered index with a sample query.

Could you help me understand page locks better with a sample query and table? I am running SQL Server 2008 SP4.

Thanks in advance

Martin

Answer 1

We can use a table of about the same size, but we need some more interesting data. Specifically, data that SQL doesn't have indexed but that we're using in our modification predicates.

USE tempdb;

CREATE TABLE dbo.Dummy
(
  Id INT IDENTITY(1, 1),
  Crap DATE,
  MoreCrap VARCHAR(1000)
);

INSERT dbo.Dummy ( Crap, MoreCrap )
    SELECT TOP 1000000 DATEADD(DAY, x.Rn % 365 + 1, GETDATE()), CONVERT(VARCHAR(100), NEWID())
        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2 
        ) AS x ( Rn );

CREATE CLUSTERED INDEX cx_RemusForPresident ON dbo.Dummy (Id) 

With just the CX on the Id column, we update!

BEGIN TRAN
UPDATE d
SET d.MoreCrap = 'Happy Friday'
FROM dbo.Dummy AS d
WHERE d.Crap >= '20170601'
AND d.Crap < '20170630'
AND d.MoreCrap LIKE '0%'

And just like before, though hopefully without offending Aaron Bertrand, we check out sp_WhoIsActive

EXEC sp_WhoIsActive @get_locks = 1

And blammo! Exclusive locks on Pages, and IX locks on the object, with no key locks.

Answer 2

The lock granularity (ie. row vs. page vs. table) is part of the lock strategy which is determined upfront when a statement starts execution. there are several factors that determine the granularity, but for rowstores the major factors are a) allowed granularities (ie. sp_tableoptions) and b) estimated number of locks expected (almost the same as cardinality estimate, but not quite the same).

So, if you relax the a) constraint (ie. allow page locks) then, to get page locks you need to run a query that the execution engine will expect a large number of row locks and will decide is better to use page locks instead. Typical examples are table scans over big enough table. You can pretty much assume 'page lock granularity => missing index' and you'll be almost always right.

For an update (ie. to make the lock exclusive) you can approach it similarly. Is usually the scan that find qualified rows is using an U page lock, and then the upgrade to X mode must be at the same granularity.