This Article discusses some best practices for Insight and Analytics databases. Information in this Article may be updated periodically.
Insight databases are comprised of the main Admin database and at least two project-related databases: Data and Meta. Optionally, a third database maybe used to stage incoming data from your external sources, Staging.
An experienced database administrator (DBA) should monitor these databases using third-party management tools that come with the RDBMS system (SQL Server Management Studio, Oracle, etc). This is the best way to ensure that your databases are running optimally. If there are long running queries, deadlocks and / or blocking, these will show in the monitoring tools when configured to capture this data. Sometimes it may be necessary to add or modify indexes and, if done, should be documented to ensure that they can be applied if product or project upgrades are done.
All projects have records, metrics and other components. These components have a property that allows rebuilding of indexes located in the Property Panel ("Rebuild indexes"). Sometimes it may be necessary to enable this option (if disabled) or disable this option (if enabled) if your DBA discovers that the rebuild, or lack of rebuilding, is causing performance issues.
Another good practice is to periodically review your business needs and determine how much data needs to be retained for historical reporting. Keep your Insight Execution Plans updated accordingly by setting the proper length of time to retain data. More information can be found here:
As always, precautions should be taken to back up all data before making any changes.