• d@mmit! I had a nice response typed in and submitted and it isn't showing up! 🙁

    Anyway, let me try to recreat.

    One thing I didn't see mentioned is if the WHERE clause field(s) are NCindexed it can be HUGELY beneficial for both performance AND concurrency to set the batch size (which I usually HARD CODE) such that you get index seeks/bookmark lookups for the DML action at hand. This avoids repeated table scans, which for (very) large tables can be REALLY REALLY bad thing to do for both perf and concurrency. I have used this to great effect many times in the past.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service