Sybase ASE 12.5.2 High Disk Queue Length

hard drivesybasewindows-server-2003

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.

Best Answer

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.

Device:
D:\DTCLASS\ads\ads_data01
ads_data01 per sec per xact count % of total

Reads                                                                       
  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.

Related Topic