• alen teplitsky (3/24/2011)


    is it better to use a non-clustered index in a large delete? i thought it was the opposite since you would be deleting data that is logically together in the table

    years ago i saw an issue where large amounts of data was inserted, deleted and modified on a non-clustered index and it cause a lot of blocking when the app changed to use multiple threads. we changed the clustered index column and the problems went away since the data that was changed was on the same pages and extents

    changing the clustered index on a huge table is often simply not possible due to tlog constraints and/or time. If your existing CI gets you a seek to deleted records that is great for you though!

    also, my comments are directed at the (much more common IMHO) scenario where you have a maintenance window to do the massive delete without concurrent-access issues. That brings a whole 'nother level of headache since you need to leave all strutures in place and pay a huge overhead to log all the existing index deletes and you also have to use much smaller batch sizes to avoid long-duration locks causing unwanted blocking.

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