The Agent Guidance database utilises a number of indexes to ensure performance of the database. These indexes become fragmented over time, and this can lead to reduced performance. We advise all indexes in the Agent Guidance database are regularly maintained through standard techniques, but be aware that typically system performance will be impacted during maintenance of these indexes. Typically a weekly maintenance cycle is appropriate.

 

An example SQL Stored Procedure (SP) that will repair and rebuild indexes that require maintenance is included within the Agent Guidance database. Use of this SP is optional and is not recommended over other SQL index maintenance routines you may have in place.

 

The SP reviews the state of all Agent Guidance SQL indexes and then takes corrective action only where required. Comments included at the top of the SP indicate the available settings and their usage, which should be reviewed before running. It also supports setting approximate time limits to avoid impacting live usage hours.

 

The SP will record its actions in to a logging table within the Agent Guidance database, and removes its older log entries automatically. This table's entries can be viewed via SSMS (SQL Server Management Studio) if required.

 

The SP is found in the Agent Guidance database at Programmability > Stored Procedures > SYS4_REINDEX

 

The logging table is found in the Agent Guidance database at Tables > tbl_IndexLogging

 

 

The Agent Guidance database will accumulate data over time as described in the Database Layer section. As an example, for systems with a medium to larger Workflow throughput of approximately 100 users the typical database growth is up to 5 GB per year. The yearly growth size can vary wildly, for example between 1 GB for low-throughput systems and 10 GB for high-throughput systems.

 

Typically we recommend that databases do not grow above 30 GB, as this can begin to impact on system performance. However, this does depend on the exact hardware and usage scenario.

 

The majority of this data is Workflow session data that is stored to facilitate the re-running of Workflow sessions. This data can be removed on a periodic basis via scheduled or manually-run Microsoft SQL Server queries, but this process must be done with care as it will impact the performance of the Agent Guidance System while being carried out.