APM database concern

thestephan

We consistently see 5 -12 sessions waiting to do inserts into table APM_ProcessEvidence_Detail. 

We also notice that this table is the only table with a disable index (IX_APM_ProcessEvidence_Detail_Name_PID) and that its

non-unique index (IX_APM_ProcessEvidence_Detail_ComponentStatusID) shows 80% fragmentation.

I know this app does its own database maintenance routines, so the question is.... Is this normal operating mode for this table?

Petr.Vilem

What version of APM/SAM are you using? Index IX_APM_ProcessEvidence_Detail_Name_PID was disabled in 4.2 SP1 and later removed completely in 5.0 because it caused intermittent performance issues on SQL server.

If you are still on 4.2 using many ProcessEvidence type components (Process Monitor - WMI, Process Monitor - SNMP, Windows Service Monitor) then I recommend upgrading to SAM 5.0.1 as there were some performance improvements in this area (especially for SNMP process monitors) which could help to reduce concurrency on APM_ProcessEvidence_Detail table.

Regarding fragmentation of IX_APM_ProcessEvidence_Detail_ComponentStatusID, did you try rebuilding it manually and check again after several days? I think it should not be highly fragmented as ID (PK) and ComponentStatusID are expected to grow together under typical circumstances... Indexes are not defragmented automatically during daily maintenance because it would cause longer time needed for maintenance.