Overview
In high-traffic situations, act_lgoc_stats_g
database table might be missing interval data and the hourly act_dmsd_stats_h
database table may take longer than usual to reflect data.
Solution
This might happen if you're using MSSQL as the database as it needs some indexes which might not have been configured on the database. Please create the following indexes to improve the performance of the setup:
CREATE INDEX act_dmsd_stats_h1 ON act_dmsd_stats_h (vector_id, itsIntervalStart DESC)
CREATE INDEX act_dmsd_stats_h2 ON act_dmsd_stats_h (itsIntervalStart DESC)
CREATE INDEX act_dimension_2 ON act_dimension_def (deletion_date);
CREATE INDEX act_alert_3 ON act_alert_def (deletion_date, time_occurred)
CREATE INDEX act_alert_dimmem_2 ON act_alert_dimmember (deletion_date);
CREATE INDEX act_bp_2 ON act_bp_def (deletion_date);
CREATE INDEX act_bp_state_2 ON act_bp_state_def (deletion_date);
CREATE INDEX act_bp_trans_2 ON act_bp_transition_def (deletion_date);
CREATE INDEX act_callsite_criteria_2 ON act_callsite_criteria_def (deletion_date);
CREATE INDEX act_cme_2 ON act_custom_metric_entry_def (deletion_date);
CREATE INDEX act_dimmem_2 ON act_dimmember_def (deletion_date);
CREATE INDEX act_dimmem_rel_2 ON act_dimmember_rel (deletion_date);
CREATE INDEX act_dmsd_def_2 ON act_dmsd_def (deletion_date);
CREATE INDEX act_endpoint_2 ON act_endpoint_def (deletion_date);
CREATE INDEX act_flowmap_2 ON act_flowmap_def (deletion_date);
CREATE INDEX act_kbi_2 ON act_kbi_def (deletion_date);
CREATE INDEX act_lgoc_cpname_def2 ON act_lgoc_cpname_def (deletion_date);
CREATE INDEX act_lgoc_def_2 ON act_lgoc_def (deletion_date);
CREATE INDEX act_pub_id_2 ON act_public_id_def (deletion_date);
CREATE INDEX act_server_def_2 ON act_server_def (deletion_date);
CREATE INDEX act_site_2 ON act_callsite_def (deletion_date);
CREATE INDEX act_site_l1_2 ON act_callsite_l1_def (deletion_date);
CREATE INDEX act_site_l2_2 ON act_callsite_l2_def (deletion_date);
CREATE INDEX act_site_l3_2 ON act_callsite_l3_def (deletion_date);
CREATE INDEX act_site_l4_2 ON act_callsite_l4_def (deletion_date);
CREATE INDEX act_subn_def_2 ON act_subn_def (deletion_date);
There are a few more steps you can take if creating the indices doesn't improve performance.
- You can also try partitioning the database.
- Observe the slow/blocking queries, and your SQL client (eg., SQL Server Management Studio) should provide recommended SQL plans to alter the database configuration to improve query performance which you can execute.
Testing
The above steps should improve the performance of the database and no data should be lost.