Wonder if anyone out there can help with this extremely perplexing problem.
We have built a reporting system that does numerous complex calculations and therefore makes use of tempdb tables extensively. We have just moved over to a new server. Sometimes our reports run fine, other times extremely slowly. Sometimes a report will start off running fine and then seem to hit a wall ... running extremely slowly. Normally the server stays in this state and all other reports after will also run extremely slowly. It seems that if you leave the server for a while without anything running, the reports will run fine again later.
We have noticed that restarting Sybase always seems to fix the problem, at least for a while.
It seems to me that some server resource is being exhausted. I have been told that the only thing that restarting Sybase does is to clear the tempdb, however I'm told by the DBAs that we are not short on tempdb space.
We have tried running update statistics, but it doesn't seem to make any difference. It wouldn't actually since all the data needed for the report is written into temp tables before being manipulated for the purposes of the report. So indexing and statistics isn't really likely to make any difference.
Originally posted by amarshende
Instead of restarting sybase just run update statistics more frequently and do reindexing.
you have to distinguish which report / set of reports are making the problem . It could be a particular report creating the problem. After the report finishes its execution the system is returning to normal state. Also try to run the different performance statistics at the time of problem to pin point the cause of problem.