---
title: Disable Lock Escalation at table level
description: Disable Lock Escalation at table level from SQL Server 2008 onward.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [Disable Lock Escalation at table level](https://www.pythian.com/blog/disable-lock-escalation-in-sql-server)

 Written by [Pythian Marketing](https://www.pythian.com/blog/author/pythian-marketing) | Apr 10, 2015 4:00:00 AM

## Introduction

If a lot of rows or pages are locked, the SQL Server escalates to a table-level lock, to save resources. Each single lock takes approx. 100 bytes. So if you have many locks it takes a lot of resources to manage them. (There is a great blog about lock escalation, if you want some more info: [https://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx](https://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx))   Until SQL Server 2008, there was no way to change the lock escalation for a single table. You could deactivate the escalation for the server by using the Trace Flags:

- **1211 **- Disables Lock Escalation completely - allows to use 60% of the allocated memory - if 60% of memory is used and more locking is needed you will get an out-of-memory error.
- **1224 **- Disables Lock Escalation until the memory threshold of 40% allocated memory is reached - after that Lock Escalation is enabled.

## Situation

But that was in most cases not a good choice and caused a lot of performance problems. In SQL-Server 2008 and above there is a new table option (ALTER-TABLE) that can be used to change the default Lock-Escalation. This helps you if you have a table where you want to disable the escalation or if the table is partitioned.   On a partitioned table activating the AUTO Option can improve concurrency, by escalating the locks to the partition-level and not to the table-level. ALTER TABLE - table option: **SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )**

- **AUTO **(should be considered if you have a partitioned table)

- If tables is partitioned - the locks will be escalated to the **partition-level**
- If table is not partitioned - the locks will be escalated to the **table-level**

- **TABLE**

- Default behavior
- Locks are escalated to the** table-level**

- **DISABLE**

- Lock escalation to the **table-level **is deactivated in most cases
- In some necessary cases it's allowed to escalate to the table-level

This is a cool feature, that are many developers are not aware of. Thanks for Reading!

## SQL Server Database Consulting Services

Ready to future-proof your SQL Server investment?

[View full post](https://www.pythian.com/blog/disable-lock-escalation-in-sql-server)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Pythian Marketing"
  },
  "dateModified" : "2026-03-26T06:39:37.727Z",
  "datePublished" : "2015-04-10T04:00:00Z",
  "headline" : "Disable Lock Escalation in SQL Server",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/disable-lock-escalation-in-sql-server",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```