For developers: Rebuild database indexes

Rebuilding a database index consists in dropping and re-creating the index. This operation removes fragmentation, and reclaims disk space. It compacts the pages based on the specified fill factor setting (if a fill factor is not specified, the default one is used), and reorders the index rows in contiguous pages.

Rebuild your Sitefinity CMS database indexes is an operation that is external from the Sitefinity CMS application, and must be by running your own custom script via the SQL Server Agent. , You can run the optimization scripts manually, or choose the regularity to do that and schedule a procedure.

NOTE: You should rebuild indexes when the site is not under heavy use, since this can result in timeouts and exceptions for your current web site users. Preferably run the script during the night hours or weekends, depending on the business intensive hours. The rebuild times usually should last less than 10 minutes, but depends on the database size. The index rebuild is atomic operation that is not considered a data corruption threat.

When you create or rebuild an index, you can specify a fill factor, which is the amount the data pages in the index that are filled. A fill factor of 100 means that each index page is 100% full.

RECOMMENDATION: To avoid fragmentation of the index each time a new record is inserted in the database and needs to be put between existing records, we recommend to specify a fill factor lower than 100%.

If you run the rebuild scripts on a regular basis and your application is not updated heavily with new data, you can use a fill factor of 90%. This should leave enough empty space on the database index pages for new records until the next rebuild. In addition, not too much empty space on the hard drive will be left unused.

The following sections demonstrate how you get report of the current database fragmentation and then rebuild the indexes.

You can use this report to adjust how often you need to run the indexes rebuild operation, depending on how often and how much your website data is changed (added, updated, deleted).

A couple of points to consider:

• Pay attention to the results for big indexes only that have page count of >100.
• If you have a lot of big indexes with fragmentation of more than 10%, then you need to run the index rebuild. After the first run of the script, you can check again in a week whether fragmentation is high, so that you run the script once again. It is possible that you need to run the script just once in a month.

Run the following script to get statistics and report of the current database fragmentation:

Run the following script to go over all the Sitefinity CMS database indexes and rebuild them with fill factor of 90%: