• 1) GUIDS

    2) probably unnecessary SORT/DISTINCT for the UNION

    3) Most importantly what is the hardware configuration?

    4) Have you done file IO stall and wait stats analysis during these hours-long runs?

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