Netic (4/2/2008)

---

Thanks in advance !

We are having a situation (PROBLEM) with our DW - SQL SERVER 2000 SP4 ENT X86, it suddenly slowed a lot.

CPU Usage : 20%

IO : AVR DISK QUEUE LENGHT Between 1.5 and 2 (rarelly spikes (20/40)).

MEM : 8 GB, 6GB to SQL SERVER.

All the code that is running against the DB is fined tuned ( Most tables are Index Seeked with Clustered Indexes ), biggest table 450 GB.

No Lock Waits ( Except the main process(spid) that is showed has locked by it's own spid ( latch_xx (sh,ix)).

I guess that the problem is related to storage, because it is only doing 1-3MB sec (sometimes 20MB/sec).

PerfMon ( PROCESS SQLSERVER ( DISK Read Bytes ( between 1-3MB sometimes 20 MB/s), Disk Bytes Write Bytes/Sec same as Reads).

No more sql server instances or other software hogging the server.

I can't understand why the SQL SERVER is never using the IO it is used to use (25MB/S).

I don't see any DISK QUEUE on the Partitions that are used to store the datafiles/logfiles)

No Pages splits in the SQL SERVER ACCESS METHODS.

Sometimes I get about 1 GB in dirty buffers.

98 % Cache Hit Ratio.

ALL points to IO contention, but the counters from windows don't indicate this.

All help is REALLY appreciated.

Best Regards.

Netic (4/2/2008)

---

Daily,
Same data volume, but taking 4x the normal time.
I don't have console access to the server...
Complicated situation to expose here...
Need more help please :|

Thanks !