Post reply

Clustered index (NoLock)

  • May 12, 2008 at 6:34 am

    #185395

    i have created table with nonclusted index ,At the time of transaction one transaction id locking this table by another session id . but if create clustered index on it , it is not happeing . can anyone explain the reason behind this locking ?

    Thanks in Advance .

  • May 12, 2008 at 6:38 am

    #813737

    Probably a different query plan was generated with the clustered index present that required less of the table to be locked.

    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

  • May 13, 2008 at 4:52 pm

    #814865

    I agree with Gilamonster (whatever the name is)

    Look at the waittypes and waittime while executing the sql statement ..the Optimizer might not even use the non clusterd index and did a table scan hence you had a lock escalation of a exclusive lock at table level.

Viewing 3 posts - 1 through 2 (of 2 total)

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