Solved

Best time to rebuild indexes on the database

Posted on 2013-06-21
7
585 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

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 48

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

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 26

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 26

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now