Is a weekly rebuild of indexes a good idea?

Question

We had a SQL expert suggest rebuilding our table indexes weekly. We're currently doing this Saturday night and it causes lots of timeouts during the rebuild process. I'd like to avoid the timeouts if possible, so is it a good idea to do the weekly index rebuild?

We're running MS SQL server 2014.

UPDATE We get this error during the rebuild "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "

Thanks!

Answer 1

Dont just blindly go for rebuilding all the indexes. There is a much intelligent way of doing it. (hint: SQL Server Index and Statistics Maintenance by Ola hallengren)

This is an official guideline from Microsoft (and its a good starting point - instead of blindly rebuilding all the indexes):

click here for enlarge

Also, if you are using sql server enterprise edition then Rebuild is an ONLINE operation (REBUILD ONLINE = ON)

Also, read : Online Index Rebuild - Can Cause Increased Fragmentation - when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives.

As a side note: Paul Randal has a nice checklist of VLDB Maintenance best practices from his MS days :-)

---

The benefit of using Ola's script is that it has logging functionality [logs to dbo.CommandLog table] which over time will become a gold mine and you can get real value out of it.

e.g.

• If you are deploying index maintenance, then you can use dbo.CommandLog to find out what indexes are frequently getting fragmented often, the time taken to defragment them and if you join to sys.indexes then you can tie up FILL FACTOR and adjust it. Check - monitoring-for-endless-index-defragmenting.
• Also, if you have DatabaseIntegrityCheck - CHECKDB solution, then you can find out how long it takes to perform CHECKDB.

Answer 2

We're currently doing this Saturday night and it causes lots of timeouts during the rebuild process

This would be an obvious scenario when the index maintenance job runs.

The time, when it should be done is something you need to figure out depending upon the load on you're system.

Also, i believe you would be doing index maintenance of all the databases or the complete database. You can consider customizing them depending upon the how bad the fragmentation is:

If you need to minimize downtime, custom index maintenance scripts should help. Ola Hallengren’s maintenance scripts. These are indeed very good scripts which can help and customize as per you're needs. We always use this and analyse our downtime accordingly.

Answer 3

Is a weekly rebuild of indexes a good idea?

Not only a good idea, a requirement on any non-trivial production system.

However, I recommend against using SQL Servers built-in maintenance plans.

Using SQL Servers built in maintenance plans gave us an enormous amount of heart burn similar to the issues you are reporting on our production databases. We switched to using SQL Fools Index Defrag script over a year ago and have never looked back. We get good performance against production systems along with excellent reporting of what was done and how long it took.

On most of our databases we run the script for 8 hours early Sunday morning. We have a few high volume systems that weren't finishing in that time period so we added 1 to 2 hours a day in the early morning.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

These are now open sourced and may no longer be maintained. However, we have been using them with success against SQL 2005 - SQL 2012 with no changes.