• So, you have a table with ~2,000k rows, you're deleting ~750k rows in batches of 50k, it's taking ~10 minutes, and you want to minimize the runtime duration. I'm guessing the biggest performance hit will be the I/O required by on the fly page reorganization and transaction logging. The problem is that deletes are the most expensive type of operation in that regard. Also, using the batch delete method, you're left with a fragmented table that could be the same size, or maybe even larger, than the original.

    Maybe I'm wrong, we never know for sure until we experiment, but I suspect that selecting the rows you need into another table would require only 10 seconds or so, because selecting into a non-indexed heap table is a minimally logged operation. Once done, drop the original table, rename the temp table, and then re-create indexes (remember to add clustered index first, then non-clustered), which might take another 10 or 20 seconds. Another benefit of the select into method is that once done your table will be logically sorted with no page or index fragmentation.

    SELECT *

    INTO F_POLICY_TRANSACTION_TEMP

    FROM F_POLICY_TRANSACTION (tablock)

    WHERE POLICY_TRANSACTION_BKEY like 'SIC%';

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho