Systems Engineering and RDBMS

Archive for May 5th, 2008

Lock Escalation Changes in SQL Server 2008

Posted by decipherinfosys on May 5, 2008

We have talked about lock escalation in some of our previous posts – you can read more on it here and here. In SQL Server 2008, there is an enhancement to the ALTER TABLE statement that allows a new LOCK_ESCALATION option that controls the escalation options on a table. One can thus disable table level lock escalations. If you have implemented partitioning in your system, then this option can be applied at the partition level as well.

This option has three possible values: TABLE, DISABLE and AUTO. TABLE is the default value and the behavior remains the same as SQL Server 2005 i.e. the lock escalation (when happens) is done at the table level regardless of whether the table is partitioned or not. DISABLE option prevents the table level lock escalation and the AUTO option lets SQL Server engine decide the lock escalation granularity – if the table is partitioned, then the escalation is allowed at the partition level and if it is not partitioned, then the escalation is done at the table level.

Posted in SQL Server | Leave a Comment »