Set up Reindexing for Database Maintenance

If performance degradation occurs, it is commonly noted in Booking and Transaction tables due to their heavy use.

IMPORTANT   Best Practice Software recommend that database reindexing be run outside of business hours to limit inconvenience to the clinic due to performance degradation

1. On the SQL Server system, locate and open SQL Server Management Studio (SSMS), the Connect to Server screen will appear.
2. Fill out the required credentials to access the SQL Server, followed by Connect.
3. Once logged in successfully, locate the Object Explorer, commonly found on the left side of SSMS.
4. Under the Object explorer select Management > Maintenance Plans, check the current maintenance plans to ensure no conflicting plans exist.
5. Right-click the Maintenance Plans folder, select Maintenance Plan Wizard.
6. Once the wizard has appeared, select Next to view the Select Plan Properties screen.
7. Give the plan an appropriate name, for example SQL Reindex, at the base of the windows under Schedule select Change.

The New Job Schedule window will appear.

8. Under the New Job Schedule, adjust the Frequency to Monthly followed by a suitable time timeframe.

This can be scheduled to be more frequent depending on the size of the database and how many users there are.

9. Once a suitable Frequency has been set, adjust the Daily frequency to a suitable start time and click Ok followed by Next to proceed.

The Select Maintenance Tasks screen will appear.

10. Inside the Select Maintenance Tasks screen, select Rebuild Index and Update Statistics under maintenance tasks, ClickNext to proceed.

The Select Maintenance Task Order screen will appear.

11. Adjust the order in which the tasks will run to order Update Statistics as the first task. ClickNext to proceed.

The Define Rebuild Index Task will appear.

12. Under Databases, select the applicable databases for the task to be run against. Click Next to confirm.

The Define Update Statistics Task window will appear, again, specify the databases.

13. Once the applicable databases have been confirmed, click Next.

The Select Report Options screen will appear.

14. Inside the Select Report Options screen, select the methods of reporting.

NOTE  It is suggested at least one Report is selected to ensure a log of activity is kept.

Once confirmed, click Next and the review screen will appear.

15. Review the actions to be taken under the maintenance task and click Finish to confirm the Maintenance Plan.

The Maintenance Plan will run upon completion. Confirm all tasks complete successfully to confirm the plan is correct.

 

Last modified: July 2020