You should check the properties UnschReq and ActiveReq in the Server-level properties (sa_eng_properties( ) or in PerfMon) to see if you have maxed out your worker threads. {hint: if you find the unscheduled request counter climbing then your -gn setting is likely too small}. Also watch how your cache resizing is working (may need to add the -cs switch).
You will also want to watch for blocking. {See sa_conn_info( ) and sa_locks(0,null,-1) on how to do that}
There is also procedure profiling and request logging available in 9.0.2. You might want to consider comparing a request logging sample from when the system is running efficiently to when it is heavily used. {You can turn that on/off with sa_server_option( ) or with Sybase Central}
Alternatively if you already have an idea that certain queries are running slow and later fast, then you could see (with dbisql's plan viewer) if the Plans are changing.
Unfortunately there is no one cause for slowness. It can be due to many different phenomena and tracking down which one is affecting you can be difficult.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.