Want to disable Lock Escalation in SQL Server?

home » blogs » Quick Tip » Want to disable Lock Escalation in SQL Server?

Want to disable Lock Escalation in SQL Server?

change text size: A A A

Comments (0)
Print
Rating: 0/5 0 Votes

posted 10/3/2011 12:30:35 PM by Quick Tip - Views: [1252]

Lock escalation in SQL Server happens automatically when the number of Locks threshold is reached orthe memory threshold of the lock manager is reached. Locks are escalated from ROWS/PAGE to TABLEor PARTITION (if the table is partitioned and partition-level locking is enabled).
However, lock escalation can be completely disabled using TRACE FLAG 1211. Use it wisely as it canimpact DB engine performance. TRACE FLAGS are mostly used to diagnose performance issues !
Alternatively, you can use TRACE FLAG 1224. While 1211 disables escalation completely, 1224 is a littlerelaxed as it will not disable lock escalation if the engine is starving for memory !

Rate This Blog Entry: register or login

Author

Quick Tip (Member since: 9/30/2011 3:53:08 PM)

View Quick Tip 's profile

Comments (no comments yet)

Email Subscriptions

Get the Most Recent Blogs in your inbox

Quick Tip's latest blog posts

SQL Server 2012 Feature Pack

4/23/2012 9:24:41 AM by Quick Tip - Views: 241 - Comments: 0

Hi SQL Geeks, The Microsoft® SQL Server® 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012. You can download the ...
SQL Server: Use DBCC SQLPERF statement to see LOG space usage

12/1/2011 4:41:51 AM by Quick Tip - Views: 1330 - Comments: 1

You can use DBCC SQLPERF statement to observe log space usage. For example; DBCC SQLPERF(LOGSPACE); GO Output: Database Name Log Size (MB) Log Space Used (%) Status -----------------------------------...
SQL Server: Use DBCC SQLPERF statement to clear wait stats

12/1/2011 4:37:50 AM by Quick Tip - Views: 1452 - Comments: 0

You can use DBCC SQLPERF statement to clear wait & latch stats. For example; -- clear the wait stats DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR); You can use this statement to also clear latch stat...
SQL Server: Fdhost.exe can be stopped to avoid a reboot while apply Service Pack 2 to SQL Server 2008

10/21/2011 10:08:22 AM by Quick Tip - Views: 2567 - Comments: 0

While apply Service Pack 2 update to a SQL Server 2008 instance, you might come across the following: This basically means that if fdhost.exe is stopped, than a reboot will not be required. Fdhost.exe...
SQL Server: Want to see the complete memory dump of the page - use DBCC PAGE command

10/7/2011 5:10:57 AM by Quick Tip - Views: 1497 - Comments: 0

DBCC PAGE is one of many undocumented and unsupported commands in SQL Server. However, it is safe to use it. It is widely used by SQL folks, Microsoft SQL engineers for troubleshooting purposes. For e...
Want to disable Lock Escalation in SQL Server?

10/3/2011 12:30:35 PM by Quick Tip - Views: 1253 - Comments: 0

Lock escalation in SQL Server happens automatically when the number of Locks threshold is reached orthe memory threshold of the lock manager is reached. Locks are escalated from ROWS/PAGE to TABLEor P...
Want to enable Partition-Level Locking in SQL Server 2008?

9/30/2011 4:14:43 PM by Quick Tip - Views: 1016 - Comments: 0

In SQL Server 2008, lock escalation to Table object is default. You can make partition-level lock escalation as default as follows: ALTER TABLE [Table_Name] SET (LOCK_ESCALATION = AUTO); For more deta...

Latest community blog posts

SQL Server Analysis Services - Setting the Default Measure for the cube

5/17/2012 9:38:28 AM by Amit Bansal - Views: 372 - Comments: 0

Hi Friends, This is indeed so basic and simple and I got surprised when I observed during one of my assignments that the customer has not set a default measure for the cube; they just did not know abo...

