Sql Interview Question #9 : What is new in lock escalation in SQL Server 2008?

This entry was posted in Sql Server 2008 Sql Server Interview Questions Sql Server tutorials and tagged ddl dml lock escalation logon server sql trigger on by .

Sql Server Interview Question #9

What is new in lock escalation in SQL Server 2008?

Solution

Lock escalation is one of the lesser known phenomena inside SQL Server. Often times locking 1 row in a table cost less than locking the entire table. If you are updating thousands of records at once then SQL might find that is less costly to lock the table once rather than locking thousands of individual rows.

SQL Server uses this to minimize the overhead of locking too many structures by escalating the locks from just row locks to page locks to table locks. There is now a lock escalation option in the alter table of SQL Server 2008 which allows the disabling of lock escalation on that table.

 

Thanks for reading this article,

Next steps :

  1. Share this with your colleagues because Sharing is Learning
  2. Comment below if you need any assistance