Optimizing Sitecore Databases : Part 1 (Using Sitecore Content Editor)

Applies to: Sitecore 6.X, IIS 7 and Higher, MSSQL 2005 and Higher

Solution Tested On:

CMS Version: Sitecore 6.2(rev 10.01.04)

Database Server: MSSQL 2005 SP2, 8 core processor, 8GB RAM

Database Test: Master (all the performance test were evaluated on master database)

Browser: IE9

Introduction:

This post deals with optimizing sitecore databases by applying a series of maintenance tasks which helps in keeping the Sitecore Database perform to the best of its abilities.

The maintenance list is further broken down into two parts

PART 1: Using Sitecore Content Editor

• Rebuild the Links Database
• Rebuild the Search index
• Clean Up Database
• Check recycle bin in sitecore and delete unnecessary files

PART 2: Using SQL Server Management Studio

• Shrink databases and Logs
• Check Database for fragmented indexes and rebuild the indexes
• Database Consistency Check
• Set auto close and auto shrink properties to false

In this post we will be only discussing PART 1 which is using sitecore content editor.

____________________________________________________________________________________________________________________________________________________________________________

Rebuild Links Database

Overview:

Sitecore maintains a table named Links in the database specified in the LinkDatabase section of web.config.  This table provides a logical links database for all other databases. One of the important features of the Links Database is to enables fast and efficient retrieval of item relations. Hence it is a nice idea to rebuild the link database periodically as a part of a maintenance plan.

How to:

1.)    Login into sitecore as a admin user (Open Desktop)

2.)    Click Sitecore Start Button -> Control Panel -> Database

3.)    Click on Rebuild the Link Database, click next and select the databases you want to rebuild (I generally check all the 3 databases i.e. Master, Web and Core) and then click next to finish the task.

Tips:

There are also ways to programmatically invoke the rebuild links database process, one of the common approach is to hook this process at the end of publish operation.

 ________________________________________________________________________________________________________________________________________________________ 

Rebuild Search Index

Note: This task applies to you only if you are using search indexes in your sitecore solution. If you are not using search indexes then it might be in your best interest to change the Indexing.UpdateInterval value in web.config to “00:00:00” for optimal performance

Overview:

Search Indexes are defined in the <indexes> section of the web.config file. Search Indexes are updated right after any entry is added to the History table of the appropriate Database. Any modification made to sitecore items causes an addition of a record to (dbo.History) History table. The History table is empty when an index for a database is created for the first time, so it is important to rebuild the indexes before letting the system to update indexes on its own.

The dbo.Properties table has a record with the key “IndexingProvider_LastUpdate”. This record defines the date, which is used as the start point for item indexing (all items which were modified before this date will not be updated by the UpdateIndexAsync job; this is why it is important to rebuild the indexes after they have been defined for a database).

Important Note :

By default Sitecore won't update the search indexes for web database, to enable this functionality please add the following snippet of code to the web <database> section in web.config

 
<Engines.HistoryEngine.Storage>
    <obj type="Sitecore.Data.$(database).$(database)HistoryStorage, Sitecore.Kernel">
        <param connectionStringName="$(id)" />
        <EntryLifeTime>30.00:00:00</EntryLifeTime>
    </obj>
</Engines.HistoryEngine.Storage>
 

How to:

1.)    Login into sitecore as a admin user (Open Desktop)

2.)    Click Sitecore Start Button -> Control Panel-> Database

3.)    Click on Rebuild the Search Index, click next and select the databases you want to rebuild (I generally check all the 3 databases i.e. Master, Web and Core) and then click next to finish the task.

Tips:

There are also ways to programmatically invoke the rebuild search index process, one of the common approach is to hook this process at the end of publish operation.

____________________________________________________________________________________________________________________________________________________________________________

Clean Up Database

Overview:

In any sitecore application, as time progresses, the data being stored in the databases keeps on growing. It is always nice idea to get rid of artifact data from the databases which is no longer needed. The clean-up database functionality in sitecore helps you to remove any artifact data and rebuild / reorganize the tables as needed.

How to:

1.)    Login into sitecore as a admin user (Open Desktop)

2.)    Click Sitecore Start Button -> Control Panel ->Database

3.)    Click on Clean up Database, click next and select the databases you want to clean (I generally check all the 3 databases i.e. Master, Web and Core) and then click next to finish the task.

Tips:

Clean up database functionality from sitecore is very similar to the shrink database task. Based on my experience we get better results after performing both the tasks (clean up database and then shrink database operation).

A shrink operation is most effective after an operation that creates lots of unused space such as database cleanup operation in sitecore.

____________________________________________________________________________________________________________________________________________________________________________

Check Recycle Bin in sitecore and delete unnecessary files

Overview:

In Sitecore content editor, the recycle bin is a very important feature which allows us to restore any item which might have been deleted by error, but over a period of time as the application grows it becomes important to get rid of items in sitecore which are no longer needed. This task will enable us to remove unnecessary data from sitecore and keep the databases healthy

How to:

1.)    Login into sitecore as a admin user (Open Desktop)

2.)    Click Sitecore Start Button -> Recycle Bin

3.)    If you want to empty the recycle bin (* make sure that all the items are no longer needed) click the empty icon in the ribbon, finally click OK to complete.

4.)    Also, you could select the items individually (you can select multiple items using CTRL + Click) and then click delete icon on the ribbon, finally click OK to complete.

Tips:

You can also programmatically delete items in sitecore using item.Delete() function. Also you can opt to send the items to recycle bin using item.recycle() function.

Note:

Sitecore also allows us to completely disable the Recycle Bin functionality. In order to disable it simply open the web.config file and set the value of RecycleBinActive value to false.

<setting name="RecycleBinActive" value="false" />

 ____________________________________________________________________________________________________________________________________________________________________________

SUMMARY

In my opinion carrying out this maintenance task monthly will make sure that the database is performing well. The part 1 is written keeping the Sitecore Content Managers in mind who are admin users but mostly do not have access to the database servers. My next post will be part 2 aimed at Sitecore database admins or users who have access to the sitecore database server.

HAPPY MAINTENANCE