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 |
Tip: – You should create three separate maintenance plan for
CORE, MASTER and WEB databases and schedule them on different time.
