Sql-server – Is a weekly rebuild of indexes a good idea?

sql serversql server 2014

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!

Best Answer

  • 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 enter image description here

    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.