I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due to a database shrink.
So the position I'm in is basically waiting it out and stewing on what I forgot to do. I'm not going to kill the query as then I will enter the even longer and more frustrating roll-back hell.
But this has got me thinking...is there a way to add an index on a table already actively running a query and would the late arrival of healthy indexes speed up the remainder of that query?
After thinking about it my feeling is that just rebuilding an index on a table already involved in a running query would probably be very slow and slow down the query itself resulting in an even longer wait, but, is there a way to pause a query to carry out the addition of an index?
I suspect not as suspending one query mid-way to allow other things to happen feels like it could lead to mayhem, but I'm just interested to know if anyone has had any experience of this?
------------------- EDIT --------------------------------
Thanks for your responses. This is the offending query complete with its plan: https://www.brentozar.com/PasteThePlan/?id=SyP_Cu3Hu
Here's the query that ran much quicker without the clustered index: