0

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.

Bob Par
  • 1
  • Why are you rebuilding indexes every night? To remove fragmentation? How fragmented do they get in one day then? – Martin Smith Mar 29 '11 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 '11 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. – Philip Kelley Mar 29 '11 at 18:12
  • [A good blog post on the statistics issue](http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/) - might you be hitting that kind of problem? – Martin Smith Mar 29 '11 at 20:45

2 Answers2

5

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.

Thanks

Paul Randal
  • 7,184
  • 1
  • 35
  • 45
2

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

http://technet.microsoft.com/en-gb/magazine/2008.08.database.aspx

JGurtz
  • 523
  • 5
  • 13