I have a Sybase ASE 12.5.2 server running on Windows 2003 that is extremely slow for most select queries. The system is a 2 x Dual Core Xeon, 4GB memory and 200GB Raid5 (10k SAS Disks) for data on an LSI megaraid controller with a 128MB cache + BBU.

The only noticeable indication on the server is that the "Disk Read Queue Length" sits at 100 in perfmon. Usually this indicates an issue with the disks but nothing appears to be wrong with them.

Any ideas for what I can do to figure out what the problem is? I should also say that I'm far more familiar with Sybase on Solaris, not Sybase on Windows.

Here is a screenshot:

alt text http://img.skitch.com/20090722-t4sgfxd5fb2ck7bbsdjei38uu6.png

Here is a 40 Minute sp_sysmon output.

EDIT: Apparently the sp_sysmon output is too big for the serverfault question text area. Here is a link to the full output: [http://pastie.org/private/t5xqd0vamhz7ynnjuo3cxq][2]

EDIT: I changed the Screenshot to one that reflects what's going on much much better.

  • What is the storage? Is it a RAID array or just a single disk or what? iSCSI SAN? 15K SAS? – Matt Rogish Jul 21 '09 at 17:38
  • also it seems to cut off the 2nd half of sp_sysmon output, can you edit and add it? I'm interested in the cache section – Matt Rogish Jul 21 '09 at 17:50
  • The system is a 2 x Dual Core Xeon, 4GB memory and 200GB Raid5 (10k SAS Disks) for data on an LSI megaraid controller with a 128MB cache + BBU. – Mark Turner Jul 21 '09 at 18:23
  • Added a link to the full output pastie.org/private/t5xqd0vamhz7ynnjuo3cxq – Mark Turner Jul 21 '09 at 18:23
  • Not adding this as an answer since i'm unsure how ASE behaves; but most databases with problems running on a RAID5 goes blazingly faster on RAID1 or RAID10. MS SQL is a good example, with logs on RAID1 and data on RAID5. – pauska Jul 21 '09 at 18:25

Looking at the sysmon, you're attempting to do 964.2 IOPS,

Total Requested Disk I/Os 964.2 2373.5 2314183

of these 99.2% are accounted for by this device, all your other devices are doing very little. All you devices are on the same physical array (D:), but this isn't a problem if they're doing nothing.

ads_data01 per sec per xact count % of total

  APF                           459.9        1132.0     1103670      48.1 %
  Non-APF                       495.8        1220.3     1189821      51.8 %
Writes                            0.8           2.0        1978       0.1 %

Total I/Os 956.4 2354.3 2295469 99.2 %

950 IOPS should be within the capabilities of a 10 disc raid 5, particularly as these are all reads. If we allow a conservative estimate of 100IOPS per spindle we have an estimate of 1000 IOPS for this array. Edit: However it looks like I've misread your original post in assuming there's 10 discs! Please let us know how many discs are in this array.

Cache Search Summary Total Cache Hits 1283.3 3158.8 3079829 71.9 % Total Cache Misses 502.2 1236.3 1205348 28.1 %

You're getting quite a few cache misses. This would lead me to wonder if you've enough memory assigned to ASE. On windows with ASE 12.5 you're limited to approx 2.6 GB memory for ASE (Whether on 64 bit or 32 bit windows). Can you please let us see the output of sp_configure 'memory'. Am in particular interested in this line:

max memory 33792 2950000 1475000 1475000 memory pages(2k) dynamic

It's possible that you may have enough memory assigned to ASE, but you've not added it to the data cache. Could you also let us see the output of sp_cacheconfig.

You can see from the start of the sysmon output that your CPUs are spending most of their time dealing with IO:

Engine Busy Utilization CPU Busy I/O Busy Idle
Engine 0 2.4 % 79.9 % 17.7 %
Engine 1 2.0 % 75.2 % 22.8 %

You also only have 2 engines assigned to ASE, on a dual dual-core you can go up to 4 if this is a dedicated server. Although your licensing may not permit this, as ASE is licensed per core.

  • I am facing a similar issue with high io busy but could not locate the problem. Any chance you could take a look at the sysmon report? – user1471980 Aug 23 '16 at 13:34

it looks to me like your actual Avg disk queue length is 3.554. The scale is set to graph it at 100x the value.

typical indications of issues start when this number is greater than the number of spindles in the raid group.

i would start with procmon from sysinternals to start determining the disk usage. i would then look into db specfic tools to monitor queries (sorry - don't know much about sybase)


You could try this optimizing tool to help track and troubleshoot the issue, it has a 14 day trial.

  • I'll try this tomorrow. – Mark Turner Jul 28 '09 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.