Feedback
Did this article resolve your question/issue?

   

Article

Sitefinity Database maintenance

« Go Back

Information

 
TitleSitefinity Database maintenance
URL NameSitefinity-Database-maintenance
Article Number000188288
EnvironmentProduct: Sitefinity
Version: 8.x, 9.x, 10.x, 11.x, 12.x
OS: All supported OS versions
Database: All supported Microsoft SQL Server versions
Question/Problem Description
How to rebuild any missing database table indexes in SQL Server to speedup database query execution?
How to rebuild database indexes manually?
Steps to Reproduce
Clarifying Information

A big problem is fragmented database space – resulting in slow inserts of new data in the database and also slow read times during page publishing. This is mostly due to the fact that Sitefinity is not using monotonically increasing primary keys, but GUIDs.

Error Message
Defect/Enhancement Number
Cause

CRUD operations in the database naturally cause SQL Server database tables to get fragmented.

Resolution

The solution to this problem is the regular rebuilding of your database indexes. This is valid especially for the clustered primary key indexes which define the physical order of the database records.

We recommend rebuilding of the indexes with Fill factor lower than 100%, which will help so when a new record is inserted and needs to be put between existing records it does not cause fragmentation of the index.  If rebuild scripts are ran on regular basis and the application is not updated heavily with new data, use a fill factor of 90%. This should leave enough empty space on the database index pages for new records until the next rebuild, plus will not take too much empty space on the hard drive.

The rebuilding of the indexes must be executed when the site is not used, the database will become unresponsive while the indexes are rebuilt.

Preferably run the script during the night hours or weekends, depending on the business intensive hours. The rebuild times usually 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.

Below are two scripts to get statistic on fragmented indexes and to rebuild them.

 

Refer to Sitefinity documentation in the Notes section.

Workaround
Notes

References to Other Documentation:
Sitefinity Documentation, Rebuild database indexes,  https://www.progress.com/documentation/sitefinity-cms/for-developers-rebuild-database-indexes-manually

SQL Server Performance, How Using the Windows 2000 Encrypted File System to Secure SQL Server Databases and Backups Affects SQL Server's Performance, https://www.sql-server-performance.com/w2k-filesystem-affects-performance/

 

Last Modified Date3/8/2021 7:31 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.