I do have the actual execution plan for the monster query so I will start going through that.

Lynn Pettis (5/17/2012)

---

Elizabeth.Block (5/17/2012)

---

I thought it was stale stats too but we updated the stats on those tables and it didn't help.

Now for the bad news... I rebuilt the...

I thought it was stale stats too but we updated the stats on those tables and it didn't help.

Now for the bad news... I rebuilt the 3 indexes yesterday...

Jeff Moden (5/17/2012)

---

Can you be more specific about that please? For example, how do you know they weren't good if...

Scott, I agree that you have to proceed cautiously. I started a simple spreadsheet noting what things I tried, indexes, tools, etc. so I wouldn't forget what I had done...

I think the performance issue was a combination of things. I think somehow one of the large indexes became corrupt, maybe there was new growth on one of the big...

Thanks, Grant, for your info. I didn't know that DTA wasn't good at a single query. I'll have to check out your books. I don't really do much query tuning...

All good ideas. I'll have to look into those. It may be a combination of a tipping point in the data (the largest table has 81 million rows), a tempdb...

Thanks, that sounds possible. I have added some new indexes so maybe there's nothing to find. I just found it disconcerting to see a monster query that the DTA finds...

Thanks for the info. I can't really test beforehand because this is for an upgrade. Once the upgrade is in place if SSRS does impact the shared server the customer...

Yep, that's what I'm trying to avoid. I'm at least going to tell my boss what I recommend and ask for a backup plan if/when that happens. It's not fun...

Thanks, I'll try that.

It would be helpful if I could find best practice articles, load tests or whitepapers on recommendations to bring back to my boss. The bigger issue is that if SSRS...

Good wording! I'll have to remember that one. 😀

We were originally trying to do a restore but couldn't get it to work. When I tried to do the restore SSMS seemed to think the file was corrupt. After...