Planning and Scheduling Maintenance

Create and follow a regular maintenance plan. Performing regular, ongoing maintenance is crucial to ensuring optimal system performance. At a minimum, a maintenance plan should include the following:

•A schedule for deleting records and tables. See Deleting Inactive Tables and Records.

•Running a tuning advisor tool to determine index tuning actions and then performing those actions. See Database Tuning.

•Scanning for duplicate indexes and then removing the duplicates. See Checking for Duplicate Indexes.

•Configuring database auto-growth. See Setting the Value for the Database Auto-Growth Parameter.

Depending on your system requirements, it is likely that you will perform additional activities as part of a maintenance plan.

The following is an example maintenance plan:

Month	Maintenance Task
January	•Evaluate the current archival plan and modify it as necessary. •Record performance metrics to establish a baseline. •Run the Microsoft SQL tuning advisor to determine recommended index changes. •Apply recommended index changes. •Scan for and delete duplicate indexes. •Check for license renewal requirements.
February	•Evaluate the current archival plan and modify it as necessary. •Troubleshoot errors reported in the event log and message queuing.
March	•Evaluate the current archival plan and modify it as necessary. •Audit log level and location settings. •Review database auto-growth and if necessary reset it to an appropriate static value.
April	•Evaluate the current archival plan and modify it as necessary. •Run the Microsoft SQL tuning advisor to determine recommended index changes. •Apply recommended index changes. •Scan for and delete duplicate indexes.
May	•Evaluate the current archival plan and modify it as necessary. •Troubleshoot errors reported in the event log and message queuing.
June	•Evaluate the current archival plan and modify it as necessary. •Record performance metrics and compare them with the recorded baseline.
July	•Evaluate the current archival plan and modify it as necessary. •Run the Microsoft SQL tuning advisor to determine recommended index changes. •Apply recommended index changes. •Scan for and delete duplicate indexes.
August	•Evaluate the current archival plan and modify it as necessary. •Troubleshoot errors reported in the event log and message queuing.
September	•Evaluate the current archival plan and modify it as necessary. •Review database auto-growth and if necessary reset it to an appropriate static value.
October	•Evaluate the current archival plan and modify it as necessary. •Run the Microsoft SQL tuning advisor to determine recommended index changes. •Apply recommended index changes. •Scan for and delete duplicate indexes.
November	•Evaluate the current archival plan and modify it as necessary. •Troubleshoot errors reported in the event log and message queuing.
December	•Evaluate the current archival plan and modify it as necessary. •Record performance metrics and compare them with the recorded baseline. •Update infrastructure design plans and other documents. •Update system growth expectations.

About Reviewing, Closing, and Deleting Records

It is not uncommon for systems to have hundreds of thousands of active incident records that no longer need to be open and active. For example, if an incident has not been modified for several months, it can probably be closed.

Dashboard parts and saved searches may query these inactive records daily. Closing records that are older than a specified age (for example, three months) prevents them from being queried by dashboard parts and saved searches, which can significantly improve dashboard and search loading performance. Deleting closed records can also reduce the load on database server infrastructure. Additionally, deleting workflow history and instance tables prevents the workflow engine from having to query them.

We highly recommend that you implement a regularly occurring process to identify records that can be closed and deleted.

You can use service level agreements to automatically trigger a notification when an active record has been open for a specified amount of time. To do this, assign a default service level agreement to all services that do not have an active one. In the service level agreement, set the escalation to notify the owner when an active incident record has been open and active for a certain number of days.

Deleting closed and inactive records can reduce the load on database sever infrastructure. See Deleting Inactive Tables and Records.