• SQL Guy 1 (5/7/2014)


    1. Change SORT_IN_TEMPDB = OFF

    2. Remove MAXDOP or set it to high and run at at weekend.

    3. Consider partitioning such a huge table by date.

    1) I believe the table is partitioned (...ON sh_WeekDT(Day_DT))

    2) It could be that there are other massive tempdb consuming queries running at the same time. Were you monitoring tempdb usage during these 20 hours?? Big necessity when you know you are going to be crushing it with something like you are trying to do.

    3) I also wonder if it isn't the ONLINE part that is getting you, or at least having a play in the issue. Keeping track of stuff that is currently getting modified can add up depending on what is happening.

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