Slow Performance (sla calculations)

[PedroRamalho]

Hi all,

 

We have SDM r12.9 conventional architecture. Most of our tickets counts SLA between 9AM-6PM. This is our 8x5 workshift:

Mon - Fri { 09:00 - 13:00 14:00 - 18:00 }

In most of our SLAs we have 3 events:

1) Evt Cr PreWarning Not Resolved

2) Evt Cr Warning Not Resolved

3) Evt Cr Violation Not Resolved

 

Event 1 is triggered at 50% of SLA Objective Time and changes a call_req variable from 0 to 1.

Event 2 is triggered at 80% of SLA Objective Time and changes 2 variables from call_req and sends a notification to ticket Assignee.

Event 3 is triggered at 100% of SLA Objective Time and changes 2 variables from call_req and sends a notification to ticket Assignee.

Since we support a retail business (stores closes at 11PM) with several equipments a huge part of our incidents are opened after 6PM which makes the SLA calculation from all incidents happens at the same time.

I already created a new workshift from 9:30-13:30 14:30-18:30 for monitoring tickets (CA SOI integration) so calculations does not occur at the same time as incidents opened by users.

However we still have several invocations at the same time.

 

This is a query that lists the number of events triggered at the same time (it only lists when there is more than 50):

select dbo.z_cformat(anima.a_time) 'Event Time',count(anima.a_time) 'Event Count'

from anima with(nolock)

left join att_evt ae with(nolock) on ae.persid = anima.t_persid

where ae.group_name = 'SLA' --anima.a_time < 1467940000 and --or a_org = 1465909200

GROUP BY anima.a_time

having (count(anima.a_time)>50)

order by anima.a_time

 

Note: z_cformat is a function that converts unix time on "human readable" time

 

This is our output:

Event Time	Event Count
2016-06-29 18:00:00.000	474
2016-06-29 18:30:00.000	271
2016-06-30 13:00:00.000	179
2016-06-30 18:00:00.000	269
2016-07-01 13:00:00.000	89
2016-07-01 18:00:00.000	253
2016-07-04 14:48:00.000	56
2016-07-04 18:00:00.000	92
2016-07-05 18:00:00.000	120

 

I had an issue opened on Support for more than 2 years and did all possible fine tuning. But we still have performance issues at that particular times (no one can work on Service Desk during 30seconds/1minute).

 

Anyone with the same issue? Any idea to solve this?

Thank you in advance.

[PedroRamalho]

At 6PM (50 minutes from now) we will have this:

  

Row Labels	Count of Incident #
Evt Cr PreWarning Not Resolved	56
Evt Cr Violation Not Resolved	362
Evt Cr Warning Not Resolved	40
Grand Total	458

[Pier Olivier Tremblay]

The only thing that i can think of is creating a dedicated domsrvr for those animations. Did you try it?

[PedroRamalho]

pier-olivier.tremblay I already have a specific domsrvr for Animator.

 

KarenMatoke any guess?

[Sandra_Antunes]

Other option is to set a dedicated dbagent for this. Has this been done?

[PedroRamalho]

Olá Sandra_Antunes

 

Yes, we have dedicated dbagents for this:

@NX_VIRTDB_AGENT1=Call_Req

@NX_VIRTDB_AGENT2=Act_Log

@NX_VIRTDB_AGENT3=Change_Request

@NX_VIRTDB_AGENT4=Change_Act_Log

@NX_VIRTDB_AGENT5=Issue

@NX_VIRTDB_AGENT6=Issue_Act_Log

@NX_VIRTDB_AGENT7=Audit_Log, event_log, session_log

@NX_VIRTDB_AGENT8=EBR_FULLTEXT, EBR_FULLTEXT_ADM, EBR_KS_INDEXING_QUEUE

@NX_VIRTDB_AGENT9=CREATE_INDEX

@NX_VIRTDB_AGENT10=Kpi_Ticket_Data, Kpi_Data

@NX_VIRTDB_AGENT11=BU_TRANS

@NX_VIRTDB_AGENT12=Animator

@NX_VIRTDB_AGENT13=Attached_Events

[Sandra_Antunes]

Honestly, I don't see much else that could be done without a change in the SDM architecture - allowing "load balancing" for the Animator could be an idea?

 

Or change a bit the way the business is set, with a slight difference in the SLA timings - this is just an idea, not sure if this is really possible.

 

Because if you have the number of events running at the same time as you say, it will really make SDM busy to process all those events.

 

Sandra

[PedroRamalho]

Sandra_Antunes how can we load balancing the Animator?

 

With the newly created workshift - 30 min later on each event - the slow performance period is lower than before. However I'm still looking for a better solution.

 

Could it be a problem regarding database I/O? If so what can I do to increase the performance?

[Sandra_Antunes]

Pedro, the load balancing currently doesn't exist. Could be an Idea in case we really identify the problem is with SDM.

 

Regarding the DB I/O, it is necessary to monitor it. You would need to involve the DBA and set the proper monitoring to verify what is occurring on the DB side by the time you see the problem.

 

Sandra

[Pier Olivier Tremblay]

I'll be very surprised if this is an issue regarding your database.

 

 

Most of the time, my SQL Server is waiting for SDM to consume the data.

 

 

If you are on SQL Server, you can right-click your instance and hit Monitor.

Let it load its data and take a look at your Processes (task stat) and Data File I/O.

 

 

You may also check if the indexes on the anima table are clean. Ask your DBA to run a maintenance against them.

[PedroRamalho]

Sandra_Antunes and pier-olivier.tremblay we have configured daily maintenance DB jobs on which we delete several historical data (not_log, attached_events, etc) and indexes are rebuilded so I believe the problem isn't related with DB but with the number of changes and locks that run at that particular times.

[System]

Hi PedroRamalho - Were you able to work on this with CA Support? If so please share the resolution. Thanks!

[PedroRamalho]

Hi Chris_Hackett,

 

Yes, I was. But CA Support did not provide a solution. A lot of fine tuning was done but then I was advised to contact CA Services 2 or 3 years after opening the issue.

[Sandra_Antunes]

if all the possible environmental effects have been verified and none are really affecting the SDM behavior, then the best way I see this could be solved is having a real customer's environment (DB + customizations) with CA Support so they can reproduce the problem and engage the Sustaining team to fix the problem.

 

Sandra