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

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

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.

StackExchange Database Administrators Q#168116, answer score: 6