Solved

Best time to rebuild indexes on the database

Posted on 2013-06-21
7
651 Views
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.
1
Comment
Question by:n3alexan
[X]
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
7 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39265326
if you don't already have this, seriously consider it:
http://ola.hallengren.com/

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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39265374
with respect to some of your detailed questions, also refer to ola hallengren's FAQ's
http://ola.hallengren.com/frequently-asked-questions.html

>>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.
0
 

Author Comment

by:n3alexan
ID: 39265424
Paul,

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,

N.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 27

Expert Comment

by:Zberteoc
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.
1
 

Author Comment

by:n3alexan
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

N.
0
 
LVL 27

Expert Comment

by:Zberteoc
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:

http://sqlbits.com/Sessions/Event9/Inside_Ola_Hallengrens_Maintenance_Solution
1
 
LVL 27

Expert Comment

by:Zberteoc
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

696 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