Mainly I am interested in understanding why a select query would stay open and running for almost an entire week. CPU use rarely if ever goes above 5 to 10%. The SQL Server is definitely busy doing something but I don't know what.

The select query joins tables that contain hundreds of millions of rows. Adding non-clustered indexes have made the query just about as optimal as I can get it.

The whole thing wouldn't bother me that much other than extreme curiosity but for the fact that index maintenance always causes blocking due to locks that are held all week. I spend a good deal of time trying to squeeze performance out of this database server because the application team is very frustrated with the overall performance of the application and I want to feel certain that there is not an optimization on the database that I am missing.