SQLServerGeeks.com bi-weekly webcast – 16 May 2012 at 11:30 a.m. IST

5/14/2012 5:27:36 AM by Admin Admin - Views: 86 - Comments: 0

Dear SQL Geek, Hope you are doing well. Continuing the success, here is the next bi - weekly webcast, absolutely free for the SQL Server community. It is your opportunity to hear a specialist and this...

SQL Server 2012 - Determining the size of a Columnstore index

5/11/2012 9:41:45 AM by Amit Bansal - Views: 943 - Comments: 0

Hi Friends, By this time, many of you already know about columnstore indexes, a column store architecture that groups and stores data for each column on separate pages and then joins all the columns t...

SQL Server - My experience as a SPEAKER - SQLBits 10 March 2012

5/11/2012 9:37:12 AM by Amit Bansal - Views: 496 - Comments: 0

Hi Friends, Speaking at SQL Server conferences is something that I cherish a lot, even more when it is outside my home country, India. It is indeed a privilege to interact with global audience and sha...

SQL Server : Clustered Index Physically Orders a Table Revisited

5/8/2012 9:46:47 AM by Ahmad Osama - Views: 4051 - Comments: 6

Hi Friends, Continuing on from my last blog post “SQL Server: Clustered Index Physically Orders a table”, this post demystifies the myth “Clustered index physically a table”. T...

Installing SQL Server 32 bit on 64 bit Operating System

5/7/2012 9:14:49 AM by Amit Bansal - Views: 975 - Comments: 3

Hi Friends, Today I got a question on “can you install SQL Server 32 bit on 64 bit OS?” Answer: Yes, you can ! During setup, click on the Options link and select the architecture (64 bit o...

Analysis Services IgnoreUnrelatedDimensions property in Dimensions Usage Tab

5/2/2012 5:20:04 AM by Amit Bansal - Views: 606 - Comments: 0

Hi Friends, Look at this scenario: When I try to slice Reseller Sales measure group by Geography dimension, I get the following output: Rightly so, because the Geography dimension is not related to th...

SQL Server Day in Gurgaon in May 05’ 2012

4/30/2012 5:05:54 AM by Admin Admin - Views: 280 - Comments: 0

Dear SQL Geek, SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And after successful events in Bangalore, Kolkata & Mumbai, we a...

Completed-Advanced SQL Server Database Administration & Performance Tuning - October 2011

4/28/2012 9:37:27 AM by Amit Bansal - Views: 352 - Comments: 0

Hi Friends, Today, while I write this blog, I am preparing to travel to Hyderabad for another assignment with Microsoft India Development Center. My last training blog post was here: http://www.sqlser...

Restoring a SQL Server Database: A Practical Example – An effective approach used to set up LogShipping.

4/26/2012 10:40:00 AM by Satnam Singh - Views: 1308 - Comments: 2

Scenario : A couple of days back, I was asked to setup a Disaster Recovery Strategy for one of the database located on my Production Server which was using SQL Server 2005 and Service Pack 3. This was...

SQLServerGeeks.com bi-weekly webcast – 27th April 2012 at 3 p.m. IST

4/26/2012 5:00:54 AM by Admin Admin - Views: 214 - Comments: 1

Hi SQL Geek, Continuing the success, here is the next bi - weekly webcast, absolutely free for the SQL Server community. It is your opportunity to hear a specialist and this time it is Amit Bansal pre...

Super Hit SQL Server Sessions in Bangalore, Mumbai and Kolkata

4/24/2012 7:29:03 AM by Sarabpreet Anand - Views: 338 - Comments: 1

Hi Friends, Attending SQL Server sessions in India was never been so easy and accessible at- least not for free, but now anyone can attend these sessions for free and easily while being in their own c...

SQL Server 2008 R2 (sp1) & SQL Server 2008 (sp3) Feature Packs

4/23/2012 9:30:33 AM by Did You Know Did You Know - Views: 212 - Comments: 0

Hi SQL Geeks, if you are looking for SQL Server 2012 feature pack; we blogged about it here: http://www.sqlservergeeks.com/blogs/QuickTip/sql-server-bi/523/sql-server-2012-feature-pack But if you are ...

SQL Server 2012 Feature Pack

4/23/2012 9:24:41 AM by Quick Tip - Views: 241 - Comments: 0

Hi SQL Geeks, The Microsoft® SQL Server® 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012. You can download the ...

SQL Server QUOTENAME function

4/23/2012 9:09:51 AM by Amit Bansal - Views: 1160 - Comments: 4

Hi Friends, Here is an interesting function: QUOTENAME() which returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. (from Books Online) ...

SQL Server – Are Inline Table-Valued Functions better than a View?

4/20/2012 6:41:09 PM by Piyush Bajaj - Views: 321 - Comments: 1

Hello Folks, You would be very much aware of the View in SQL Server. But do you really know about Inline Table-Valued Functions? Well you don’t have to worry further more on this, because I will...

SQL Server Index Internals Part 3 – Index union operations

4/18/2012 5:15:08 AM by Amit Bansal - Views: 994 - Comments: 4

Hi Friends, I am writing this blog on my way back from Great Indian Developer Summit where I presented 2 sessions and received great feedback on my session. I had started the Index Internals series so...

SQL Server Analysis Services - another known bugissue in 2008 R2 64 bit – function library showing blank

4/16/2012 8:13:41 AM by Amit Bansal - Views: 748 - Comments: 0

Hi Friends, Last week while working on an assignment in Analysis Services, I came across a strange behavior that trace output was not showing. One of the readers of my blog, Tejal, pointed out that it...

SQL Server 2012 - Speaking at Great Indian Developer Summit (GIDS) - April 2012

4/16/2012 7:58:39 AM by Amit Bansal - Views: 1056 - Comments: 0

Hi Friends, This is turning out to be a fantastic year of conferences and speaking engagements. And God & my well-wishers continue to bless & wish… I haven’t been luckier before. ...

SQL Server 2008 R2 Express Database size limit

4/12/2012 8:20:10 AM by Ravi Kumar - Views: 926 - Comments: 0

Hi All, It is a great news to all those who use SQL Server Express editions. Microsoft has increased the database size limit of SQL Server 2008 R2 Express edition from 4GB to 10GB. Previous versions o...

SQL Server Analysis Services - Trace output not showing while processing the cube

4/11/2012 9:49:14 AM by Amit Bansal - Views: 3969 - Comments: 2

Hi Friends, I got a query from one of our blog readers that while he was processing the cube, the trace output was not showing up in the ‘Process Progress’ window. Something like this: If ...

SQL Server – What is Scalar Function?

4/11/2012 7:04:48 AM by Piyush Bajaj - Views: 3239 - Comments: 2

Hello Friends, It’s been a while that I have posted any blog. So here it is- Well with the arrival of SQL Server 2000, Microsoft has introduced the concept of User Defined Function (UDF). So the...

SQL Server 2012 Released to Manufacturing

4/9/2012 11:41:31 AM by SQLnews SQLnews - Views: 325 - Comments: 0

Exciting news! SQL Server 2012 has released to manufacturing. Customers and partners can download an evaluation of the product today and can expect general availability to begin on April 1. Microsoft ...

Winners Announcement – Webcast Quiz - 15March 2012 Thurs-Index Tuning by Amit Bansal

4/7/2012 5:45:58 AM by Admin Admin - Views: 233 - Comments: 0

Hi Geeks, We thank all the attendees who participated in the webcast quiz - 15March 2012 Thurs–Amit Bansal by Amit Bansal. The quiz was hosted here: http://www.sqlservergeeks.com/forums/sqlserve...

SQL Server 2012 SEQUENCE Feature RTM restrictions and update

4/7/2012 4:40:14 AM by Amit Bansal - Views: 4085 - Comments: 0

Hi Friends, I had written a series of blog posts on the SEQUENCE feature in DENALI (now SQL Server 2012). You can browse them here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/346/sql...