Hi,
I am using SQL 2008 server and I got problem with 100% Avg Disk Queue Length (too High), anybody know why and how to solve the problem?
Thanks.
asked
SQL 2008 Disk Queue Length Too High
What is the counter value for avg and max, not the percentage?
Avg Disk Queue Length is measured in number of requests not percentage, so not sure what you mean by 100%
What is your physical setup? How many disks? What RAID?
Also have you experienced a performance issue elsewhere that has lead you to look at Avg Disk ueue Length, or have you just looked at this metric and 'seen' the problem?
Logical drives not withstanding, if all your database files are on the same physical RAID you will get contention. Logical partioning still uses the same physical drives.
So if your primary dB and all its associated files, temp dB and its files, the system dBs and their files, and any other activity involved such as backups, file sharing etc. are all on the same physical drives you will definitely push the queue length up.
How much RAM is on the system?
What is your cache hit ratio?
What is your page life expectancy?
If we get these details we may be able to assist further. Provide us with a basic overview of your filesystem.
i.e.
C:\System
D:\Data
E:\Temp dB
F:\Logs
C: & D: on LUN 1
E: & F: on LUN 2...
Something that helps us see the physical support behind the logical.
You need to need to look @ the I/O of your disks. This could be your bottle neck.
A few questions:
- Do you have different arrays for data/logs/tempdb etc? If so are they RAID?
- Are you running on bare metal or VM?
- Is it always over 60-70% or just @ certain times?
The scale of the diagram in perfmon is 0-100%, but 100% only indicates 100% of the max "average disc queue length" counter.
You have to look at the value of "Avg disk queue length" and divided with the number of harddrives that serves your logical disk. Lets assume you are using a SAN with 50 drives for your logical disk, then you divide "avg disk queue length" with 50, and if that value is above 2 then you may have a bottleneck in your storage solution. In this sample an average above 100 will indicate a bottleneck.
You need to analyze why your disk system can't handle the datastream from SQL server.
- Are your tables indexed correct
- Are the statistics updated
- Are your queries optimized
- Do you have a huge database on very few discs.
Try to analyze your workload and search for table/index scans on large tables. Use SET STATISTICS IO ON before your statements to see how much logical and physical reads you have on your statements. Analyze your query plans etc.
If you have an optimized database, then you need to buy more and faster harddrives, and/or change your raid level ( i preffer RAID 1+0, most expensive and you'll never get more than you pay for :) )
Hi it is RAID 5. Most of the time over 90%
what do you mean my bare metal?
When analyzing performance issues, it is important to check several related counters to get a better indicator of the cause(s). Here are the top ones I recommend for disk performance:
- Average Disk Seconds per Transfer
- Average Disk Queue Length
- Average Disk Bytes per Second
- Average Disk Transfers per Second
In addition to disk performance, don't forget to also check memory, processor and network.
