• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 735
  • Last Modified:

Best time to rebuild indexes on the database

I would like to create a sql job to re-build all indexes on the database.

What things do I need to watch out for?

The first that comes to mind is to make that this is run before the backup task.
The process will run overnight.

Any other things do I need to consider?

The other question is how often should this task take place (once a week, once a day?)

Many thanks for your help.
  • 3
  • 2
  • 2
1 Solution
if you don't already have this, seriously consider it:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
with respect to some of your detailed questions, also refer to ola hallengren's FAQ's

>>The other question is how often should this task take place (once a week, once a day?)
please looks at the details of ola's index maintenance stored procedures - there are parameters that would permit 'light' index maintenance regularly and 'heavier' maintenance less frequently. The specifics of this will depend on your needs, the numbers of databases (& their size) etc. i.e. very hard to give precise answers.
n3alexanAuthor Commented:

Many thanks for this. Very interesting and useful indeed. I need to look at a greater detail.

How does this differ from the sql server built in Maintenance Plan wizard which generates the sql job one of which is to rebuild indexes?

Thank you,

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

In the FAQ section there is a link for a video with Ola in which you will find details about how the solution works with demos. I recommend you to spend time to watch that. I remember I had to download it as it wouldn't work in the browser.

To answer your question in regards to index optimizing, the Ola's solution differs greatly and is far superior to a regular maintenance plan in few aspects. One and most important is that the maintenance only does rebuilds every time is executed, which in most of the cases is not needed. Depending on fragmentation level different action is necessary. If is between 0 and 10% no need to do anything, between 10 an 40 reorganizing is sufficient, less resource consuming, and over 40 rebuild is needed, if possible with online option. Ola solution check all these things and applies the appropriate action.

Another aspect is object and database filtering are much more flexible.

It pairs the index optimizing with statistic updates and applies them only where needed.

Deals with partitions if you have partitioned tables.

Also worth mentioning is the logging feature which keeps every action that is applied, the actual SQL command with all the parameters, what the fragmentation was, how long it took, etc. And it does that in 2 ways, in a table and in job output files, which give the greatest detail.

One thing you need to be concerned with index optimization is hard drive space. Index rebuild is done in one transaction, which will cause the log file to grow.  So it needs space equivalent to the biggest table existing on the server(from all databases), at least, 1.5 times is recommended. This is needed only when you will run it for the first time though because after that, if you do it regularly, no rebuilds will be needed. I, for instance, scheduled the job daily, which keeps the fragmentation at low levels all the time.
n3alexanAuthor Commented:
Perfect thank you so much for the detailed explanation.

One last thing here is that there is already a backup task which runs every night.

Should any re-indexing or maintenance tasks run before the backup?

I guess that the backup and the re-indexing should not take place at the same time.

Am I correct in thinking that?

The type of back up I am referring to is a full backup once a week and differential backups the rest of the week.

Thank you

It doesn't really matter if you do the backup before or after. As soon as you implement the index optimizing and schedule it regularly your backups will include the optimized indexes anyways. I agree the full and diff backups should not be scheduled at the same time with index job(log backups are OK and actually necessary to free up your logs), not that it wouldn't work but because they will compete for resources.

If you have some big tables, databases you can plan to schedule 2 index jobs at different times, or as many as you need. In my case I created  one job for the biggest table(partitioned with 100's M rows) and db and one for the rest of the dbs. I also filtered out all the spatial indexes as they consume a lot of resources especially in terms of CPU usage. I will deal with them separately and later. The database and index filtering feature works great to be able to do these things, which are not possible with the built in maintenance.

In regards to the backup, and integrity check for that matter, I would recommend you to switch to Ola solution well. This way your maintenance will be placed under the same hood with unified approach, results and monitoring means(i refer to logging here). Nothing else will give you the possibility to keep track as good as Ola's solution. You should definitely implement the log backups as well if you haven't already done that. The way I did it was:

1. One weekly full backup done during weekend. No Diff backups that day
2. Daily differential backups, except for the full backup day
3. Every 15 min log backups, every day

The backup job is smart and if you add a new database will not fail if you run a diff and or a log backup on it as it actually checks every time what kind of backup can be applied to a database based on its backup history. Also it ignores databases that are not in ONLINE mode. You will see all the explanation in that video:

In the end I will just add that at this moment there is nothing better on the market in terms of regular SQL maintenance as Ola's solution. Especially when it comes to index optimizing. The built in maintenance lacks a lot of features that are needed but addressed in Ola solution and I expect that Microsoft will actually implement them, if they haven't done that with 2012 version already.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now