• You're probably getting escalation because of the rowlock hint.

    Without the hint, SQL takes locks at whatever granularity it thinks best and escalates to table locks at a specific threshold

    With the hint, SQL takes row locks and escalates to table locks at the same threshold.

    So if SQL would have taken page locks, but you force it to take row locks, that means more locks taken and an earlier escalation.

    Try dropping both the nolock hint (which is meaningless on the target of an update and harmful elsewhere) and the rowlock hint, see if that helps. Also try tuning the query and indexes. The query's probably not using indexes efficiently, so reading and locking far more rows than ideal.

    There are ways to completely prevent escalation, which results in SQL running out of memory and potentially crashing instead of escalating locks.

    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