Post reply

Lock escalation

  • 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.

  • February 12, 2014 at 9:20 am

    #1688450

    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

  • February 12, 2014 at 9:40 am

    #1688466

    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?

  • February 12, 2014 at 11:21 am

    #1688519

    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

  • February 12, 2014 at 1:02 pm

    #1688552

    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?

  • February 12, 2014 at 10:34 pm

    #1688597

    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

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

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