Slow performance - Starting point

adeel

VIP Member
Hello there

Our users experience many time in a day that the system get slow, e.g when they are in the application and click find, it takes long time to bring the data. Also when click application to start, it says "opening application".

I dont know where to start debugging as this happen not so often and i can not replicate the problem.

We are on VMware.

Please advice.

AD
 
When it is slow try to gather a debug log from one of the users, both JAS and Call Object kernel, and examine them to see where the biggest gaps in time are. For example, if you see a fairly large gap in time when a SQL statement is being run then chances are you need to do some database tuning of some kind.
 
That is the point, users dont get often this performance issue, only once in a while and that is the challenge to replicate or debug the issue. System just waiting and stuck in processing.
Note: The solution from oracle about stuck in processing is already implemented in IEs.

Thanks
AD

Thanks
AD
 
Did you see any zombies? How long was your system up and running?
Our system slows when zombies are created, or when the server was up for more than say 30 days.
 
There are 50 zombies, we do restart our server every 2 weeks.
Is there a way to delete those without effecting real users?
I believe that causing the slowness.

Thanks
AD
 
Adeel,

Zombies are 'dead' jobs to JDE, it will not dispatch any more work to them. Cleaning them up is a good idea, but unless the jobs are actually live and consuming cpu/memory, cleaning them up provides minimal benefit.

However the jde logs for the zombies may point you in the right direction. Check them for what caused the zombie, there is often a call stack associated with each thread. Check the Bxxyyyy and Nxxyyy objects for ESUs. Also check all jas logs for 'virtual rollbacks'. These are caused when a BSFN takes longer than the timeout to respond.

By doing this I was able to reduce our unexpected delays and errors significantly.

Tom
 
Hi Adeel, just my opinion:
you need to isolate the layer: database, eneterprise, jas.

So first start to look at the db when your system start to go slow.
Then go to eneterprise (try to run some ube), and then go to jas (look at memory usage, cpu, etc...).

Regards

Bruno Condemi
 
Thanks for the suggestions. Atleast this gives me some starting point.
It would be neat if this can be track through the server manager.

Thanks
Ad
 
Just another hit; if you are on oracle db be sure that do not reach the maximum number of connection; in this case you will find some errors in jas.log (inspect from ServerManager).
 
[ QUOTE ]
Hello there

Our users experience many time in a day that the system get slow, e.g when they are in the application and click find, it takes long time to bring the data. Also when click application to start, it says "opening application".

I dont know where to start debugging as this happen not so often and i can not replicate the problem.

We are on VMware.

Please advice.

AD

[/ QUOTE ]

Step 1 - Analyze SQL Server index fragmentation using the code in this post:

http://jeffstevenson.karamazovgroup.com/2008/09/determine-index-fragmentation-in-all.html

You are looking for Avg. Fragmentation % above 3-5 percent on any table larger than 100 or so pages. You can uncomment "--and [Page Count] > 100" by removing the two dashes to show only those tables larger than 100 pages.


Step 2 - Perform index maintenance (Rebuild/Reorganize) on those (or all) indexes.

Note that an index rebuild is a *LOGGED* operation and may result in very large transaction logs.
Prior to executing the command it is advisable to ensure that sufficient space exists
in the volume on which the transaction logs are stored (anywhere from 1G to possibly
60G or more, depending on database size, number of rows and other factors). The
largest transaction log growth will occur on databases using the FULL recovery model.

If this is the first full reindex of a database, the transaction log growth will be
quite large. Subsequent reindexing will not cause as large a growth but may cause
growth nonetheless (assuming that the transaction log files are not being shrunk…which
they should not be).

If transaction log backups and the FULL recovery model are being used in conjunction
(as they should be), the first transaction log backup following the index rebuild will
also be abnormally large. As above, it is advisable to ensure sufficient space exists
in the volume on which the transaction log backups are stored.


Step 3 - Profit!


Once you have taken care of this low-hanging fruit you can work on other possibilities. Since you mention that apps are slow to open there is a distinct possibility that the users' clients are not up to par or that the JAS server is not sufficiently fast or that you have too many users per JVM.

Keep us posted.
 
Very interesting thread. Thanks all for your input. We have the same issue and would discuss in our team about what you presented. Thanks,
 
Thanks everyone for the feedback. This gives me alot of feedback and the starting point. I did not apply ESU for the finance module since 3 years and believe that is the issue as finance users always complain about performance. I believe indexing is the main issue on tables. But still wondering users should encounter the performance on daily basis not occassionly.

Thanks
AD
 
Hi Jeff

Looks like we have a real problem look at the report attach, i generated through your blog and i have tons of tables avg. frag 100%,

I think i have to recreate the index?

Thanks
AD
 

Attachments

  • 180599-Capture.PNG
    180599-Capture.PNG
    84.5 KB · Views: 99
[ QUOTE ]
Hi Jeff

Looks like we have a real problem look at the report attach, i generated through your blog and i have tons of tables avg. frag 100%,

I think i have to recreate the index?

Thanks
AD

[/ QUOTE ]


Yes, you have a real problem with index fragmentation. I imagine that the performance of the system is terrible with those kind of numbers. You can either rebuild all indexes in the database or you can rebuild indexes based on criteria (only rebuild tables that are above a certain fragmentation %). Since it appears that most of your indexes are badly fragmented you will have to rebuild all of them.


Note that an index rebuild is a *LOGGED* operation and may result in very large transaction logs.
Prior to executing the command it is advisable to ensure that sufficient space exists
in the volume on which the transaction logs are stored (anywhere from 1G to possibly
60G or more, depending on database size, number of rows and other factors). The
largest transaction log growth will occur on databases using the FULL recovery model.

If this is the first full reindex of a database, the transaction log growth will be
quite large. Subsequent reindexing will not cause as large a growth but may cause
growth nonetheless (assuming that the transaction log files are not being shrunk…which
they should not be).

If transaction log backups and the FULL recovery model are being used in conjunction
(as they should be), the first transaction log backup following the dbcc reindex will
also be abnormally large. As above, it is advisable to ensure sufficient space exists
in the volume on which the transaction log backups are stored.
 
Thanks alot Jeff, Your scripts were great and the blog was very knowledgeable.

I plan to do individual tables on weekend that way i will track transaction log growth, as we dont have too much space available.
Thanks
AD
 
Jeff,
Just wondering how to verify the performance after the reindex? I just through SQL statement to count(*) from f0911 which result in 5 sec, i expected after the reindexing is done i assume result will be in less seconds? Or this is not the good way to measure?

Thanks
Ad
 
Hi Adeel, A few months ago we had same problems too, we applied reindex, rebuild statistics, add other JAS server, work prioritization, but same the situation don't improved.

During our monitoring we found that's our storages hardware had impacted over our Oracle DB.

We repair our storage issue, and changing our Database server hardware too. Now, from 2 month ago without problems.

Maybe your case, is different, but please verify your storage system (local or remote), specially I/O ratios, cache, is very important monitoring that behavior of your database are normally. Adicionally verify your JAS server and E1 Server.


In our case, we sensed when use -specially- AR (invoice charge) and SOP.


I Hope helped you with this experience.
 
XaviGray
Very much appreciated, That is exactly i will go that route once we found no difference in the improvements.
Here is my steps:

1. IE7/8 settings (already good for users).
2. Reindexing as Jeff suggested (This weekend).
3. Storage/hardward you suggested (May VMware not handling).

Thanks
AD
 
[ QUOTE ]
Jeff,
Just wondering how to verify the performance after the reindex? I just through SQL statement to count(*) from f0911 which result in 5 sec, i expected after the reindexing is done i assume result will be in less seconds? Or this is not the good way to measure?

Thanks
Ad

[/ QUOTE ]

Doing a select count does not utilize an index, it simply does a full scan so you will not see an improvement. The way to measure the performance impact after an index rebuild is to execute a query that utilizes an index. The easiest way to determine if a query uses an index is to examine an index's columns and design a query that uses those columns. The best way is to run a UBE that you know uses a certain table. Rebuilding an index generally has a large impact on long-running UBE's.

If you are going to selectively rebuild indexes due to space constraints you can use this post to determine which tables are most utilized: http://jeffstevenson.karamazovgroup.com/2011/10/sql-server-most-utilized-tables.html.

You really, really need to get all of your indexes rebuilt. The kind of fragmentation you are seeing is so bad that I cannot imagine what is causing it. Can you check your Fill Factors and report back to me?


Check Fill Factor of indexes in a database:
<font class="small">Code:</font><hr /><pre>
USE databasename
SELECT OBJECT_NAME(object_id) as 'Table Name', name as 'Index Name', fill_factor as 'Current Fill Factor'
FROM sys.indexes
order by OBJECT_NAME(object_id), name </pre><hr />
 
[ QUOTE ]
Hello there

Our users experience many time in a day that the system get slow, e.g when they are in the application and click find, it takes long time to bring the data. Also when click application to start, it says "opening application".

I dont know where to start debugging as this happen not so often and i can not replicate the problem.

We are on VMware.

Please advice.

AD

[/ QUOTE ]


Your database server is on VMware?

How much SQL Server memory do you have specified for max memory?

<font class="small">Code:</font><hr /><pre>SELECT name, value, value_in_use, [description]
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE); </pre><hr />

How much memory do you have reserved in VMware for this server?

It is generally not considered a good idea to use VMware for your SQL server as VMware, particularly older versions, struggle to handle the disk I/O necessary for EnterpriseOne.
 
Back
Top