disable page lock

Question

Hello,

We are doing migration to SQL Server 2019, during the application configuration, we are getting some issues and the solution is to disable the page locking on the table.
Please let me know how to disable and enable the page lock on a table in sql server 2019?

Thanks

Answer 1

Just because I see everything and nothing about this Page/ROW lock topic and lock escalation topic.
1- Lock escalation is a mechanism where a lock (Page/Row level) can be escalated directly to page level. There is no intermediate lock level. It is a table level setting, NOT an index level setting. It means it is different from page level lock.
I encourage you reading :
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-blocking-problems-caused-lock-escalation
and:
https://sqlperformance.com/2022/09/sql-performance/lock-escalation-threshold-part-1
and
https://www.sqlpassion.at/archive/2016/10/31/disabling-row-and-page-level-locks-in-sql-server/

2- It is possible for each index in a table to disable page and row level lock.
It is usually advised NOT to modify these, unless it has been proven by a DB professional that this is a problem.
Usually, people are mixing up both. Make sure you don't have a lock escalation problem, and if you do a page lock problem, make sure you have the right indexes and the right isolation level.

Answer 2

Hi @Biltz ,

> Please let me know how to disable and enable the page lock on a table in sql server 2019?

You can using below T-SQL ;

-- Disable Page level locks  
ALTER INDEX index name ON table name   
SET (ALLOW_PAGE_LOCKS = OFF)  
GO  

-- Enable Page level locks  
ALTER INDEX index name ON table name  
SET (ALLOW_PAGE_LOCKS = ON)  
GO  

But disable page lock on a table is not a good choice, suggest you read below links.

Disabling ROW and PAGE Level Locks in SQL Server
Risk of disabling page locking

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

See ALTER TABLE (Transact-SQL) => SET LOCK_ESCALATION and ALTER INDEX (Transact-SQL) => ALLOW_PAGE_LOCKS
But why do you think that would solve anything?