But what if you DON'T have time?
I suggested rebuilding the indexes once a week. And the bog standard maintenance plan rebuilds every index, whether it needs it or not. But if things are heavily fragmented, or there are a lot of indexes with millions of rows each, that might take an awfully long time. I would expect heavily fragmented indexes to adversely affect performance, so set up a Smart Rebuild / Reorganise job to run as part of the weekly maintenance plan.
Here's the script - I swiped it from Artemakis Artemiou - poli efcharisto!
The job identifies all the indexes with fragmentation levels >15%It starts with the worst and works down
Above 30% it rebuilds them
Between 15% and 30% it re-organises them
If the database is badly fragmented, you might want to run this daily, at least until they are under control.
I also set up a job to update statistics. Statistics can get out of date, and queries may be less efficient because of this.
Updating statistics is a bit of a funny one. If you rebuild the indexes, you get a nice new set of statistics. But if we are not rebuilding the indexes, you have to rely on Microsoft to do it as it thinks fit, according to its algorithm. Sod's Law says that it won't update the ones you want it to update. The SQL execution plan can ignore a perfectly good index because the statistics are not up to date, and may take far longer to run a query because of this.Note that Bill's script sets a time limit too, so I don't really need to set an execution timeout.
As before, I added a T-SQL task to the Daily tasks, last in the line.
I've given it a timer limit of 5400 seconds (an hour and a half, to save you getting your calculator out)
And it runs a stored procedure which I found here...
Thanks to Bill Graziano!
There's an alternative to the above - the well regarded and reputable scripts from Ola Hallengren.
A lot of people swear by them.