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.
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.
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:
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.
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 :) )
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:
In addition to disk performance, don't forget to also check memory, processor and network.
No one has followed this question yet.