I am encountering an odd problem with an ASE server and thought I
would poll for any thoughts. :) I'm just starting on this problem, but
I would appreciate any pointers on things to look at or snap
First, let me list my configuration. I am running an 8 processor
Solaris box with 4 GB of RAM and ASE 11.9.2. The database has no more
than 10 tables with the largest ( and primary ) table containing
approximately 10,000 rows. Only one trigger is configured to maintain
a history for updates to the primary table. No variable length data is
used in any column; they are all numeric(x,0) and char(x). Load
overall is very light; no where near the amount ASE could handle. The
only weak link would seem to be the hosting of the data over NFS. The
database is also heavily over-indexed ( index-to-data ratio of about
The problem is this : periodically, response times for simple
updates and queries will go through the roof ( e.g. from .25 seconds
to 9 seconds ). This seems to happen during periods of low to zero
usage, but will then persist, sometimes for hours, through periods of
My colleague has done a variety of tests including moving the data
onto locally hosted drives and increasing the memory for the
dataserver to a whopping 512 MB. Nothing has worked.
Here are the things I have observed : ( Thank you sp_sysmon! ;) )
1) During some of the slow queries, I have noticed a checkpoint
being run. This does not seem to be consistent across all of them,
however, ASE seems to be checkpointing an inordinate amount.
2) Slow queries seem to be making a lot more ( x10! ) non-blocking
network checks than the fast ones.
3) Address Lock Contention seems to be the major cause of process
task switches on both cases, but much more so for the bad queries.
Any ideas? I have suspected HOUSEKEEPER and CHECKPOINT SLEEP, but
this does not seem to be the case. Also, I have tried switching the
tables in the affected database from "allpages" to "datarow", to no
Thank you! :)
"Sword, hold thy temper; heart, be wrathful still:
Priests pray for enemies, but princes kill."