Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003

    Unanswered: Slow sybase response time


    I have an rs6000 H50 as database server with 4 G memory. From that 2.5 is assigned to Sybase ASE 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.

    Thanks ahead,


  2. #2
    Join Date
    Aug 2006
    Can you tell me how you allocated the 2.5Gb in ASE. I would like to know in terms of caches and buffer pools

  3. #3
    Join Date
    Jul 2003
    Here is the output from sp_configure:

    total data cache size 0 2180202 0 2180202 kilobytes NULL
    total logical memory 49152 2322798 1161037 1161399 memory pages(2k) read-only
    total physical memory 0 2322076 0 1161038 memory pages(2k) read-only

    procedure cache size 3271 69108 32768 32768 memory pages(2k) dynamic

    max memory 49152 2500000 1250000 1250000 memory pages(2k) dynamic

    Here is the output from sp_cacheconfig:

    1> sp_cacheconfig
    2> go
    Cache Name Status Type Config Value Run Value
    ------------------------------ --------- -------- ------------ ------------
    default data cache Active Default 2000.00 Mb 2000.00 Mb
    ------------ ------------
    Total 2000.00 Mb 2000.00 Mb
    ================================================== ========================
    Cache: default data cache, Status: Active, Type: Default
    Config Size: 2000.00 Mb, Run Size: 2000.00 Mb
    Config Replacement: strict LRU, Run Replacement: strict LRU
    Config Partition: 1, Run Partition: 1
    IO Size Wash Size Config Size Run Size APF Percent
    -------- --------- ------------ ------------ -----------
    2 Kb 61440 Kb 0.00 Mb 1636.00 Mb 10
    4 Kb 13104 Kb 64.00 Mb 64.00 Mb 10
    16 Kb 61440 Kb 300.00 Mb 300.00 Mb 10
    (return status = 0)

  4. #4
    Join Date
    Jan 2003
    Geneva, Switzerland
    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
         , linenum
         , blocked, time_blocked 'time', hostname
         , program_name
    from master..sysprocesses
    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 you can use the monProcessSQLText table to get the actual SQL being executed.


  5. #5
    Join Date
    Aug 2006
    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.

  6. #6
    Join Date
    May 2010
    Network World posted an article about a free response time tool that is worth checking out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts