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