Results 1 to 11 of 11

Thread: IO Bottleneck

  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: IO Bottleneck

    Hi,

    We have been getting really bad performance from one of our databases recently.

    This is a three tier system with two services (.dll) connecting to the database, we have only experienced problems since the second service has been installed.

    These two services both use the same user and make about 300 connections to the database.

    I have noticed IO bottleneck when the system is under load, and the Average disk queue at this time is 15.

    How do I fix this? We have the logs and data on the same raid 10 array.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    The best way to do this is to use the PhysicalDisk Object: Avg. Disk Queue Length to monitor each disk array in your server.

    As a rule of thumb, the % Disk Time counter should run less than 55%. If you suspect a physical disk bottleneck, you may also want to monitor the % Disk Read Time counter and the % Disk Write Time counter in order to help determine if the I/O bottleneck is being mostly caused by reads or writes.

    Before using this counter under NT 4.0, be sure to manually turn it on by going to the NT Command Prompt and entering the following: "diskperf -y", and then rebooting your server.

    Useful article from SQLJunkies http://www.sqljunkies.com/Tutorial/9...2DDF8A808.scuk to finetune the problem.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi,

    Ok i need to re-check these values when the system is under load, but last night the average disk queue length was 180 / 12 (disks) = 15 as the ideal is 2 , I'm kinda thinking thats a lot.

    The two services using the same user ID to connect to SQL wouldn't make a difference would it?

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Capture PERFMON counters to see the system's (H/w) behaviour during this load.

    BTW how about database maintenance checks, such as dbreindex and updating stats for tables etc.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Whats the system's h/w behaviour?

    Yes an update statistics and re-index is done very night as part of the backup routine.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I mean to say these PERFMON stats will give more information about Physical disk, memory, processor, process details.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Ok the system is under load I will check all those values in perfmon

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Also follow the link above for the tips to resolve.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Yeah I also found this article yesterday, while it shows you how to tell whether you have an io bottleneck it doesn't really tell you how to resolve it.

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    A simple search on that website itself will fetch you http://www.sqljunkies.com/Tutorial/9...2DDF8A808.scuk


    There are several strategies to fine-tune the performance in this case. One of the simplest techniques is to increase the option value for max async IO and use the system Performance Monitor to check the most significant SQL Server counters, such as the "Batch requests/sec" of the "SQLServerQL Statistics".
    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Mar 2004
    Posts
    45
    Originally posted by Satya
    One of the simplest techniques is to increase the option value for max async IO and use the system Performance Monitor to check the most significant SQL Server counters, such as the "Batch requests/sec" of the "SQLServerQL Statistics".
    HTH
    Is this SQL Server 7? That option is self-tuning in SQL Server 2000.

    If the new service has caused such a major jump in disk I/O, first try and rewrite that code or redesign indexing so it is more efficient. If that is not possible then you are faced with improving the hardware by adding memory and/or disks.
    Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •