Performance issues - posting occasionally takes 50 minutes

msuters

msuters

Active Member
We occasionally see some performance issues in JDE, and I can't figure out how to track down the cause. The main symptom we see is that a posting job sits processing for about 50 minutes. This holds up all the other post jobs in the queue (which are normally very quick, less than 30 seconds), and it seems to also affect the performance of some other jobs in other queues, and response of the system in general. I think it is the database server that slows the system, as the symptoms of slowness happen both to batch jobs and to interactive sessions on several clients.

While things are running slowly, the CPU on the database server doesn't seem to be working excessively. It's fairly active, but not consistently above 75%. Other batch jobs (in different queues) run through OK, so it's not the entire system that is hanging up, and there doesn't seem to be anything unusual about the activity on the Citrix clients that the clients use.

When the posting job does finally complete, everything looks like it completed successfully, it just took a long time. Sometimes the posts are very small, affecting only one business unit and two objects.

Some of our application support people have suggested that perhaps the batch file that takes 50 minutes to post is only a symptom, and is itself being held up by something that a person is doing interactively, maybe a budget upload.

Some of the issues are that we cannot tell what interactive users are doing in JDE. We don't run with logging on normally, for performance reasons. I have tried using SQL Profiler to see what is happening in the database, but that seems to mostly show me lots of cursor messages, which I don't understand. Also, the number of events is overwhelming, as people continue to use the application.

Another suggestion that has been made is that perhaps writing to the F0902 table is being prevented. I would have expected each update JDE did would lock a small number of records, only for the duration of the update, rather than doing something that could impact the whole system for a period of time. And that is what seems to work most of the time.

My questions are:
1. Has anybody seen (and preferably solved) a similar problem to this?
2. Is there anything I can do to troubleshoot these events?
3. Can you suggest anything that might mitigate the impact, if I can't resolve the root cause?

Thanks,
Mark
 
Sounds like a SQL Server blocking issue.


Next time it happens run this script:

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.sid = l.sid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )


This will tell you the root blocking process and will get you on your way to identifying what is doing the blocking.



[ QUOTE ]
We occasionally see some performance issues in JDE, and I can't figure out how to track down the cause. The main symptom we see is that a posting job sits processing for about 50 minutes. This holds up all the other post jobs in the queue (which are normally very quick, less than 30 seconds), and it seems to also affect the performance of some other jobs in other queues, and response of the system in general. I think it is the database server that slows the system, as the symptoms of slowness happen both to batch jobs and to interactive sessions on several clients.

While things are running slowly, the CPU on the database server doesn't seem to be working excessively. It's fairly active, but not consistently above 75%. Other batch jobs (in different queues) run through OK, so it's not the entire system that is hanging up, and there doesn't seem to be anything unusual about the activity on the Citrix clients that the clients use.

When the posting job does finally complete, everything looks like it completed successfully, it just took a long time. Sometimes the posts are very small, affecting only one business unit and two objects.

Some of our application support people have suggested that perhaps the batch file that takes 50 minutes to post is only a symptom, and is itself being held up by something that a person is doing interactively, maybe a budget upload.

Some of the issues are that we cannot tell what interactive users are doing in JDE. We don't run with logging on normally, for performance reasons. I have tried using SQL Profiler to see what is happening in the database, but that seems to mostly show me lots of cursor messages, which I don't understand. Also, the number of events is overwhelming, as people continue to use the application.

Another suggestion that has been made is that perhaps writing to the F0902 table is being prevented. I would have expected each update JDE did would lock a small number of records, only for the duration of the update, rather than doing something that could impact the whole system for a period of time. And that is what seems to work most of the time.

My questions are:
1. Has anybody seen (and preferably solved) a similar problem to this?
2. Is there anything I can do to troubleshoot these events?
3. Can you suggest anything that might mitigate the impact, if I can't resolve the root cause?

Thanks,
Mark

[/ QUOTE ]
 
Thanks Brother,

We'll try identifying the blocking process the next time we see this issue occur.

Mark
 
Back
Top