[ 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.