High %Disk Time, Current Disk Queue with SQL Server 2005 + Server 2008

Discussion in 'Operating Systems' started by Karandras69, Sep 22, 2009.

  1. Karandras69

    Karandras69 [H]ard|Gawd

    Messages:
    1,857
    Joined:
    Feb 16, 2001
    Hey.

    So I'm no DB master, I'm just trying to figure out why this server is running like it is.

    We have a WhatsUp Gold server, it's a T300, E6405 (2.13 Ghz), 4 Gig ram.
    The original setup was dual WD2502ABYS drives in RAID 1 (software raid...blegh)
    I've moved the dbase on to a WD3000HLFS to give it the IO speed it needs.

    I've tested the drive and it peaks at 125 Meg/sec read and/or Write. So the hardware is performing as it should. So now I need to look deeper at this database and I don't know what I'm looking at. I don't know if the WhatsUp Gold Database designers made this thing to run stupid slow or what. Here are my %Disk Time and Current Disk Queue stats:

    (All on a scale of 100.0)
    %Disk Time
    Last - 160.00
    Average - 351.943
    Max - 3,595.800

    Current Disk Queue
    Last - 60
    Average 10
    Max 129

    Avg Disk Queue Length
    Last - 77
    Average - 64
    Max - 287.453

    To my understanding that's really high. However HDTune Pro shows
    Read
    Speed - 6.6 MB/sec
    Max - 12.4 MB/sec
    I/O - 164/sec

    Write
    Speed - 2.8 MB/sec
    Mac - 43.8 MB/sec
    I/O - 120/sec

    That seems somewhat low to me for the amount length of the disk queue and disk time?
    The whole reason for this, WhatsUp Gold is timing out while trying to either read or write to the database so I'm trying to resolve that issue. The dbase size is about 24 gig right now.

    Any help is very much appreciated.

    Thanks.
     
  2. extide

    extide 2[H]4U

    Messages:
    3,382
    Joined:
    Dec 19, 2008
    Yeah those numbers are way out of whack. I would make sure you have DMA enabled, maybe try swapping out the drivers for the disk controller, or the chipset. There is definitely something up there...
     
  3. Ritorix

    Ritorix [H]ard|Gawd

    Messages:
    1,823
    Joined:
    May 5, 2002
    Besides the hardware possibilities, you may need to look at software settings and the memory usage.

    24 gig database on 4 gigs ram. This is MS SQL right, how much of that 4gig memory is your DB set to use? How much of that 24 gig db is needed at the same time? Is it frequently utilized data, or is most of it rarely used? You see where I'm going with this, lots of reads and writes that could be avoided by either better memory management or more ram.
     
  4. extide

    extide 2[H]4U

    Messages:
    3,382
    Joined:
    Dec 19, 2008
    Mmm that's a good point, I didnt even think about the ram size being an issue. Check out your perf counter stats for the page misses and whatnot. I wouldn't be surprised if jumping from 4 to 8 or 12 or even 16GB of ram helped a lot.
     
  5. Karandras69

    Karandras69 [H]ard|Gawd

    Messages:
    1,857
    Joined:
    Feb 16, 2001
    Problem is, this was built on Win2k8 32 bit. Max of 3.2ish Gig unless there is something I don't know about.

    Also, the sql server is only using 1.5 gig of ram right now.
     
  6. extide

    extide 2[H]4U

    Messages:
    3,382
    Joined:
    Dec 19, 2008
    PAE mode works on windows server.

    -Enable PAE
    -Enable 3GB mode (SQL Server Supports this)

    I would do both of these steps even though SQL server is only using ~1.5GB.

    It will also allow you to get all 4gb of your ram.
     
  7. dx2

    dx2 Gawd

    Messages:
    545
    Joined:
    Jan 6, 2005
    how many tables/objects in the DB?

    Is the performance level suck constantly or only during certain times?

    Do you do any reporting off this DB? COuld be blocking or deadlocking on the table structure causing the timeout.

    re-index the core tables and update the index statistics (sp_updatestats) to see if that helps optimize it a bit. Everything you read about SQL 2005 being self optimizing is horseshit....well at least when you are dealing with moderately to highly transaction dbs :)

    That setup should handle a 24GB db with little effort....
     
  8. extide

    extide 2[H]4U

    Messages:
    3,382
    Joined:
    Dec 19, 2008
    Also you may want to use the resource monitor (Go to task manager, Performance Tab, Resource Monitor button at bottom) and you can see disk activity by process, see what processes are typically using the most disk time, maybe there is something rogue on there.
     
  9. Karandras69

    Karandras69 [H]ard|Gawd

    Messages:
    1,857
    Joined:
    Feb 16, 2001
    Thanks for all the info. Once I fix my client's server problems then I'll implement these suggestions.

    Thanks!