Lock escalation

Question

Lock escalation

jungnaja

Old Hand

Points: 362
More actions
February 12, 2014 at 9:16 am

#285574

I kept seeing Lock Escalation (Profiler) on UPDATE statements with WHERE clause of 10 records or less. I have no luck finding an explanation. I hope someone here can explain to me why.
Will using While-Loop to process records in batch (5000 or less) avoid lock escalation?
SQL Server 2008 R2 that's what I'm using.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Does the update use an index? How many does SQL have to read to identify those 10 rows?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jungnaja Old Hand Points: 362 More actions · Answer 2

Thanks for a quick response.

It update columns used in a non-cluster index; a where clause has a column that is part of the same non-clustered index.

Sorry, I didn't look at the read when update done. When I use run select statement, it's about 1K logical reads, and 0 physical reads.

I hope I answer your questions.

They mode was 5-x and type is 5 - object. Is it that bad?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

jungnaja (2/12/2014)
When I use run select statement, it's about 1K logical reads, and 0 physical reads

So that's 1000 pages read to get the 10 rows. If it's starting with row locks doesn't sound all that surprising that it's escalating locks. Maybe try to tune it and get the reads down? Less read = less locks needed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jungnaja Old Hand Points: 362 More actions · Answer 4

Sorry, gotta ask the stupid question.

What should I look into tuning to reduce logical reads? Would you please direct me to a good article?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

http://www.amazon.com/Server-2012-Query-Performance-Tuning-ebook/dp/B008E6HOIS/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass