Is it worth adding or rebuilding an index on a table already involved in an active query 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:
quicker query
It is not possible as the rebuild operation would be blocked due to the actively running query holding a lock on the table, and therefore it wouldn't complete until after the running query completed. Note this is to ensure data consistency, as some of the data pages of the index could contain a mix of data that was processed and data that was not yet processed by your query, and moving that data around could result in repeated reads of data for your query, resulting in invalid results, etc.
A couple other things I'd like to note, I'm not sure why a SHRINK was recently run on your database, but just a heads up that rebuilding your indexes will cause the database to regrow again. (I'm not saying you shouldn't rebuild your indexes, as 95% fragmentation is high, but just a heads up on how SHRINK is usually a wasted operation.)
On that note, 95% fragmentation is not usual unless your tables don't have clustered indexes on them. Just wanted to make sure you do have clustered indexes on your tables (as it is only in rare edge cases one would not use them)?
Also, there's unfortunately no way to determine exactly how long a rollback will take. It's possible the rollback could be anywhere between instantaneous to longer than the query has been running for. So if you want to try killing it, it is possible you get lucky and it rolls back quicker than the remainder it would've taken to complete. But it's hard to say. (I've rolled back hours long running queries in seconds before, but it's no guarantee.)
Finally, even with 95% index fragmentation, 12+ hours for a query to run is also an unusual amount of time. I'd be curious how big your tables are, and what kind of query it is that's running so slowly, if you'd be willing to share. I'd bet it can be sped up a lot more, even after rebuilding your indexes (as that only helps so much).