SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Uri Dimant — Sun, 01 Nov 2009 06:14:42 GMT

Hi Adam

Its intresting. I have been doing lots on such things recently. How do you insert 100 million rows? One batch? Using trace 610?

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Adam Machanic — Sun, 01 Nov 2009 23:29:25 GMT

Hi Uri,

One batch, in this case. I'm not familiar with TF 610--what does it do?

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Uri Dimant — Mon, 02 Nov 2009 07:43:51 GMT

Hi Adam

Hmm, 100 million on one batch, I have to test it :-)

>I'm not familiar with TF 610--what does it do?

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Abe Weinograd — Mon, 02 Nov 2009 15:59:55 GMT

Hi Adam,

http://msdn.microsoft.com/en-us/library/dd425070.aspx That article has been really helpful. I have outright disabled lock escalation and used TF 610 to help here. I had to also play with batchsizes to get optimal performance.

I found that even when inserting in the order of the clustered index with concurrentinserts, i had to use a real batch size. If you are inserting into an index concurrently, i think you have to make sure that the data sets are non overlapping.

Abe

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Adam Machanic — Wed, 04 Nov 2009 00:21:24 GMT

Unfortunately, I can't do minimal logging in my scenario, because we have CDC enabled on the table (whether or not this is a good use for CDC is a whole other topic). And while I can stop the key locks on the main insert, I can't control what CDC does; it will kick in and cause the same problem when it does its own work.

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Florian Reischl — Fri, 15 Jan 2010 11:42:46 GMT

Just referred at

http://www.mycsharp.de/wbb2/thread.php?threadid=80507

(German)

Greets

Flo

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Adam Machanic — Fri, 15 Jan 2010 14:42:57 GMT

Thanks, Flo. Now translate it for me :-)

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

bjan — Fri, 18 May 2012 09:44:34 GMT

I am looking for Dont Escalate and failed to achieve row level locking. Please look at http://stackoverflow.com/questions/10648448/i-need-row-level-locking

re: SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Murali — Wed, 19 Dec 2012 20:33:02 GMT

I definitely agree Adam.. I noticed the same exact strange behaviour in SQL 2008