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?
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.
Thanks for the info.
We are still using APM 4.2.0 SP1 so I will upgrade to 5 first and check the tables again.
Regarding the fragmentation we haven't tried rebuilding it yet.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.