Re: Random problems with performance in IDS 10 TC6

On 3 Sep, 21:35, kem...@gmail.com wrote: > Hi, > > At my company we recently upgraded to IDS 10 TC6 and have been running > it since march this year. Lately we've been experiencing some major > problems with performance running certain stored procedures. The > problem does not occur every time we run the SP. It does some quite > randomly, at least we haven't been able to distinguish a pattern for > the performance loss. > > For example; A SP that almost every day take around 5 - 10 minutes > some days run for 2-3 hours. The SP does basically the same thing > everyday. It scans through an indexed table for changed values and if > so updates or inserts rows in another table. Each table is in a > separate DBSpace and these in turn on separate disks. > > We've of course been running update statistics regularly, we actually > run it every night. Buffered writes and reads are > 98% before and > after running the SP. There are no sequential scans. There's not a lot > bufwaits and normally no other processes are running on the server > (Windows 2003 server). We've run basically every oncheck command there > is to check the consistency of the tables in the database and all > seems fine. I should probably also mention that the B-Tree Scanner is > turned off during execution of the SP. > > Has anyone had similar problems? Any ideas on what to look for or > check? onstat -u onstat -g ses for the session. onstat -g ath for the thread id several times. DISK - Check disk service times MEMORY - Check for paging/swapping CPU - Check for other processes use the cpu. NETWORK - check network i/o What else is running on the box at the time? Is this on a SAN whether other machines may be using the disk? Have you run hardware diags on the host? Run with set explain on and check the query plans do not change.