At my company we've been using SQL Server for quite a while and have followed the recommended upgrade path. I've noticed that over the last couple of years I'm spending way more time rebuilding indexes and doing general functional maintenance than I ever did back when we were running SQL Server 2000. I realize that much has changed about our application so it's possible that this difference is of my own making. However, I feel fairly certain that, for us, SQL Server 2000 was much better at "just running" without much help than the newer versions of SQL Server.

My question is if anyone can confirm or deny this opinion and if this is true what the is the reason for it. I'm currently rebuilding index ever night which i never had to do in the old days. Thanks for any help.

  • Why are you rebuilding indexes every night? To remove fragmentation? How fragmented do they get in one day then? Mar 29, 2011 at 16:38
  • If we don't rebuild them, I find that the performance of the application really slows down by mid-day. So it's really a sort of catch all for performance degradation.
    – Bob Par
    Mar 29, 2011 at 18:04
  • Perhaps the statistics on your indexes are out of date? I hit this recently with new rows being added to a very large table. Might be worth a try, refreshing statistics would be faster than rebuilding the indexes. Mar 29, 2011 at 18:12
  • A good blog post on the statistics issue - might you be hitting that kind of problem? Mar 29, 2011 at 20:45

2 Answers 2


Apart from the TechNet Magazine article I wrote that JGurtz referenced above, your problem is either fragmentation or statistics.

If perf is slowing down gradually, its fragmentation. If perf suddenly drops off a cliff, its statistics getting out-of-whack and causing a query plan change.

If its fragmentation, consider changing keys to something that doesn't cause random inserts, or using a FILLFACTOR. Google 'Paul Randal FILLFACTOR' for more info.

If its stats, then updating stats rather then rebuilding the indexes may be a more efficient solution and take fewer resources.



If anything 2005 and newer are much better at "running themselves" but I would not really ever say that as all sql servers needs some maintenance. It's just the nature of the beast...

Here are some Index related resources:

http://performance.sqlpass.org/PresentationArchive.aspx Look at the Jan, Feb, Mar 2011 talks


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.