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
Best Answer
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.
With just the CX on the Id column, we update!
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.