Setting up SQL Server Index Maintenance Plans

Index Maintenance Plan Set Up

Note: Index maintenance is ultimately the responsibility of the Customer's Sys Admin for the DEACOM SQL Server. MCS clients have their database’s indexes maintained by DEACOM’s MCS Support team. Also, there are more complex and efficient methods to maintain indexes than the below, but this is the simplest to set up.

  1. Open SSMS and connect to the DEACOM database server.
  2. Expand Management > Maintenance Plans.
  3. Confirm no maintenance plan is currently set relating to Indexes.
  4. Right click Maintenance Plans > New Maintenance Plan.
  5. Give the new plan a name (ex. IndexMaintenance)
  6. In Subplan_1, click the calendar next to the Schedule column.
  7. Under Frequency and Daily Frequency, this should be run Weekly and when users are not using the system
  8. If DEACOM is in use 24/7, pick the time with lowest traffic. You can use the query attachment at the bottom of the page to track when the users are creating/modifying data the most per day and hour in the last 90 days.
  9. Once the above run-time is decided, click OK.
  10. Go to View > Toolbox to open the Toolbox if it is not already open.
  11. Drag the Rebuild Index Task into the Grey box below the Subplan area’s white box.
  12. Double click the new Rebuild Index Task box.
  13. Click the Database(s): drop down and choose the databases you want affected.
  14. If databases besides DEACOM are on the same server, choose ‘These databases:’ and check the boxes for all your DEACOM databases.
  15. If only DEACOM databases are stored on the SQL server, choose ‘All user databases (excluding master, model, msdb, tempdb)
  16. Click OK
  17. The options below Connection, Databases, Object, and Selection should now be available to change.
  18. If the decided run time is during downtime, leave as is and click OK.
  19. If the decided run time is NOT during downtime, check the box for Keep index online.
  20. You will have to decide if you want to rebuild the indexes offline that don’t support online index rebuilds. At the time of writing, the below indexes do not support online rebuilds:
  21. PK_dxdflt - belongs to a table with only one line.
  22. PK_dmpict – belongs to the Docs databases.
  23. PK_dmprtarchive – belongs to the Docs databases.
  24. Keeping indexes online while rebuilding will take more time and will suck up SQL Server’s resources
  25. Under ‘Optimize index only if:’, the default settings are sufficient and should match the below:
  26. ‘Fragmentation >’ – 20%
  27. ‘Page Count >’ - 10
  28. 15. Click OK once complete and Save (CTRL + S) the new maintenance plan.
  29. User Data Creation/Modification Tracking Query

Click to download MS Word file