Understanding Disk Queue Length

This does seem strange. If you use Perfmon to record the same data are the values that perfmon returns the same as your WMI script returns? If not it's a problem with WMI (or of course your script ;-).

JR

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.