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

K

Karandras

[H]ard|Gawd
Joined
Feb 16, 2001
Messages
1,873
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.
 
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...
 
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.
 
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.
 
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.
 
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.
 
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....
 
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.
 
Thanks for all the info. Once I fix my client's server problems then I'll implement these suggestions.

Thanks!
 
You must log in or register to reply here.
Back
Top