1

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

1
4

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).

9
  • 1
    Thanks for your response. This is very helpful. The whole SHRINK thing was the result of working with very limited disk space. We ran out of disk completely mid-indexing a few times. An upgrade to the hardware is on the way. I won't be shrinking again. I actually bit the bullet and killed and I got lucky, it rolled back relatively quickly. The tables do have clustered indexes but they are large (matching 30M to 40M) and the matching is via geographic spatial matching which I think is quite involved. My Query is in my edited question. Thanks again. – Columbo Apr 7 at 15:39
  • @Columbo No problem, I'm very glad to hear my advice was helpful and you got lucky with the rollback process! 🙂 (I've been on the other side of the coin, waiting hours for the rollback to finish too lol.) Everything else you said in your comment sounds reasonable. I'll take a look at your query when I get a chance later (as I've worked with tables with 10s of billions of rows, and even then I've never had a 12+ hour query lol, I'm sure we can improve your execution time). One other thing would be very helpful if possible, is if you could link a copy of the execution plan in your post?... – J.D. Apr 7 at 18:48
  • ...I'm assuming you won't be able to get the actual execution plan since the query takes so long, but the estimated execution plan would definitely be of help in debugging the performance too. You can upload the plan to Paste The Plan and then link it in your post. – J.D. Apr 7 at 18:48
  • 1
    I've used Sentry One and uploaded my plan and added the link to the original question. If you do you notice anything in the plan please let me know, no matter how basic, I'm picking this stuff up as I go along. Thanks again. – Columbo Apr 8 at 13:25
  • 1
    No need to apologise, I am grateful that you have taken the time to look at this. – Columbo Apr 12 at 8:50
3

Given the fact that the query plan was built before the query kicked off, the answer is no.

1

The query in this question was inserting in to a table (Object1) that had a clustered index on a random GUID. I suspect that this may have been the problem since the query would have had to recalculate and reorder the index at each pass as it inserted newly generated results (please correct me if I'm wrong, I want to learn this stuff).

I removed the clustered index on the GUID, added an incrementing primary ID column (int) and then added a clustered index to the incrementing primary ID column. Now, I think, the newly generated rows are being given the next incrementing ID that just needs to be added on the end of the clutered index. Much less work, I would think, than having to find where a random GUID fits in a clustered index and making space for it.

The query now runs in 3 hours.

Also, the previous query was using at least 300gig of disk space while processing and running out of disk space. This query seems to have used only 10 gig or so which is just the resultant table and index space.

I think this is the answer as I didn't change anything else.

2
  • Just saw this now as well, glad you found a way to improve performance and disk usage! 🙂 If you want to post your updated query and execution plan (actual plan would be best but estimated works too if you don't have 3 hours to kill waiting lol :) I wouldn't mind seeing if we can get that query down to under an hour if possible. I did see some huge cardinality estimate issues on first glance of the original execution plan you linked which I'll update my answer with more information on and what that means when I get a chance today. – J.D. Apr 11 at 19:56
  • 1
    Cheers JD, much appreciated. I've posted the quicker query. There was one change. The same query is done twice, which is the same, but in the final left join of each query I am now joining (Object8.Column4 = ?) instead of (Object2.Column4 = ?). To be honest I assumed that would make little difference, other than that it was only the removal of the GUID clustered index and the addition of the stright int ID clustered index that changed. – Columbo Apr 12 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.