Best time to rebuild indexes on the database

Posted on 2013-06-21
Medium Priority
1 Endorsement
Last Modified: 2013-06-21
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.
Question by:n3alexan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39265326
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.
LVL 49

Expert Comment

ID: 39265374
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.

Author Comment

ID: 39265424

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,

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

LVL 27

Expert Comment

ID: 39265474
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.

Author Comment

ID: 39265535
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

LVL 27

Expert Comment

ID: 39265879
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:

LVL 27

Expert Comment

ID: 39265913
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question