I have an rs6000 H50 as database server with 4 G memory. From that 2.5 is assigned to Sybase ASE 126.96.36.199 on AIX 5.2. The app is a Windows app and it is provided to the Mac clients via two blades as Citrix servers in an HP Blade container. So the traffic mostly between the database server and the citrix blades. All three are on a Gbit network segment. Users complained about slow response times, so I suspected network congestions, but the network folks here say that there is no congestion on their network. They did some sniffering and found that there is slow response back and forth between the DB server and the blades. There are cases when it takes minutes for the DB server to respond.
How can I catch those with sysmon or other performace checking tools ?
The DB itself is about 30GB and has a few big tables: 8G, 4G, two 2G and some 1G. The remaining 1100 tables are below 1G. The disks are 9GB SCSI disk forming a Raid5 array via a hardware raid controller.
The first thing to do is to figure out where the problem may be.
You can use nmon and/or vmstat at the OS level and sp_sysmon during times of heavy load to see if the dataserver is overloaded. If that is the case, then you need to see if the server has simply become undersized for the amount of work it has to do (size of the tables, number of users, etc) or if there are some specific queries (or tables) that need optimizing.
You can find out about what Sybase processes are doing by first listing the "active" SPIDs and then finding what these SPIDs are doing.
One such query that I use is this:
select spid, suser_name(suid) login, cmd, status
, object_name(id, dbid) procname
, blocked, time_blocked 'time', hostname
where spid != @@spid
and status != 'recv sleep'
and cmd not like '%HANDLER'
and cmd not like 'DEADLOCK%'
and cmd not like 'CHECKPOINT%'
and cmd not like 'AUDIT%'
and cmd not like 'HK %'
and cmd != 'REP AGENT'
and cmd != 'HOUSEKEEPER'
and cmd != 'PORT MANAGER'
This will list all SPIDs that are currently active. If you see the same SPID listed after several runs of this query then you have a long-running request. At this point you need to figure out what the request is doing - use sp_showplan to get the query plan, and dbcc sqltext(), or, if you load the MDA tables (see http://www.sypron.nl/mda.html) you can use the monProcessSQLText table to get the actual SQL being executed.
Thanks Mike for your suggestion. My opinion is he has to define his caches and attach the objects like tables, indexes etc to these caches. I think he as pretty understanding of the application so he can work with the developers to find out the hot tables.(of course your above query might help him). Starting 12.5 the cache management is somewhat changed.
Now coming to the backend(Network and RAID). If the environment is a pure OLTP, I suggest you to go for RAID10 or if it is a mixed one please go for RAID1. In RAID 5 there is a overhead of parity checking on the disks.