Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2008

    Exclamation Unanswered: Dataware Performance Degradation - Urgent.

    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.

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    Quote Originally Posted by Netic
    biggest table 450 GB
    Is that for real!?

    If so, consider partitioning your table!
    Home | Blog

Posting Permissions

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