September 4, 2009 at 9:18 am
As the title says I am experiencing High % Disk Time but low Avg. Disk Queue Length on a production machine. THe period measured is during a typical 9-5 day with the % Disk TIme averaging %70 but the Avg. Disk Queue Length over the same period is only 1 and this is for a RAID 5 configuration.
Can anyone advise at all if I have IO problem as eveything I have read indicates that I have an IO issue when both counters are high.
Oh - this was at a sample rate of every 60secs
Thanks
September 4, 2009 at 9:57 am
Can you provide information about the server itself? Can you also tell me what your cache hit% is? I would hazard a guess and say that you are spending time retrieving data from disk ,which is where your high % is, but your disk is capable of providing in a short period of time. The total memory of your server + your cache hit % would help in determining that.
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
September 6, 2009 at 10:15 am
Some time % of disk time is ... not accurate as it should be. There are some better counters i.e. sec/read, sec/write - these should be less then 20ms ( slow io).
September 6, 2009 at 4:44 pm
A great approach to performance optimization can be found in the following TechNet article (waits and queues):
It gives great detailed advice and includes scripts and other goodies. I recommend it to you, if you really want to understand this area.
September 6, 2009 at 5:02 pm
Kwisatz78 (9/4/2009)
As the title says I am experiencing High % Disk Time but low Avg. Disk Queue Length on a production machine. THe period measured is during a typical 9-5 day with the % Disk TIme averaging %70 but the Avg. Disk Queue Length over the same period is only 1 and this is for a RAID 5 configuration.Can anyone advise at all if I have IO problem as eveything I have read indicates that I have an IO issue when both counters are high.
Oh - this was at a sample rate of every 60secs
If accurate then this just indicates that the disks saturation is the result of a single, non-mutlithreaded process, thread or session. A lot of people know of the mathematical relation between utilization and queue length (Q = U/(1-U)), but many do not realize that this is in fact a maximum because Q is bounded on the upper end by something called the "overlap" or "concurrency" level of the program, processes, application, system or server that is using the resource (Disk in this case).
Of course that does again presume that Util% is accurate and unfortunately on MS-Windows, %Busy is NOT and cannot be used as a synonym for Util%. However, %Idle is accurate and you can derive an accurate Util% from it with Util% = (100% - %Idle).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 7, 2009 at 3:29 am
Hi there thankyou to all for your replies.
SQLRunner - the SQL Buffer cache is %99 and above for the same period.
Marcin Gol - I will set up a new counter log using those disk counters.
Paul White - I will endevour to read the article
I am also using the Faults/Sec counter to determine if in fact presuure on the memory is causing the disk activity. If the read/sec and writes/sec are above the boundary of the disk and the memory seems ok, then does this indicate a I/O problem?
I am also running a load through DTA as I believe there to be a bunch of missing indexes.
If after that I still have I/O issues is it then time to look at moving data files around?
Thanks
Tom
September 7, 2009 at 10:31 am
You should read this position too: Tuning IBM System x Servers for Performance you can find there a lot of information about hardware, Windows and SQL Server. I always recommend this to my students!
September 7, 2009 at 3:05 pm
I am also running a load through DTA as I believe there to be a bunch of missing indexes.
do NOT just accept what the DTA gives you and move on. It will do awful things to your database! It is NOT a good tool! I know this for a fact because I have spent several hundred manhours in the last few years cleaning up the messes that client's have done with it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 7, 2009 at 4:14 pm
Yes - the DTA certainly isn't perfect, though it is occasionally worth a run just to see if there's anything obvious. Never take the recommendations completely literally!
September 7, 2009 at 4:42 pm
Kwisatz78 (9/7/2009)
Hi there thankyou to all for your replies.SQLRunner - the SQL Buffer cache is %99 and above for the same period.
Marcin Gol - I will set up a new counter log using those disk counters.
Paul White - I will endevour to read the article
I am also using the Faults/Sec counter to determine if in fact presuure on the memory is causing the disk activity. If the read/sec and writes/sec are above the boundary of the disk and the memory seems ok, then does this indicate a I/O problem?
I am also running a load through DTA as I believe there to be a bunch of missing indexes.
If after that I still have I/O issues is it then time to look at moving data files around?
Thanks
Tom
Can you give some stats on the box?
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
September 8, 2009 at 3:01 am
Ok, I wil make sure I review the recommendations that come out of DTA carefully.
SQLRunner what kind of stats you require? This is a 4 disk RAID 5 configuration on Windows Server 2003 SP2 running SQL 2005 RTM (yes I know something for me to resolve) 8 CPUs running 8GB RAM.
Thanks
September 8, 2009 at 3:51 am
sqlrunner (9/4/2009)
Can you provide information about the server itself? Can you also tell me what your cache hit% is? ...The total memory of your server + your cache hit % would help in determining that.
^^^ I think sqlrunner is after this ^^^
September 8, 2009 at 4:46 am
Kwisatz78 (9/8/2009)
Ok, I wil make sure I review the recommendations that come out of DTA carefully.SQLRunner what kind of stats you require? This is a 4 disk RAID 5 configuration on Windows Server 2003 SP2 running SQL 2005 RTM (yes I know something for me to resolve) 8 CPUs running 8GB RAM.
Thanks
How much of that memory is allotted to SQL Server? I normally leave 2GB for OS, which would mean 6GB for sql. You might have a situation where you don't have enough cache for your daily operations so you are hitting disk to satisfy standard requests. Ockham's razor.
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
September 8, 2009 at 7:13 am
Hi again
How do I check whats allotted, this isn't the max server memory setting is it? Here are the settings from sp_configure anyway:
name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 2147483647 2147483647
min server memory (MB) 0 2147483647 0 0
September 8, 2009 at 7:18 am
Kwisatz78 (9/8/2009)
How do I check whats allotted, this isn't the max server memory setting is it?
That's what he's after, yes.
edit: Just seen your posted figures. Buffer cache hit ratio is fine, so no worries there. It is best practice to leave a certain amount for the OS (especially on 64-bit, or if AWE is enabled on 32-bit) - I generally leave 1GB or so on a server with that amount of memory - assuming it is totally dedicated to SQL Server. It's a tuning thing, but not a huge problem here, given the cache hit ratio.
Paul
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply