Rebuilding a database index consists in droping 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:
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%:
Sign up for our free beginner training. Boost your credentials through advanced courses and certification. Register for Sitefinity training and certification.
To submit feedback, please update your cookie settings and allow the usage of Functional cookies.
Your feedback about this content is important