Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Lock Escalation Limit Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 8:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 802, Visits: 1,358
Comments posted to this topic are about the item Lock Escalation Limit

Thanks.
Post #1465443
Posted Wednesday, June 19, 2013 10:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Got to learn something. I have never looked into Lock escalation mechanism in depth. Thanks Sourav

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1465452
Posted Wednesday, June 19, 2013 11:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Lokesh Vij (6/19/2013)
Got to learn something. I have never looked into Lock escalation mechanism in depth. Thanks Sourav


+1




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1465456
Posted Wednesday, June 19, 2013 11:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
A very good question and well explained as well.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1465462
Posted Thursday, June 20, 2013 1:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:15 AM
Points: 820, Visits: 1,505
Thank you for your question. I admit I got it wrong, but I've learnt something new.

Incidentally, you state that

[Note: This is for SQL Server 2008 R2 Onwards]


According to msdn, the escalation threshold is also 5000 in SQL Server 2005 and 2008 as well.

Curiously, I can't find any explicit reference that states this threshold for SQL Server 2012.
Post #1465494
Posted Thursday, June 20, 2013 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
martin.whitton (6/20/2013)
Curiously, I can't find any explicit reference that states this threshold for SQL Server 2012.

If you follow the link in the explanation, you'll see (right at the top) the text: "Applies to: SQL Server 2008 R2 and higher versions". I think this is a standard disclaimer that MS puts in to save them the effort of researching whether or not articles apply to older versions.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1465512
Posted Thursday, June 20, 2013 2:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 12:20 PM
Points: 1,792, Visits: 895
(from the question today)
> [...]triggers Lock Escalation (i.e. Row lock to Page level lock)

This would seem to refer to an event that sets off the mental alarms...

(from the reference)
> The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks.

My memory was that Lock Escalation from row or page level always jumps to table. I assume I'm simply misinterpreting the question, but figured I'd ask/point just 'cause I haven't had my coffee yet...
Post #1465523
Posted Thursday, June 20, 2013 3:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Rich Weissler (6/20/2013)
My memory was that Lock Escalation from row or page level always jumps to table. I assume I'm simply misinterpreting the question, but figured I'd ask/point just 'cause I haven't had my coffee yet...


That depends. There is a lock escalation hierarchy, usually after page it is an extent (group of 8 pages). For more on escalation hierarchy, refer the link below:

http://msdn.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

Furthermore, this hierarchy can be altered with ALTER TABLE commanding using SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1465526
Posted Thursday, June 20, 2013 3:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 3,861, Visits: 5,003
Rich Weissler (6/20/2013)
(from the question today)
> [...]triggers Lock Escalation (i.e. Row lock to Page level lock)

This would seem to refer to an event that sets off the mental alarms...

(from the reference)
> The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks.

My memory was that Lock Escalation from row or page level always jumps to table...


+1


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1465538
Posted Thursday, June 20, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
Lokesh Vij (6/20/2013)
Rich Weissler (6/20/2013)
My memory was that Lock Escalation from row or page level always jumps to table. I assume I'm simply misinterpreting the question, but figured I'd ask/point just 'cause I haven't had my coffee yet...


That depends. There is a lock escalation hierarchy, usually after page it is an extent (group of 8 pages). For more on escalation hierarchy, refer the link below:

http://msdn.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

Furthermore, this hierarchy can be altered with ALTER TABLE commanding using SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

I'm sorry, but you are wrong. SQL Server will never escalate row level locks to page level locks.
By default, SQL Server will take row level locks, and at the threshold indicated in today's question they will escalate to either table level or partition level (for partitioned tables) locks.
With index-level options or query hints, you can cause SQL Server to start out by taking page level locks instead of row level locks. The same threshold is then still used to escalate, again either to table level or partition level.

The ALTER TABLE option you mention can only be used to force SQL Server to escalate to table level locks instead of partition level locks for a partitioned table, or to (almost) completely eliminate lock escalation.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1465550
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse