Post reply

Disk Bytes/sec

  • December 20, 2010 at 11:25 am

    #232176

    I am troubleshooting database performance problems and I can clearly see that the database is IO bound. The server has a SAN with a 1 gigabit network connection. The network engineer has tracked packets and says that this is all the Windows (2003) server is requesting and the SAN/network delay is < 1ms usually, with an occasional 2 ms. delay. The delay I see is much greater. This test was done with a file copy from one SAN drive to another to eliminate the database from the equation.

    What I'm wondering is what type of performance should be expected with this configuration. My Disk Bytes/sec counter is reading around 34MB/sec. This seems very slow to me and accounts for the slow database. (It's 4TB with many multi gigabyte queries.) I would appreciate it if someone who knows these can confirm or deny that this is a slow value. This will help me know if we need an upgrade or tuning.

    Here are the stats from performance monitor: (average over 60 seconds)

    Physical Disk - Total:

    Avg. Disk sec/Read 6ms

    Avg. Disk sec/Write 88ms

    Disk Read Bytes/sec: 34mb

    Disk Write Bytes/sec: 40mb

    Thanks for any help you can give.

    -Brian

  • December 20, 2010 at 11:34 am

    #1264728

    I'm not a disk numbers specialist, but I just got this poster today and wanted to pass it on:

    "Free Poster – SQL Server Perfmon Counters of Interest"

    http://www.mssqltips.com/whitepaper.asp?id=17

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • December 21, 2010 at 6:53 am

    #1265042

    Brian - Have you reviewed this - http://blogs.technet.com/b/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx

    Your write measures seem high but based on what you stated your read measure looks ok. What is your primary type of activity that is occurring, read or write? Curious.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 21, 2010 at 10:12 am

    #1265175

    Thanks for the info. I will check out that blog.

    There are a lot of writes going on in this database. A lot of this is due to poor application design. Applications update an entire 25GB table and don't have a where clause to ensure they're only issuing the write if the data has changed. Sometimes only a few hundred rows are changing.

  • December 21, 2010 at 10:14 am

    #1265178

    If you have time please update this thread as you have more information. I would be curious to see what you find as you start looking at the counters.

    The application sounds like fun. 🙁

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 22, 2010 at 6:42 am

    #1265481

    I've read through the blog and it's useful. Most of the counters I have already checked and the bottleneck is clearly I/O. We may have a Windows problem because the network monitor is reporting much different statistics than the performance monitor. The network monitor is showing latency of <1ms and that the HBA is only requesting 25mb/sec to be sent. Theres not much to update though. At this point, I'm just trying to gather more support for upgrading the hardware.

    Thanks,

    Brian.

  • December 22, 2010 at 8:59 am

    #1265564

    Seems like you might have a misconfigured HBA (FC SAN)/NIC (iSCSI SAN). You didn't state which kind of SAN. Check read/write cache ratio if configurable, queue depth if HBA. Is Jumbo Frames enabled and speed mode if NIC. Lots of things can cause perf issues when a SAN is involved - consider getting a tuning professional to review your system.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 22, 2010 at 10:45 am

    #1265619

    Our SAN is a Dell Equilogic. We don't have a SAN administrator, but the SA that takes care of it says that it's an old SAN and nothing is configurable. I'd love to get a professional to confirm this. We are working on that too but there are politics in our shop that make this difficult right now.

  • December 22, 2010 at 2:34 pm

    #1265712

    Brian Carlson (12/22/2010)

    Our SAN is a Dell Equilogic. We don't have a SAN administrator, but the SA that takes care of it says that it's an old SAN and nothing is configurable. I'd love to get a professional to confirm this. We are working on that too but there are politics in our shop that make this difficult right now.

    Well, I have a client that made it up to just over 30TB (yes, I meant tera there) of SQL Server databases on Equalogic storage (virtualized, no less), so I assure you that a properly powered and configured iSCSI SAN can serve up some serious data volumes.

    As for configuration, your admin is correct in that there isn't a whole lot you can do with the devices, but I mentioned the two most important in my previous message - jumbo frames and forced sensing of the speed and full duplex on the NIC. Clearly you need to be on a dedicated network for the iSCSI backbone too. It is very cheap to add in additional NICs to the server to possibly gain some throughput as well.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 29, 2011 at 8:10 pm

    #1318026

    Brian,

    Where you able to resolve your issue with the Slow Avg Disk Sec/Write? We are have a very simular problem with our SAN right now where when we run a backup it could take 2 hours to finish up to 12 hours to finish depending on the how it wants to run that night.

    I am getting readings of 90ms on our Avg. Disk Sec/Write...

    Thanks

    Scott

  • May 4, 2011 at 4:07 pm

    #1320133

    I did not resolve the issue, but eventually gathered enough evidence to show that it was the SAN and not the database. Our data center is moving and getting all new hardware so we can't upgrade anything now.

  • May 5, 2011 at 6:30 am

    #1320326

    Brian,

    Thanks for the update...

    Scott

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply