Environment is SQL 2008 R2, 26GB RAM, 4-core Xeon E5-2650 @ 2.6GHz.

Please see the image below, our maintenance plan which reorganises indexes (not rebuilds). This is run on a weekly basis, and within one week, this plan went from taking ~5 minutes way up to 1hr - 1hr:15, and it has not improved since. We've had to disable this plan and work around this in another way. I've looked at the amount of data in the database and it hasn't grown by a considerable enough volume to cause this issue.

Maintenance plan history

I've been in touch with our hosting company to see if anything has changed within our environment, but they are saying nothing's changed, there's no limitations/restrictions in place on our VMs, IO seems ok. We haven't made any changes to the underlying application neither.

From what I can see, other things have started to fail within this week period too - some queries which ran perfectly fine are starting to timeout. I'm at a bit of a loss as to what could have caused this and am unsure how to retroactively find out. Could anyone point me in the direction of where to begin to discover what could have been wrong here?

  • 2
    Short of someone actively monitoring at 4am, you need to find out what is actively running during that time - this post will get you started - brentozar.com/responder/log-sp_whoisactive-to-a-table – Scott Hodgin May 12 '17 at 10:31
  • What's your criteria for reorging the index? Is there something that's started happening (a new process or application upgrade) that's causing the indexes to become heavily fragmented? – David Fowler May 12 '17 at 11:43
  • I didn't realise how bad it was at the time, but it was the maintenance plan's standard "reorganise indexes" tasks, which just does it to every table. I've since swapped over to Ola Hallegren's scripts which are working much better. Since the plan was running every week, I can't see what'd cause a high fragmentation count to occur in a week, and then to affect every subsequent run of the plan. – Steven Sproat May 12 '17 at 13:00
  • I was reading that article yesterday and was looking to implementing this on our server, to help with potential future issues. Thanks for the advice – Steven Sproat May 12 '17 at 13:01
  • Maintenance plans are horrible when it comes to reindexing, good choice moving to Ola's scripts. I'd probably start out by monitoring exactly what you're reorganising, looking out for any large, heavily fragmented indexes. When the indexes are heavily fragmented, they really can take a long time to reorganise. You might find that it's nothing to do with fragmentation but once you've got a fair idea of exactly what's going on it'll be easier know where to look for a cause. – David Fowler May 12 '17 at 13:24

Question you asked is a very generic one and people working with database have this challenge all the time. There is no one thing you can do will give you the answer right away. Your case is more difficult than others as your databases are hosted by third party.

Troubleshooting long running process is a science and art at the same time. Everybody has their own way and it is not unique.

First you want to make sure your reorganize process still doing the same work. Which is not easy in this case because few factors:

  • Number of index.
  • Size of each index.
  • Fragmentation level of each index.

Once you check that out and confident that the amount of work is same, you can use method called ‘waits and queues’ to find out why the duration increased?

A SQL server process can be in three states. Running, Runnable and Suspended. I suggest you read this more here. There is nice method for capturing wait stats for one single operation.

Once you know what resources your process was waiting for and how long (during the whole duration) the waits were, you should be able to quantify the run time. You can be blocked but underneath that is also a wait because some other process is holding the resource that you need.

As you are in a hosting environment and if you have baseline number of your hardware you should check those out too, to make sure nothing changed there. I understand you did check with them but I suggest you confirm what they are saying.

Few things to check:

  1. storage performance
  2. Network
  • Thanks for the answer, I was unsure whether there was anything "out of the box" to do some historical digging - now I think it'd be down to us to put something in place. We don't actually have access to the virtual hosts, only the underlying VMs so it's hard to prove what the hosting company is saying. I have a feeling it's environment related though because we have 3 SQL instances, all with maintenance plans set up, all with different underlying applications and all 3 saw a big spike in the maintenance plan timings with no app changes. I'd have to accuse my hosting company of lying... – Steven Sproat May 12 '17 at 14:30
  • There are dmv's that save waitstats information, store procedure statistics since last restart of sql engine. But that I doubt if any of that will be helpful in your case with the maintenance plan. – SqlWorldWide May 12 '17 at 15:04

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.