• This is a very well thought out article and well-written.

    But I'm with stevemash here. You don't address locking at all in your article which is a big issue for people purging high transaction tables.

    I have a few suggestions:

    Taking smaller bites, which you mentioned, can sometimes beat the calculus of lock escalation

    (This has something to do with % of rows being updated/deleted and the amount of memory pressure)

    Disabling lock escalation is one method, but it can eat up lock memory so should be used with caution.

    Disable lock escalation in your session using Trace 1244 or 1211 (nuclear option)

    You can also jam open a transaction with an update lock like so:

    BEGIN TRAN

    SELECT * FROM [dbo].[myTable] with (UPDLOCK, HOLDLOCK) WHERE 1=0

    WAITFOR DELAY '10:00:00'

    COMMIT TRAN

    The lock hints start an IX lock on the table which is held for one hour.

    Any other transaction's table lock on dbo.myTable won't escalate past your jam (unless someone else manually uses the tablock hint)

    This is pretty much like using the trace flags... you could probably run out of lock memory and tank your system this way, since SQL wants to escalate, but can't.

    ~Craig