question

Leo avatar image
Leo asked

SQL 2008 Disk Queue Length Too High

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.

sql-server-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
What is the counter value for avg and max, not the percentage?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

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 :) )

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered

Hi it is RAID 5. Most of the time over 90%

what do you mean my bare metal?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.