In case you prefer to manually rebuild your database indexes, you can choose the regularity to do that and schedule the procedure whenever your other work in Sitefinity CMS backend will not be impeded. You do the database optimization by running your own custom script via the SQL Server Agent.
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 run one or both of the scripts.
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%:
Back To Top