Sitecore database lesson 7 - Sitecore SQL Server database Maintenance Plans

You should create maintenance plans for SQL server for fragmented indexes.

If there is high index fragmentation on the database tables. This suggests the index defragmentation script should be run nightly.

Procedure to Check for Fragmented Indexes

To check for percentage of fragmentation on indexes, run the Index Physical Statistics Standard Report against the CMS databases (Core, Master, Web) as follows:

Open SQL server -> Select Core/Master/Web database ---> Right click on database and select Reports ---> Standard Reports ----> Index Physical Statistics -> Locate “Items” table -> and check details of ndxID

Index Physical Statistics

One key value that is provided in the report is the Operation Recommended field. A value Rebuild in Operation Recommended column is an indication that the index is fragmented.

Fragmented index

How to create this plan?

SQL Server Management Studio provides an IDE for the simplification of creating Maintenance Plans. To create a MP for defragmenting the indexes:

1. Launch SQL Server Management Studio.

2. In the Object Explorer expand the Management folder.

3. Right click the Maintenance Plans folder and select New Maintenance Plan.

4. Give the MP a meaningful name such as Defragment CMS Indexes.

5. From the Toolbox drag and drop a Check Database Integrity Task, Rebuild Index Task, Update Statistics Task and place them vertically in the same order.

6. Connect the tasks together by dragging the arrow from one box to the other so they are connected as: Check Database Integrity Task -> Rebuild Index Task -> Update Statistics Task.

Fragmented Indexes Plan

7. Right click the Check Database Integrity Task and select Edit.

Check Database Integrity Task

8. Select the Connection and CMS databases — Core, Master, Web — and click OK.

9. Right click the Rebuild Index Task and select Edit.

Rebuild Index Task

 

10. Select the Connection and CMS databases — Core, Master, Web — and then select the Keep index online while reindexing checkbox — enterprise edition of SQL Server only — and click OK.

11. Right click the Update Statistics Task and select Edit.

Update Statistics Task

12. Select the Connection and CMS databases — Core, Master, Web — and set the Object to Tables and Views, Update All existing statistics, Scan type = Full scan, and click OK.

13. Click the calendar icon next to the Schedule (upper right corner) and set the schedule to run weekly.

Defragment Index Schedule Plan

14. Save.

Tip: – You should create three separate maintenance plan for CORE, MASTER and WEB databases and schedule them on different time.

I hope you like this Sitecore database lesson. Stay tune for more Sitecore database related articles. 

Please leave your comments or share this article if it’s useful for you.