APM 4.2 Alert Issue

We just upgraded to APM 4.2 last week and we are gettting reports that APM is not sending "reset" alarm messages. Investigating this shows long running "reset" queries. I tracked this down to 3 SQL Funtions. I added necessary index's to help with the query but I believe more should be looked at than index's. Let me know if you have any questions

1. GetComponentMultiValueData

2.GetComponentMultiValueFirstCurrentNumericData

3.apm_GetComponentMultiValueFirstCurrentStringData

Find more posts tagged with

Accepted answers

All comments

sean.martinez

What are the Reset Conditions that you have setup? Are you using Reset when the Conditions are no longer true?

bwicks

Yes - Reset when the Conditions are no longer true

martin.susil

These procedures are related to data from multi-value scripts. How many script components do you have? When you say long running, how long does it really take to execute such SQL query?

bwicks

Yes here is more details - the "reset query" took anywhere between 2-3 minutes. Adding index's reduced to 15 - 30 sec. I think adding index's is usuable now but we are concerned about when we double our component count "we will be increasing our component count" now with 4.2

Here is my "reset" query"

SELECT DISTINCT APM_AlertsAndReportsData.ComponentID AS NetObjectID,

APM_AlertsAndReportsData.ComponentName AS Name FROM APM_AlertsAndReportsData

WHERE NOT ( (APM_AlertsAndReportsData.StatisticData = 0) AND

( (APM_AlertsAndReportsData.ComponentName LIKE '%KSTLMSWB%') OR

(APM_AlertsAndReportsData.ComponentName LIKE '%KSTLMSWOP03%')) )

The issue is with the 2 case statements in "APM_CurrentStatistics"

Here is the "APM_CurrentStatistics" query with a count of componentevidencetype = 3

SELECT count(*) FROM dbo.[APM_CurrentComponentStatus] [ccs]

INNER JOIN dbo.[APM_Component] [c] ON [c].[ID] = [ccs].[ComponentID]

INNER JOIN dbo.[APM_ComponentDefinition] [cd] ON [c].[ComponentType] = [cd].[ComponentType] and cd.ComponentEvidenceType = 3

INNERJ OIN dbo.[APM_Application] [a] ON [c].[ApplicationID] = [a].[ID]

LEFTJOIN dbo.[APM_CurrentApplicationStatus] [cas] ON [a].[ID] = [cas].[ApplicationID]

INNERJOIN dbo.[Nodes] [n] ON [a].[NodeID] = [n].[NodeID]

Below is the result

-----------

3137

(1 row(s) affected)